transaction-dependent migrations / "pending trigger events" error with Postgresql

[sqlalchemy-bot]

Migrated issue, originally created by Jack Zhou (@univerio)

It seems by default alembic runs a sequence of migrations in a single transaction. However, this causes problems in e.g. PostgreSQL where certain queries cannot run together in the same transaction and fail with an error like:

#!

sqlalchemy.exc.OperationalError: (OperationalError) cannot ALTER TABLE "some_table" because it has pending trigger events

Because of this, upgrading a database from None to head all at once might fail while upgrading one None to <intermediate_revision> and then from <intermediate_revision> to head would succeed.

It would be helpful to have the option of running each migration in its own transaction (which means alembic would stop at the first failed migration without rolling back all the previous migrations).

---

Attachments: 201.patch

[sqlalchemy-bot]

Changes by Jack Zhou (@univerio):

• edited description

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

you can do this simply by changing your env.py to not call "begin_transaction()" and also send "transactional_ddl=False" to environment.configure() so that it writes a new version entry after each migration file:

connection = engine.connect()
context.configure(
            connection=connection,
            target_metadata=target_metadata,
            transactional_ddl=False
            )

try:
    context.run_migrations()
finally:
    connection.close()

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

also i'd need some more detail on what specifically causes this condition, what "pending trigger events" means. I've used alembic with Postgresql extensively and haven't seen this nor has anyone else reported it.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: execution model

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "run each individual migration in its own transacti" to "transaction-dependent migrations / "pending trigge"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

in fact just send transactional_ddl=False, leave the rest the same; no transaction is begun by context.begin_transaction() when that flag is False.

this of course is no transaction at all but if PG doesn't allow certain migration instructions in the same trans, the two operations could be in the same migration file in any case. I'd like to understand the actual case better.

[sqlalchemy-bot]

Jack Zhou (@univerio) wrote:

Thank you for the quick response. This error happens, in my case, due to a DEFERRABLE foreign key constraint on my table. The following reproduces the error:

BEGIN;
CREATE TABLE test (id serial PRIMARY KEY, self_id int REFERENCES test (id) DEFERRABLE INITIALLY DEFERRED);
INSERT INTO test (id, self_id) VALUES (1, 1);
ALTER TABLE test DROP COLUMN self_id;

Here is a relevant StackOverflow question.

I'd still like to have transactions within each migration, though, instead of no transaction at all.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK, while I have ideas like a directive to "restart" the transaction and such, I can still see the value in transaction per-migration overall and there's no mechanics in place to allow that. I'll see what I can do.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: high priority

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

here's an initial patch, needs tests written. feel free to try it out / debug, this is just a 5 minute version.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• attached file 201.patch

[sqlalchemy-bot]

Jack Zhou (@univerio) wrote:

I had to add one line to make it work (the migration context can't find the environment), but other than that it worked like a charm. Thanks again!

--- a/environment.py	2014-05-02 11:46:07.026403613 -0700
+++ b/environment.py	2014-05-02 11:48:56.906397007 -0700
@@ -679,6 +679,7 @@
             dialect_name=dialect_name,
             opts=opts
         )
+        self._migration_context.environment = self
 
     def run_migrations(self, **kw):
         """Run migrations as determined by the current command line

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK I'm running some tests here and it needs some more work than that actually, I'll have a full feature committed soon.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Added new feature :paramref:.EnvironmentContext.configure.transaction_per_migration,
  which when True causes the BEGIN/COMMIT pair to incur for each migration
  individually, rather than for the whole series of migrations. This is
  to assist with some database directives that need to be within individual
  transactions, without the need to disable transactional DDL entirely.
  fixes transaction-dependent migrations / "pending trigger events" error with Postgresql #201

→ 2d490cd

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed