'ON CONFLICT' error for new single insert through Django admin to postgres

Question

Today, for the first time, I got an 'ON CONFLICT' error while trying to insert a new entry through the Django admin to my postgres database:

ignored_wrapper_args
(False,
 {'connection': <django.db.backends.postgresql.base.DatabaseWrapper object at 0x7f0bb08e3d90>,
  'cursor': <django.db.backends.postgresql.base.CursorDebugWrapper object at 0x7f0bb094ab50>})
params
(3351, 48, 3351, 212, 3351, 31)
self
<django.db.backends.postgresql.base.CursorDebugWrapper object at 0x7f0bb094ab50>
sql
('INSERT INTO "ktab_entry_tags" ("entry_id", "tag_id") VALUES (%s, %s), (%s, '
 '%s), (%s, %s) ON CONFLICT DO NOTHING')

3351 is the id that would have been auto assigned to the entry had it been accepted. 48, 212, and 31 are the ids of the 3 tags I was going to put on this entry.

I have been using this blog app - which I created - for almost 3 years. Not only have I never come across this error before, it does not make sense to me. Each entry has a unique id. Therefore it's hard to imagine a case where there would be a conflict due to a combination of entry_id, tag_id, and entry_tag_id being repeated. Therefore, any such constraint would be redundant and unnecessary.

But it looks like I do have it:

ktab.public.ktab_entry_tags.ktab_entry_tags_entry_id_tag_id_0900d285_uniq(entry_id, tag_id)

and

ktab.public.ktab_entry_tags.ktab_entry_tags_entry_id_tag_id_0900d285_uniq(entry_id, tag_id) UNIQUE

If the 2nd one is unique, why do both end in '_uniq'?

I also have two of these:

ktab.public.ktab_entry_tags.ktab_entry_tags_pkey(id)

and

ktab.public.ktab_entry_tags.ktab_entry_tags_pkey(id) UNIQUE

Note that this is the intermediate table that Django created for me because of the many-to-many field. I started to delete one each, but I didn’t because I still don’t see how this causes, or solves, my issue - or why it only showed up now.

According to PyCharm / DataGrip, the last successful entry has the id 3350. Therefore, there is no conflict that I can see with a new entry of entry_id=3351 with tag_ids= 212, 48, and 31. I have the Database Tools and SQL plug in.

Filtering for: tag_id = '48' AND tag_id = '212' AND tag_id = '31' got NOTHING, including no errors!

entry_id = '3351' same

entry_id = '3350' gets 3 results, as expected. One for each of the 3 tags, each with their own ktab_entry_tags id, but all with the same entry_id:

3102,3350,86
3101,3350,13
3100,3350,8

I don't really understand "ignored_wrapper_args", see https://github.com/django/django/blob/main/django/db/backends/utils.py. I'm guessing that's False because it does have a connection?

I saw this: Postgres: INSERT if does not exist already But this is not a concurrent operation. I am trying to add one entry through the Django admin.

However, I also saw bulk_create in my traceback, which makes no sense for a single insert, especially one where "ignore_conflicts=ignore_conflicts":

/home/malikarumi/.virtualenvs/chronic_all/lib/python3.9/site-packages/django/db/models/query.py, line 506, in bulk_create
...
            if objs_without_pk:
                fields = [f for f in fields if not isinstance(f, AutoField)]
                returned_columns = self._batched_insert( …
                    objs_without_pk, fields, batch_size, ignore_conflicts=ignore_conflicts,
                )
                if connection.features.can_return_rows_from_bulk_insert and not ignore_conflicts:
                    assert len(returned_columns) == len(objs_without_pk)
                for obj_without_pk, results in zip(objs_without_pk, returned_columns):
                    for result, field in zip(results, opts.db_returning_fields):

Could this error be because the entry does not yet have an ID? I don’t think that makes sense, either.

Thanks for the help.

ps

At the last second I decided to check version, and got this:

PostgreSQL 9.4.26 on x86_64-pc-linux-gnu (Ubuntu 9.4.26-2.pgdg18.04+1), compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

I am a little horrified to see how old my Postgres is. I actually have newer versions installed, but as I said, I’ve been using this app for three years without issue. I will see about upgrading and deleting old versions now while I await your answers. But I also read that ‘ON CONFLICT’ started with 9.5, so how do we explain that?

Python 3.9.4 Django 3.1.7 Ubuntu 20.04.2 LTS

score 1 · Answer 1 · answered Apr 26 '21 at 20:26

I don't know if there are many people with this issue, but it boils down to a self-inflicted syntax error. When Django/Postgres decides there is a conflict, "DO NOTHING" is an alternative to an IntegrityError. But then they go the extra step of appending that string to the end of your INSERT, where it is treated as a syntax error! Even worse, at least in my case, is that there was no conflict! Bottom line, I commented out the following lines, and everything is back to normal:

lines 1382-3 of site-packages/django/db/models/sql/compiler.py:

if ignore_conflicts_suffix_sql
result.append(ignore_conflicts_suffix_sql)

and on lines 284-5 of

site-packages/django/db/backends/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)

'ON CONFLICT' error for new single insert through Django admin to postgres

1 Answers1