Adding foreign keys and instances in the same migration crashes on PostgreSQL 10+.

[Janne Rönkkö]

An example project can be found from GitHub with test script reproducing the issue: ​https://github.com/jannero/django3-postgresql10-migration-issue

The issue I faced looks a lot like the issue discussed in PostgreSQL mailing list: ​https://www.spinics.net/lists/pgsql/msg194687.html (see this answer: ​https://www.spinics.net/lists/pgsql/msg194689.html)

If there is a migration that adds something causing Django 3 to create new index and the migration also adds (or possible when data is updated and/or deleted) migrations fail when PostgreSQL 10 or newer is used. With PostgreSQL 9.6 such migrations seem to work with Django 3.

With Django 2.2.9 the same migrations work on all the PostgreSQL versions I tested (9.6, 10.11, 11.5).

You can reproduce the issue using the code linked above and by running two commands:

docker-compose up -d and then ./test.sh

The output of the test script (​https://github.com/jannero/django3-postgresql10-migration-issue/blob/master/test.sh) cat be found from the attached file log.txt. The migration file triggering the issue is here: ​https://github.com/jannero/django3-postgresql10-migration-issue/blob/master/app/migrations/0002_auto_20191218_1353.py

This is the relevant part of the error (this is taken from case using PostgreSQL 11.5):

Operations to perform:
  Apply all migrations: admin, app, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying app.0001_initial... OK
  Applying app.0002_auto_20191218_1353...Traceback (most recent call last):
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ObjectInUse: cannot CREATE INDEX "app_value" because it has pending trigger events


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 231, in handle
    post_migrate_state = executor.migrate(
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/migrations/executor.py", line 247, in apply_migration
    migration_recorded = True
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 115, in __exit__
    self.execute(sql)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/janne/code/django-postgresql-migrations/venv-django3/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: cannot CREATE INDEX "app_value" because it has pending trigger events

[Janne Rönkkö]

Output from test.sh

[Mariusz Felisiak]

Thanks for this report.

Regression in 738faf9da2a5cd03148a36375db80746c99c9623.

Reproduced at 35d36d946272bed06a3d7c7cd4e5b71b613e7a4f.

[Simon Charette]

This is similar to #25492.

Making sure to ​check the deferred inline foreign key constraint after creation will likely be necessary. We still want it be deferred though for the rest of the migration so we likely want to make it immediate and back to its default value.

Janne, you can work around this issue by marking the migration atomic = False in the mean time.

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 22ce5d00:

Fixed #31106 -- Fixed migrations crash on PostgreSQL 10+ when adding FK constraints inline and changing data.

This allows adding foreign key constraints inline and changing data in
the same migration on PostgreSQL 10+.

Regression in 738faf9da2a5cd03148a36375db80746c99c9623.

Thanks Janne Rönkkö for the report and Simon Charette for the
implementation idea and review.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0f8041a:

[3.0.x] Fixed #31106 -- Fixed migrations crash on PostgreSQL 10+ when adding FK constraints inline and changing data.

This allows adding foreign key constraints inline and changing data in
the same migration on PostgreSQL 10+.

Regression in 738faf9da2a5cd03148a36375db80746c99c9623.

Thanks Janne Rönkkö for the report and Simon Charette for the
implementation idea and review.
Backport of 22ce5d0031bd795ade081394043833e82046016c from master