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!

First thought that comes to mind, split it. Make the datamigration first, then make your field not Null. — danielcorreia, Feb 10 '15 at 12:02
Yes, I thought of this approach, but I wanted to know if there's a way to avoid this and do everything in the same migration — Gabriel Amram, Feb 10 '15 at 12:39

score 47 · Accepted Answer · edited Sep 10 '20 at 13:25

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
        ]),
]

@shadow No, the doc says that "SET CONSTRAINTS sets the behavior of constraint checking within the current transaction." — eric, Dec 16 '16 at 19:21
Can you provide a concrete, correct example of using `SeparateDatabaseAndState`? I tried to solve the same problem by filling the target field with `RunPython` in `database_operations`, and `AlterField` in state_operations. But the result is that Django didn't actually create the NOT NULL constraint in the database. — Kal, Jun 26 '19 at 05:37
@Kal I suggest you to open a new Question, showing your migration, explaining what you are trying to accomplish and what is the issue. It is hard to know what it can be with this brief description, and also this comments section isn't appropriate for that. Best! — eric, Jul 03 '19 at 16:57
What worked for me is Django's support for non-atomic migrations. Mark the migration class with "atomic = True" and it will stop executing the entire migration in a single transaction. https://docs.djangoproject.com/en/dev/howto/writing-migrations/#non-atomic-migrations — Paul Hendry, Oct 30 '19 at 19:51

Steve Jalim · Answer 2 · 2016-01-25T11:02:57.220

19

Yes, I'd say it's the transaction bounds which are preventing the data change in your migration being committed before the ALTER is run.

I'd do as @danielcorreia says and implement it as two migrations, as it looks like the even the SchemaEditor is bound by transactions, via the the context manager you'd be obliged to use.

edited Jan 25 '16 at 11:02

answered Feb 10 '15 at 12:13

Steve Jalim

11,989
1
37
54

I have to admit that though this approach is valid and technically works, it is quite frustrating that there isn't a way to achieve that in the same migration. Thanks! – Gabriel Amram Feb 10 '15 at 12:43
3

Splitting into 2 (one with --empty and manually handling) is the solution! – danius Dec 27 '15 at 10:25

score 0 · Answer 3 · answered Sep 02 '22 at 02:55

Adding null to the field giving you a problem should fix it. In your case the "template" field. Just add null=True to the field. The migrations should than look like this:

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', null=True),
        preserve_default=False,
    ),
]

Django migrations using RunPython to commit changes

3 Answers3

Linked