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

[Malik A. Rumi]

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.

[Mariusz Felisiak]

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

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

[Simon Charette]

This is certainly the cause. 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/

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

[Patrick K]

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.

[Mariusz Felisiak]

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.