ArrayField with IntegerRangeField as base field generates invalid SQL on Insert operations

[Ederson Mota]

I'm with problems trying to create a model object that contains an ArrayField with IntegerRangeField as base.

I asked about this on #django, and for @knbk this seems a bug. I believe it may be right.

Model:

class PostgreSQLModel(models.Model):
    class Meta:
        abstract = True


class IntegerRangeArrayModel(PostgreSQLModel):
    field = ArrayField(IntegerRangeField())

The initial migration an the table are created fine.

                           Table "public.core_integerrangearraymodel"
 Column |    Type     |                                Modifiers
--------+-------------+--------------------------------------------------------------------------
 id     | integer     | not null default nextval('core_integerrangearraymodel_id_seq'::regclass)
 field  | int4range[] | not null
Indexes:
    "core_integerrangearraymodel_pkey" PRIMARY KEY, btree (id)

However, when I try to create an object, it fails with a SQL error.

integer_range_list = [
    (10, 20),
    (30, 40),
]

IntegerRangeArrayModel.objects.create(
    field=integer_range_list
)

======================================================================
ERROR: test_create_with_tuples (core.tests.TestIntegerRangeArray)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: column "field" is of type int4range[] but expression is of type text[]
LINE 1: ...O "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10...
                                                             ^
HINT:  You will need to rewrite or cast the expression.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/eder/devel/integerrange-array/core/tests.py", line 17, in test_create_with_tuples
    field=integer_range_list
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/manager.py", line 127, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/query.py", line 348, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 710, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 738, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 822, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 861, in _do_insert
    using=using, raw=raw)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/manager.py", line 127, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/query.py", line 920, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 963, in execute_sql
    cursor.execute(sql, params)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/utils.py", line 97, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/utils/six.py", line 658, in reraise
    raise value.with_traceback(tb)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "field" is of type int4range[] but expression is of type text[]
LINE 1: ...O "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

There are no difference if a NumericRange object is used instead.

integer_range_list = [
    NumericRange(10, 20),
    NumericRange(30, 40),
]

IntegerRangeArrayModel.objects.create(
    field=integer_range_list
)

The generated SQL is:

INSERT INTO "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10,20)', '[30,40)']) RETURNING "core_integerrangearraymodel"."id"

It should be something like this:

INSERT INTO "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10,20)'::int4range, '[30,40)'::int4range]) RETURNING "core_integerrangearraymodel"."id"

Or this:

INSERT INTO "core_integerrangearraymodel" ("field") VALUES ('{"[10,20)","[30,40)"}') RETURNING "core_integerrangearraymodel"."id";

I created a test project, with more details, and some test cases to help illustrate the problem.

​https://github.com/edrmp/integerrange-array

There is Travis-CI too

​https://travis-ci.org/edrmp/integerrange-array

Thanks in advance.

[Ederson Mota]

I forked the Django repository and added some tests:

​https://github.com/edrmp/django/tree/ticket_24726

[Ederson Mota]

Diff between django/django@master and edrmp/django@ticket_24726

[Ederson Mota]

Taking a closer look at the exception generated by one of tests created mentioned above, lead me to some things that I would like to share.

The exception is trowed at db/backends/utils.py, line 64, method execute(), when trying to:

return self.cursor.execute(sql, params)

With the SQL:

INSERT INTO "postgres_tests_integerrangesarraymodel" ("int_ranges", "bigint_ranges") VALUES (%s, %s) RETURNING "postgres_tests_integerrangesarraymodel"."id"

And params:

([NumericRange(10, 20, '[)'), NumericRange(30, 40, '[)')], [NumericRange(7000000000, 10000000000, '[)'), NumericRange(50000000000, 70000000000, '[)')])

At this time, it's a method of psycopg2.extensions.cursor.

I wondered if this error will also occur using pycopg2 directly, and the answer is yes.

import psycopg2
from psycopg2.extras import NumericRange

# Connect to an existing database
conn = psycopg2.connect("dbname=integerrange-array user=postgres")

# Open a cursor to perform database operations
cur = conn.cursor()


# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
cur.execute("INSERT INTO core_integerrangearraymodel (field) VALUES (%s)",
            ([NumericRange(10, 20, '[)'), NumericRange(30, 40, '[)')],)
            )

Traceback (most recent call last):
  File "test_psycopg2.py", line 14, in <module>
    ([NumericRange(10, 20, '[)'), NumericRange(30, 40, '[)')],)
psycopg2.ProgrammingError: column "field" is of type int4range[] but expression is of type text[]
LINE 1: ... INTO core_integerrangearraymodel (field) VALUES (ARRAY['[10...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

I think that this issue on psycopg2 is somehow related:

​https://github.com/psycopg/psycopg2/issues/231

It's not a bug, but he describe a way to circumvent this behavior:

​https://github.com/psycopg/psycopg2/issues/231#issuecomment-53741200

Thanks.

[Marc Tamlyn]

This is a tricky one to fix. My inclination is that half the fault lies with postgres, and perhaps it could be fixed with psycopg2 as well.

The nicest solution is to add a ::int4range[] at the end of the parameter, but doing that is tricky to implement. It should be possible to register a custom typecaster for arrays of ranges with psycopg2. It feels like it should just work though...

I have raised ​https://github.com/psycopg/psycopg2/issues/313 with psycopg2 to get their feedback.

[Daniele Varrazzo]

Hello,

I think your best shot is to use a NumericRange subtype with an adapter adding ::int4range. Adding ::int4range[] after the array is impractical as you should know subclass the list instead.

An example implementation is in ​this comment.

I'll think whether we should introduce these objects in the library, I'm not so sure, because usually end users are able to specify a placeholder like %s::int4range[] and work around the problem without introducing too many details of the Postgres data system into the Python objects.

[Adam Zapletal]

Marc,

How would you like to proceed with this ticket? I'm willing to help close this with some guidance.

Fixing this would help my use case!

Thanks!

[Adam Zapletal]

​https://github.com/Crossway/django/commit/718bfaa18a12fb059d9e5cf8c4c716402ac8cb4d

This patch integrates and updates the contributions from edrmp and dvarrazzo above. The tests pass, but I'm not sure I put the psycopg2 adapter bits in the right place. I guess this is more a proof of concept.

I think there needs to be more discussion about how arrays of range fields should work. Lookups don't work right now, and I'm not sure how they would.

Also, dvarrazzo's suggestion to subclass NumericRange works, but wouldn't that require range objects destined for the database to be created with the adapted subclasses? I'm not sure if that's desired for Django. I'm interested to hear what the core team thinks.

Anyway, I hope this patch is helpful!

I'm unassigning this ticket for now as I don't think I can make progress without some design discussion taking place.

[Adam Zapletal]

I interacted with mjtamlyn over email, and he asked me to open a PR with my patch.

PR is here: ​https://github.com/django/django/pull/7099

[Tim Graham]

I guess this isn't ready for review since it's marked "WIP".

[Tim Graham]

#27808 is related or a duplicate.

[vinay karanam]

​PR for #28291 also fixes this issue.

[Tim Graham <timograham@…>]

In 3af695e:

Fixed #28291, #24726 -- Fixed ArrayField with JSONField and RangeFields.