Django-DB-Migrations: cannot ALTER TABLE because it has pending trigger events

I want to remove null=True from a TextField:

-    footer=models.TextField(null=True, blank=True)
+    footer=models.TextField(blank=True, default='')

I created a schema migration:

manage.py schemamigration fooapp --auto

Since some footer columns contain NULL I get this error if I run the migration:

django.db.utils.IntegrityError: column "footer" contains null values

I added this to the schema migration:

    for sender in orm['fooapp.EmailSender'].objects.filter(footer=None):
        sender.footer=''
        sender.save()

Now I get:

django.db.utils.DatabaseError: cannot ALTER TABLE "fooapp_emailsender" because it has pending trigger events

What is wrong?

Another reason for this maybe because you try to set a column to NOT NULL when it actually already has NULL values.

Every migration is inside a transaction. In PostgreSQL you must not update the table and then alter the table schema in one transaction.

You need to split the data migration and the schema migration. First create the data migration with this code:

 for sender in orm['fooapp.EmailSender'].objects.filter(footer=None):
    sender.footer=''
    sender.save()

Then create the schema migration:

manage.py schemamigration fooapp --auto

Now you have two transactions and the migration in two steps should work.

At the operations I put SET CONSTRAINTS:

operations = [
    migrations.RunSQL('SET CONSTRAINTS ALL IMMEDIATE;'),
    migrations.RunPython(migration_func),
    migrations.RunSQL('SET CONSTRAINTS ALL DEFERRED;'),
]

If you are adding a non-nullable field, you need to do it in two migrations:

1. AddField and RunPython to populate it
2. AlterField to change the field to be non-nullable

Explaniation

On PostgreSQL and SQLite, this problem can occur if you have a sufficiently complex RunPython command combined with schema alterations in the same migration. For example, if you are adding a non-nullable field, the typical migration steps for this is:

1. AddField to add the field as nullable
2. RunRython to populate it
3. AlterField to change the field to be non-nullable

On SQLite and Postgres, this can cause problems because the whole thing is being done in one transaction.
The Django docs have a specific warning about this:

On databases that do support DDL transactions (SQLite and PostgreSQL), RunPython operations do not have any transactions automatically added besides the transactions created for each migration. Thus, on PostgreSQL, for example, you should avoid combining schema changes and RunPython operations in the same migration or you may hit errors like OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events.

If this is the case, the solution is to separate your migration into multiple migrations. In general, the way to split is to have a first migration containing the steps up through the run_python command and the second migration containing all the ones after it. Thus, in the case described above, the pattern would be the AddField and RunPython in one migration, and the AlterField in a second.

Have just hit this problem. You can also use db.start_transaction() and db.commit_transaction() in the schema migration to separate data changes from schema changes. Probably not so clean as to have a separate data migration but in my case I would need schema, data, and then another schema migration so I decided to do it all at once.