django.db.utils.OperationalError: cannot ALTER TABLE because it has pending trigger events

Question

I am trying to change accepted_answer ForeignKey to a BooleanField and while migrating getting the error django.db.utils.OperationalError: cannot ALTER TABLE "forum_thread" because it has pending trigger events. This is the models.py of before:

class Thread(models.Model):
    title = models.CharField(max_length=300)
    answer_count = models.PositiveIntegerField(default=0)
    added_at = models.DateTimeField(auto_now_add=True)
    accepted_answer = models.ForeignKey('forum.Post', null=True, blank=True, related_name='+')

score 16 · Accepted Answer · answered Oct 18 '17 at 01:58

This error seems to happen when trying to update and change schema of the same table in one operation.

For example, after providing new default values when prompted by makemigrations when removing null=True from a field, this error seems to appear because Django is trying to change nullability while also updating the column.

Workarounds, depending on situation:

Easiest is often adding a new field with the desired properties, then removing the old field (maybe after copying data over).
If the error can be avoided by setting values for all fields that were needing a new default value (Django asking during the migration), try setting those values before making the migration/schema change.

In your specific case, adding a new field then removing the old is probably the way to go.

adding a new field, migrate and remove old is the easy solution. also, In my experience, if once you had this error, you better get a fresh database, and restart migrating. — benzkji, Mar 15 '18 at 15:06

score 3 · Answer 2 · answered Oct 24 '22 at 08:48

Adding to Max Lemieux's answer, if you try to change a field from nullable to non-nullable, this is impossible in the same database transaction, thus:

you can split your operations into 2 migrations (not recommended if complex operations)

you can wrap your migration's operations with these two statements to force PostgreSQL to check null constraint on save and not at the end of transaction

operations = [
    migrations.RunSQL('SET CONSTRAINTS ALL IMMEDIATE', reverse_sql='SET CONSTRAINTS ALL DEFERRED'),
    ... # your other operations here
    migrations.RunSQL('SET CONSTRAINTS ALL DEFERRED', reverse_sql='SET CONSTRAINTS ALL IMMEDIATE'),
]

django.db.utils.OperationalError: cannot ALTER TABLE because it has pending trigger events

2 Answers2