Opened 3 years ago

Closed 2 years ago

#32688 closed Bug (needsinfo)

The ON CONFLICT sql suffix creates a syntax error on m2m inserts

Reported by: Malik A. Rumi Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Patrick K Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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:

In base/operations, the kwarg ignore_conflicts is set to False, while in postgresql/, 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/, 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/, where it is set to self.query.ignore_conflicts.

I found what I was looking for on lines 1382-3 of

# if ignore_conflicts_suffix_sql:
# result.append(ignore_conflicts_suffix_sql)

and on lines 284-5 of postgresql/

# 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.

Change History (6)

comment:1 Changed 3 years ago by Mariusz Felisiak

Component: UncategorizedDatabase layer (models, ORM)
Resolution: invalid
Status: newclosed

It looks that you're using unsupported version of PostgreSQL i.e. < 9.5.

comment:2 Changed 3 years ago 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.

comment:3 Changed 3 years ago by Simon Charette

This is certainly the cause. PostgreSQL < 9.5 simply doesn't support the ON CONFLICT statement.

FWIW PostgresSQL 9.5 is EOL and 9.6 will also be in this situation a few weeks from now.

Last edited 3 years ago by Simon Charette (previous) (diff)

comment:4 Changed 2 years ago by Patrick K

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. It doesn't happen with Postgres 10 though.

Last edited 2 years ago by Patrick K (previous) (diff)

comment:5 Changed 2 years ago by Patrick K

Cc: Patrick K added
Resolution: invalid
Status: closednew
Type: UncategorizedBug

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.

comment:6 Changed 2 years ago by Mariusz Felisiak

Resolution: needsinfo
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top