[Django] #24726: ArrayField with IntegerRangeField as base field generates invalid SQL on Insert operations
Django
Insert operations
----------------------------------+--------------------
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------
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:
{{{#!python
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.
{{{#!python
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.
{{{#!python
integer_range_list = [
NumericRange(10, 20),
NumericRange(30, 40),
]
IntegerRangeArrayModel.objects.create(
field=integer_range_list
)
}}}
The generated SQL is:
{{{#!sql
INSERT INTO "core_integerrangearraymodel" ("field") VALUES
(ARRAY['[10,20)', '[30,40)']) RETURNING "core_integerrangearraymodel"."id"
}}}
It should be something like this:
{{{#!sql
INSERT INTO "core_integerrangearraymodel" ("field") VALUES
(ARRAY['[10,20)'::int4range, '[30,40)'::int4range]) RETURNING
"core_integerrangearraymodel"."id"
}}}
Or this:
{{{#!sql
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* needs_docs: => 0
* needs_tests: => 0
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:1>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by edrmp):
I forked the Django repository and added some tests:
https://github.com/edrmp/django/tree/ticket_24726
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:2>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by edrmp):
* Attachment "ticket_24726.diff" added.
Diff between django/django@master and edrmp/django@ticket_24726
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by edrmp):
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:
{{{#!python
return self.cursor.execute(sql, params)
}}}
With the SQL:
{{{#!sql
INSERT INTO "postgres_tests_integerrangesarraymodel" ("int_ranges",
"bigint_ranges") VALUES (%s, %s) RETURNING
"postgres_tests_integerrangesarraymodel"."id"
}}}
And params:
{{{#!python
([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.
{{{#!python
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:3>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by mjtamlyn):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:4>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by dvarrazzo):
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
[https://github.com/psycopg/psycopg2/issues/313#issuecomment-108295518
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:5>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by adamzap):
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!
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:6>
Django
Insert operations
Reporter: edrmp | Owner: adamzap
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by adamzap):
* owner: => adamzap
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:7>
Django
Insert operations
Reporter: edrmp | Owner: adamzap
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by adamzap):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:8>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by adamzap):
* status: assigned => new
* owner: adamzap =>
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:9>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by adamzap):
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
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:10>
Django
Insert operations
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by adamzap):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:11>
Django
Insert operations
Reporter: Ederson Mota | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
Comment:
I guess this isn't ready for review since it's marked "WIP".
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:12>
Django
Insert operations
Reporter: Ederson Mota | Owner: (none)
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Tim Graham):
#27808 is related or a duplicate.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:13>
Django
Insert operations
Reporter: Ederson Mota | Owner: vinay
| karanam
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by vinay karanam):
* owner: (none) => vinay karanam
* status: new => assigned
Comment:
[https://github.com/django/django/pull/10222 PR] for #28291 also fixes
this issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:14>
Django
Insert operations
Reporter: Ederson Mota | Owner: vinay
| karanam
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"3af695eda24b874486ee8be7e0d729761b3bdc71" 3af695e]:
{{{
#!CommitTicketReference repository=""
revision="3af695eda24b874486ee8be7e0d729761b3bdc71"
Fixed #28291, #24726 -- Fixed ArrayField with JSONField and RangeFields.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:15>