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

Question

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?

This question is similar: http://stackoverflow.com/questions/28429933/django-migrations-using-runpython-to-commit-changes and had answers that were more useful to me. — SpoonMeiser, Apr 25 '17 at 16:35
I got the same problem with Postgres v10 (but not with Postgres v.12). Problem solved, by adding a separate migration file. — nik_m, Aug 11 '21 at 09:04

maazza · Answer 1 · 2016-03-02T13:27:47.440

185

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

edited Mar 02 '16 at 13:27

answered Feb 25 '14 at 16:58

maazza

6,646
15
58
94

9

To address this you can either use a data migration or manually (manage.py shell) go in and update non-compliant values – mgojohn Oct 26 '14 at 23:13
@mgojohn How do you do that? – pyramidface Jul 28 '15 at 23:42
1

@pyramidface If you aren't too picky, you can just update the null values at the django shell. If you're looking for something more formal and testable, it depends on what versions you're using. If you use south, see: http://south.readthedocs.org/en/latest/tutorial/part3.html and if you use django's migrations, see the "data migrations" section here: https://docs.djangoproject.com/en/1.8/topics/migrations/ – mgojohn Jul 29 '15 at 00:23
2

You saved my day! – Vladimir Prudnikov Nov 25 '20 at 07:30
2

I'm a huge fan of Django but obviously error messages like this - that are COMPLETE red herrings - just goes to show the complexity of such platforms! – AntonOfTheWoods Feb 15 '21 at 00:39
1

This hint saved my day. Thank you so much! I couldn't use the Django shell because of the DatabaseError, but I was able to open a connection with `psycopg2` and execute an `UPDATE` statement which set the columns having NULL to an empty string (which I wanted to be the default value). – mcrot Mar 23 '22 at 16:55

score 176 · Accepted Answer · answered Oct 11 '12 at 11:06

176

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.

answered Oct 11 '12 at 11:06

guettli

23,964
63
293
556

12

PostgreSQL probably changed its behaviour regarding such transactions, as I managed to run a migration with both data and schema changes on my dev machine (PostgreSQL 9.4) while it failed on the server (PostgreSQL 9.1). – Bertrand Bordage Jan 24 '15 at 17:49
1

Almost same for me. It worked flawlessly for 100+ migrations(including ~20 data migrations) until today, while adding unique together constraint along with data migration removing duplicates before it. PostgreSQL 10.0 – LinPy fan Apr 10 '18 at 12:23
2

If using a RunPython operation in the migration for the data migration, you just need to make sure it's the last operation. Django knows that if the RunPython operation is last, to open its own transaction. – Dougyfresh Sep 10 '19 at 19:42
2

@Dougyfresh is this a documented feature of django? – guettli Sep 11 '19 at 08:01
I actually don't see this anywhere, was just something I observed. https://docs.djangoproject.com/en/2.2/ref/migration-operations/#runpython – Dougyfresh Sep 12 '19 at 15:40
@Dougyfresh the page you linked contains the word "last" once. But the context is different. It is about the last migration, not the last operation. Still I don't understand this comment of you "Django knows that if the RunPython operation is last, to open its own transaction. " – guettli Sep 14 '19 at 06:49
I said it was "just something I observed". I faced the same error OP faced while running a migration with approx. 12 operations, with the RunPython operation in the middle. After coming across that error, I moved the RunPython operation to be the last operation with no other changes. The migration then ran successfully. – Dougyfresh Sep 15 '19 at 22:50
4

Django's migration also support `Migration.atomic = False` which allows you to not have to spit the file (as mentioned in the answer). – jnns Aug 28 '20 at 07:21
1

@jnns thanks, setting atomic to `False` solved my problem – DataGreed Nov 06 '20 at 00:59
Is atomic False option running migration without transaction? On postgresql transaction on schema is a good thing, which helps you not using manual rollback schema in db. If answer is yes - i think this is the way to get errors on app deployment. Approach in the answer the best choice, and it is a official choice, which you can find in django documentation. – Max Maximov Dec 21 '20 at 13:37

score 25 · Answer 3 · answered Mar 24 '20 at 10:41

25

At the operations I put SET CONSTRAINTS:

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

answered Mar 24 '20 at 10:41

sluge

429
1
5
11

2

Better to use [SeparateDatabaseAndState](https://docs.djangoproject.com/en/dev/ref/migration-operations/#separatedatabaseandstate) – bdoubleu Jul 06 '20 at 17:24

Zags · Answer 4 · 2021-07-02T19:23:17.220

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

AddField and RunPython to populate it
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:

AddField to add the field as nullable
RunRython to populate it
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.

score 9 · Answer 5 · answered Apr 15 '13 at 13:24

9

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.

answered Apr 15 '13 at 13:24

clime

8,257
9
58
82

8

The problem with this solution is this: What happens if your migration fails after db.commit_transaction()? I prefere to use three migrations, if you need this: schema-mig, data-mig, schema-mig. – guettli Apr 22 '13 at 13:42
6

See: http://django.readthedocs.io/en/latest/ref/migration-operations.html 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. – Iasmini Gomes Feb 01 '18 at 10:20

score 1 · Answer 6 · answered Apr 23 '20 at 20:31

You are altering the column schema. That footer column can no longer contain a blank value. There are most likely blank values already stored in the DB for that column. Django is going to update those blank rows in your DB from blank to the now default value with the migrate command. Django tries to update the rows where footer column has a blank value and change the schema at the same time it seems (I'm not sure).

The problem is you can't alter the same column schema you are trying to update the values for at the same time.

One solution would be to delete the migrations file updating the schema. Then, run a script to update all those values to your default value. Then re-run the migration to update the schema. This way, the update is already done. Django migration is only altering the schema.

Running some script is not really an option for me. I have several instances of the database and the continuous deployment process just calls "manage.py migrate". This question is already valid answers which work fine. — guettli, Apr 28 '20 at 14:19

score 0 · Answer 7 · answered Mar 11 '21 at 05:07

step 1)the solution is to remove the latest migration from the migration folder and remove the latest added fields in models.

step 2)then again makemigration and migrate

step 3)At the last add the field again that has been removed in the first step

step 4)then again makemigration and migrate

Problem solved

score 0 · Answer 8 · answered Nov 10 '21 at 16:19

0

In my case I've got

AddField
RunPython
RemoveField

Then I just moved the last RemoveFied to the new migration file, that fixed the problem

answered Nov 10 '21 at 16:19

Damir Nafikov

55
1
9

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

8 Answers8

Explaniation

Problem solved

Linked