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

August 03, 2019 - IT

If you're here, it means you've come across this problem too.

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

What's the issue?

I wanted to remove null=True from a few fields.

According to Django docs:

Avoid using null on string-based fields such as CharField and TextField. If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL. One exception is when a CharField has both unique=True and blank=True set. In this situation, null=True is required to avoid unique constraint violations when saving multiple objects with blank values.

I removed the null=True from CharField. Django generated this migration:

migrations.AlterField(
    model_name="user",
    name="first_name",
    field=models.CharField(
        blank=True, default="", max_length=30, verbose_name="first name"
    ),
    preserve_default=False,
),

The database already contained users with first_name set to null and because of that, the migration raised the error mentioned above.

Solution? I've created additional migration to convert null values to an empty string (''). I put this migration before the migration where I want to remove null=True.

To create an empty migration, use --empty param.

python manage.py makemigrations users --empty

The migration.

from django.db import migrations


def postgres_migration_prep(apps, schema_editor):
    User = apps.get_model("users", "User")
    fields = ("first_name", "last_name", "city")

    for field in fields:
        filter_param = {"{}__isnull".format(field): True}
        update_param = {field: ""}
        User.objects.filter(**filter_param).update(**update_param)


class Migration(migrations.Migration):

    dependencies = [("users", "0002_auto_20190710_1101")]

    operations = [
        migrations.RunPython(postgres_migration_prep, migrations.RunPython.noop)
    ]

This migration does not contain reverse code which is required for unapplying migrations. I didn't need it.