Django Generates Incorrect SQL for Altering Multiple Nullable Fields With Defaults

[James Pulec]

It seems that if a model has multiple fields on it that are nullable, and then modified to be non-nullable and have defaults, the sql generated tries to mix updating and schema changes and results in postgres yelling about pending trigger events. This only seems to happen if the model also has a foreign key to another object. I think that's the pending trigger event that is being referenced. As an example:

# Initial models

class Basket(models.Model):
    spam = models.IntegerField(null=True)
    eggs = models.IntegerField(null=True)

    bike = models.ForeignKey('baskets.Bike')

class Bike(models.Model):
    name = models.TextField()

You then create initial migrations, run them, and add some data. Then alter the models to be as follows:

# Altered models

class Basket(models.Model):
    spam = models.IntegerField(default=1)
    eggs = models.IntegerField(default=1)

    bike = models.ForeignKey('baskets.Bike')

class Bike(models.Model):
    name = models.TextField()

If you then generate a migration, and try to run it, the follow error and traceback occur.

Operations to perform:
  Apply all migrations: admin, auth, baskets, contenttypes, sessions
Running migrations:
  Applying baskets.0002_auto_20171209_2319...Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.OperationalError: cannot ALTER TABLE "baskets_basket" because it has pending trigger events


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

Traceback (most recent call last):
  File "mysite/manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 221, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 515, in alter_field
    old_db_params, new_db_params, strict)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/postgresql/schema.py", line 112, in _alter_field
    new_db_params, strict,
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 710, in _alter_field
    params,
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: cannot ALTER TABLE "baskets_basket" because it has pending trigger events

The SQL generated by running sql migrate is:

BEGIN;
--
-- Alter field eggs on basket
--
ALTER TABLE "baskets_basket" ALTER COLUMN "eggs" SET DEFAULT 1;
UPDATE "baskets_basket" SET "eggs" = 1 WHERE "eggs" IS NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "eggs" SET NOT NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "eggs" DROP DEFAULT;
--
-- Alter field spam on basket
--
ALTER TABLE "baskets_basket" ALTER COLUMN "spam" SET DEFAULT 1;
UPDATE "baskets_basket" SET "spam" = 1 WHERE "spam" IS NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "spam" SET NOT NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "spam" DROP DEFAULT;
COMMIT;

I ran into this on Django 1.11, but it also still appears to be an issue when I test Django 2.0 as well.

[Simon Charette]

Duplicate of #25105.