Django migrations using RunPython to commit changes

Question

I want to alter a foreign key in one of my models that can currently have NULL values to not be nullable.

I removed the null=True from my field and ran makemigrations

Because I'm an altering a table that already has rows which contain NULL values in that field I am asked to provide a one-off value right away or edit the migration file and add a RunPython operation.

My RunPython operation is listed BEFORE the AlterField operation and does the required update for this field so it doesn't contain NULL values (only rows who already contain a NULL value).

But, the migration still fails with this error: django.db.utils.OperationalError: cannot ALTER TABLE "my_app_site" because it has pending trigger events

Here's my code:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations

def add_default_template(apps, schema_editor):
    Template = apps.get_model("my_app", "Template")
    Site = apps.get_model("my_app", "Site")

    accept_reject_template = Template.objects.get(name="Accept/Reject")
    Site.objects.filter(template=None).update(template=accept_reject_template)    

class Migration(migrations.Migration):

    dependencies = [
        ('my_app', '0021_auto_20150210_1008'),
    ]

    operations = [
        migrations.RunPython(add_default_template),
        migrations.AlterField(
            model_name='site',
            name='template',
            field=models.ForeignKey(to='my_app.Template'),
            preserve_default=False,
        ),
    ]

If I understand correctly this error may occur when a field is altered to be not-nullable but the field contains null values. In that case, the only reason I can think of why this happens is because the RunPython operation transaction didn't "commit" the changes in the database before running the AlterField.

If this is indeed the reason - how can I make sure the changes reflect in the database? If not - what can be the reason for the error?

Thanks!

Answer 1

This happens because Django creates constraints as DEFERRABLE INITIALLY DEFERRED:

ALTER TABLE my_app_site
ADD CONSTRAINT "[constraint_name]"
FOREIGN KEY (template_id)
REFERENCES my_app_template(id)
DEFERRABLE INITIALLY DEFERRED;

This tells PostgreSQL that the foreign key does not need to be checked right after every command, but can be deferred until the end of transactions.

So, when a transaction modifies content and structure, the constraints are checked on parallel with the structure changes, or the checks are scheduled to be done after altering the structure. Both of these states are bad and the database will abort the transaction instead of making any assumptions.

You can instruct PostgreSQL to check constraints immediately in the current transaction by calling SET CONSTRAINTS ALL IMMEDIATE, so structure changes won't be a problem (refer to SET CONSTRAINTS documentation). Your migration should look like this:

operations = [
    migrations.RunSQL('SET CONSTRAINTS ALL IMMEDIATE',
                      reverse_sql=migrations.RunSQL.noop),

    # ... the actual migration operations here ...

    migrations.RunSQL(migrations.RunSQL.noop,
                      reverse_sql='SET CONSTRAINTS ALL IMMEDIATE'),
]

The first operation is for applying (forward) migrations, and the last one is for unapplying (backwards) migrations.

EDIT: Constraint deferring is useful to avoid insertion sorting, specially for self-referencing tables and tables with cyclic dependencies. So be careful when bending Django.

LATE EDIT: on Django 1.7 and newer versions there is a special SeparateDatabaseAndState operation that allows data changes and structure changes on the same migration. Try using this operation before resorting to the "set constraints all immediate" method above. Example:

operations = [
    migrations.SeparateDatabaseAndState(database_operations=[
            # put your sql, python, whatever data migrations here
        ],
        state_operations=[
            # field/model changes goes here
        ]),
]