Run some migration revisions outside of a transaction

[Michał Bultrowicz]

Hey!

What approach would you recommend if we want to run some revisions outside of a transaction, but then run the other ones normally?

Let me illustrate that. Let's say we have revisions 1,2,3,4,5. The database is currently at revision 1, so we need to run 2-5. The problem is that 4 is an operation that can't be in a transation, specifically CREATE INDEX CONCURRENTLY from Postgres (synchronous creation of the index would block writes to a table, which is unacceptable in production).

So we'd like to run, 2 and 3 in a transaction, then do 4 outside of a transactionand wait for it, then run 5 in a transaction.

In run_migrations_online (we don't use offline) there's this bit

with sql_engine.connect() as connection:
    with context.begin_transaction():
        context.run_migrations()

Can we get access to the list of revisions here and bundle them up however we want?

Best regards,
Michał Bultrowicz

[Mike Bayer]

On Fri, Aug 10, 2018 at 2:00 PM, Michał Bultrowicz
<michalbu...@gmail.com> wrote:
> Hey!
>
> What approach would you recommend if we want to run some revisions outside
> of a transaction, but then run the other ones normally?
>
> Let me illustrate that. Let's say we have revisions 1,2,3,4,5. The database
> is currently at revision 1, so we need to run 2-5. The problem is that 4 is
> an operation that can't be in a transation, specifically CREATE INDEX
> CONCURRENTLY from Postgres (synchronous creation of the index would block
> writes to a table, which is unacceptable in production).
> So we'd like to run, 2 and 3 in a transaction, then do 4 outside of a
> transactionand wait for it, then run 5 in a transaction.

so you want to set transaction_per_migration:

http://alembic.zzzcomputing.com/en/latest/api/runtime.html?highlight=transaction_per_migration#alembic.runtime.environment.EnvironmentContext.configure.params.transaction_per_migration

you're still safe if 1,2,3 or 5 fail since they are in their own
transaction and it will stop at that migration if a problem happens.

then in migration 4, inside the migration script get a separate
connection, which has to be an ".autocommit' connection with psycopg2:

with op.get_bind().engine.connect().execution_options(isolation_level='AUTOCOMMIT')
as conn:
conn.execute("CREATE INDEX CONCURRENTLY")

>
> In run_migrations_online (we don't use offline) there's this bit
> with sql_engine.connect() as connection:
> with context.begin_transaction():
> context.run_migrations()
>
> Can we get access to the list of revisions here and bundle them up however
> we want?
>
> Best regards,
> Michał Bultrowicz
>

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alem...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Michał Bultrowicz]

Thanks, but how can I inspect the revisions in code? I'd need to provide some special markers on the ones I don't want to surround in a transaction.

[Mike Bayer]

On Fri, Aug 10, 2018, 4:23 PM Michał Bultrowicz <michalbu...@gmail.com> wrote:

Thanks, but how can I inspect the revisions in code? I'd need to provide some special markers on the ones I don't want to surround in a transaction.

That database  code is in the migration file itself.   The approach doesn't imply changes to env.py.   

[Michał Bultrowicz]

Ok, thank you. I'll see how that goes.