#32688: The ON CONFLICT sql suffix creates a syntax error on m2m inserts
-----------------------------------------+------------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.ae222a1e00d532562c1e48b11e1bcf84%40djangoproject.com.
