[Django] #32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
Django
-----------------------------------------+------------------------
Reporter: Malik A. Rumi | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
When I made the leap from 1.11.5 to 3.1.1, I ran into this issue: Whenever
I tried to insert on an m2m related model / field, I got this error:
{{{
ProgrammingError('syntax error at or near "ON"\nLINE 1: ...ry_tags"
("entry_id", "tag_id") VALUES (3353, 31) ON CONFLIC...\n
}}}
I have never seen this error before, and the fact that it is excessively
truncated [ one might even say, 'butchered' ], didn't help. The problem
was that Django was adding this 'sql suffix' - "ON CONFLICT, DO NOTHING",
to the end of my insert statement, and then Django turned around and
treated it as a syntax error on my inserts. ''But there was **no**
conflict! So the solution was obvious: stop Django from adding this
unnecessary and irrelevant suffix.
A lot of digging led me to two files:
/home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
packages/django/db/backends/base/operations.py
/home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
packages/django/db/backends/postgresql/operations.py
In base/operations, the kwarg ''ignore_conflicts'' is set to **False**,
while in postgresql/operations.py, it is set to **None**. Was this
intentional? Because as I will show you in a moment, the code has examples
of this variable taking on [True, False, None, and
self.query.ignore_conflicts]. Digging further, I looked at
/home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
packages/django/db/models/sql/subqueries.py, where ignore_conflicts is
initially set to False in class InsertQuery(Query), and
/home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-
packages/django/db/models/sql/compiler.py, where it is set to
self.query.ignore_conflicts.
I found what I was looking for on lines 1382-3 of compiler.py:
# if ignore_conflicts_suffix_sql:
# result.append(ignore_conflicts_suffix_sql)
and on lines 284-5 of postgresql/operations.py:
# def ignore_conflicts_suffix_sql(self, ignore_conflicts=None):
# return 'ON CONFLICT DO NOTHING' if ignore_conflicts else
super().ignore_conflicts_suffix_sql(ignore_conflicts)
After commenting them both out, my inserts proceed as normal. So, maybe
I'm the only one with this issue. I don't know. I don't know why
insert_conflicts seems to be able to take any arbitrary value. Even if it
is a nullable Boolean, True, False, and None are not ==, so if False is
passed down to a method expecting it to be None, then the alternate
condition is going to be triggered. I don't know why this sql suffix was
attached to my insert when there was no conflict, causing a syntax error
and preventing me from adding new data.
If you think this is a bug, great. If not, at least I fixed the issue for
me. Thanks.
--
Ticket URL: <https://code.djangoproject.com/ticket/32688>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Malik A. Rumi | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => invalid
* component: Uncategorized => Database layer (models, ORM)
Comment:
It looks that you're using unsupported version of PostgreSQL i.e. < 9.5.
--
Ticket URL: <https://code.djangoproject.com/ticket/32688#comment:1>
Django
Reporter: Malik A. Rumi | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Malik A. Rumi):
Yes, I am upgrading tonight. Do you think this is the cause? Can you
explain how, or point me to some documents that will do the explaining?
Thanks.
--
Ticket URL: <https://code.djangoproject.com/ticket/32688#comment:2>
Django
Reporter: Malik A. Rumi | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Simon Charette):
This is certainly the clause. PostgreSQL < 9.5 simply doesn't support the
`ON CONFLICT` statement.
https://www.postgresql.org/docs/9.5/release-9-5.html#AEN131764
FWIW PostgresSQL 9.5 is EOL and 9.6 will also be in this situation a few
weeks from now.
https://www.postgresql.org/support/versioning/
--
Ticket URL: <https://code.djangoproject.com/ticket/32688#comment:3>
Django
Reporter: Malik A. Rumi | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by sehmaschine):
I'm having the same issue with Postgres 11. Just trying to update an M2M
field (with both `set` or `add`) leads to this error.
--
Ticket URL: <https://code.djangoproject.com/ticket/32688#comment:4>
Django
Reporter: Malik A. Rumi | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Patrick K):
* cc: Patrick K (added)
* status: closed => new
* resolution: invalid =>
* type: Uncategorized => Bug
Comment:
I'm not 100% sure, but I think this needs to be reopened. I can easily
reproduce this with the admin interface when trying to save an M2M with
Postgres 11. I do understand that Postgres 9.x is not supported anymore,
but IMHO this should not happen with Postgres 11.
--
Ticket URL: <https://code.djangoproject.com/ticket/32688#comment:5>
Django
Reporter: Malik A. Rumi | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => needsinfo
Comment:
I cannot reproduce this issue on PostgreSQL 11. Can you provide an SQL
statement which crashes? or a sample project? and confirm your database
version.
--
Ticket URL: <https://code.djangoproject.com/ticket/32688#comment:6>