`ProgrammingError` when unapplying migrations

[simensol]

I am encountering a ProgrammingError when unapplying migrations that include triggers. The error occurs when running the python manage.py migrate [app] zero command or unapplying specific migrations. The issue seems to be related to the pgtrigger library attempting to install triggers on tables that have already been dropped.

Here is an excerpt from one of the migration files generated by Django and django-pgtrigger:

operations = [
    ...
    pgtrigger.migrations.AddTrigger(
        model_name='group',
        trigger=pgtrigger.compiler.Trigger(name='RO_Group', sql=pgtrigger.compiler.UpsertTriggerSql(condition='WHEN (OLD."api_id" IS DISTINCT FROM (NEW."api_id") OR OLD."id" IS DISTINCT FROM (NEW."id") OR OLD."owner_id" IS DISTINCT FROM (NEW."owner_id"))', func="RAISE EXCEPTION 'pgtrigger: Cannot update rows from % table', TG_TABLE_NAME;", hash='130432f1fd9dcd2b84df3d5a24b09232b3276253', operation='UPDATE', pgid='pgtrigger_ro_group_deb1a', table='groups_group', when='BEFORE')),
    ),
]

Here is relevant parts of the Group model:

class Group(models.Model):
    class Meta:
        ...

        triggers = [
            pgtrigger.ReadOnly(
                name="RO_Group",
                fields=["api_id", "id", "owner"],
            )
        ]

Here is the traceback of the error:

python manage.py migrate groups zero --verbosity 3
Operations to perform:
  Unapply all migrations: groups
Running pre-migrate handlers for application admin
Running pre-migrate handlers for application auth
Running pre-migrate handlers for application contenttypes
Running pre-migrate handlers for application sessions
...
Running pre-migrate handlers for application pgtrigger
...
Running migrations:
  Rendering model states... DONE (0.048s)
  Unapplying manager.0002_initial_data... OK (0.010s)
  Unapplying groups.0002_initial... OK (0.039s)
  Unapplying children.0002_initial... OK (0.022s)
  Unapplying groups.0001_initial... OK (0.002s)
Running post-migrate handlers for application admin
Running post-migrate handlers for application auth
Running post-migrate handlers for application contenttypes
Running post-migrate handlers for application sessions
Running post-migrate handlers for application djmoney
Running post-migrate handlers for application pgtrigger
Traceback (most recent call last):
  File "/code/.venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UndefinedTable: relation "groups_group" does not exist

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/code/manage.py", line 26, in <module>
    main()
  File "/code/manage.py", line 22, in main
    execute_from_command_line(sys.argv)
  File "/code/.venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/code/.venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/code/.venv/lib/python3.12/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/code/.venv/lib/python3.12/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 384, in handle
    emit_post_migrate_signal(
  File "/code/.venv/lib/python3.12/site-packages/django/core/management/sql.py", line 52, in emit_post_migrate_signal
    models.signals.post_migrate.send(
  File "/code/.venv/lib/python3.12/site-packages/django/dispatch/dispatcher.py", line 189, in send
    response = receiver(signal=self, sender=sender, **named)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/pgtrigger/apps.py", line 88, in install_on_migrate
    installation.install(database=using)
  File "/code/.venv/lib/python3.12/site-packages/pgtrigger/installation.py", line 32, in install
    trigger.install(model, database=database)
  File "/code/.venv/lib/python3.12/site-packages/pgtrigger/core.py", line 932, in install
    self.exec_sql(install_sql, model, database=database)
  File "/code/.venv/lib/python3.12/site-packages/pgtrigger/core.py", line 847, in exec_sql
    return utils.exec_sql(str(sql), database=database, fetchall=fetchall)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/pgtrigger/utils.py", line 67, in exec_sql
    cursor.execute(sql)
  File "/code/.venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 122, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/.venv/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/code/.venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: relation "groups_group" does not exist

This may be related to #29, but I'm not sure.

[wesleykendall]

Thanks for reporting, definitely seems like a bug on pgtrigger's end. I will mark it as such and keep this open.

Are you able to currently work around this? The unfortunate hack right now may simply be manually removing the statement inserted into the initial migration file that installs the trigger.

I know that I've been able to successfully revert migrations in the past that drop tables with triggers, but I remember django doing some unexpected things with the ordering of some of these operations if they are in the same migration file.

Can you confirm that you have the creation of the model and the addition of a trigger in the same migration file?

If so, can you try marking atomic=False in the migration file and reverting? This would at least give me more data that this is not a transaction-related issue.

Will see if I can reproduce this myself

[simensol]

The makemigrations command generates two migration files for the app: 0001_initial.py, which creates the model, and 0002_initial.py, which creates constraints, triggers, ForeignKey fields, and ManyToManyField fields. The 0001_initial.py file is listed as a dependency for 0002_initial.py.

0001_initial.py:

class Migration(migrations.Migration):
    initial = True

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Group',
            fields=[
                ...
            ],
            bases=(..., models.Model),
        ),
    ]

0002_initial.py:

class Migration(migrations.Migration):
    initial = True

    dependencies = [
        ('groups', '0001_initial'),
        ...
    ]

    operations are:
        migrations.AddField(...),
        migrations.AddField(...),
        migrations.AddField(...),
        migrations.AddConstraint(...),
        migrations.AddConstraint(...),
        pgtrigger.migrations.AddTrigger(
            model_name='group',
            trigger=pgtrigger.compiler.Trigger(name='RO_Group', sql=pgtrigger.compiler.UpsertTriggerSql(condition='WHEN (OLD."feide_id" IS DISTINCT FROM (NEW."feide_id") OR OLD."id" IS DISTINCT FROM (NEW."id") OR OLD."owner_id" IS DISTINCT FROM (NEW."owner_id"))', func="RAISE EXCEPTION 'pgtrigger: Cannot update rows from % table', TG_TABLE_NAME;", hash='130432f1fd9dcd2b84df3d5a24b09232b3276253', operation='UPDATE', pgid='pgtrigger_ro_group_deb1a', table='groups_group', when='BEFORE')),
        ),
    ]

Adding atomic = False below initial = True in both migration files does not make any difference. The same ProgrammingError is raised.

I have not found a solution other than removing the trigger from the model's Meta.