Re: Run some migration revisions outside of a transaction
Ok, thank you. I'll see how that goes.
sob., 11 sie 2018 o 01:26 Mike Bayer napisał(a):
>
>
> On Fri, Aug 10, 2018, 4:23 PM Michał Bultrowicz <
> michalbultrow...@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.
>
>
>
>
>> pt., 10 sie 2018 o 20:50 Mike Bayer
>> napisał(a):
>>
>>> On Fri, Aug 10, 2018 at 2:00 PM, Michał Bultrowicz
>>> 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-alembic+unsubscr...@googlegroups.com.
>>> > For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> 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-alembic+unsubscr...@googlegroups.com.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>> --
>> 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-alembic+unsubscr...@googlegroups.com.
>> For more options, visit https://groups.google.com/d/optout.
>>
> --
> 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-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Run some migration revisions outside of a transaction
On Fri, Aug 10, 2018, 4:23 PM Michał Bultrowicz
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.
> pt., 10 sie 2018 o 20:50 Mike Bayer napisał(a):
>
>> On Fri, Aug 10, 2018 at 2:00 PM, Michał Bultrowicz
>> 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-alembic+unsubscr...@googlegroups.com.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> 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-alembic+unsubscr...@googlegroups.com.
>> For more options, visit https://groups.google.com/d/optout.
>>
> --
> 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-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Run some migration revisions outside of a transaction
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.
pt., 10 sie 2018 o 20:50 Mike Bayer napisał(a):
> On Fri, Aug 10, 2018 at 2:00 PM, Michał Bultrowicz
> 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-alembic+unsubscr...@googlegroups.com.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> 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-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: Run some migration revisions outside of a transaction
On Fri, Aug 10, 2018 at 2:00 PM, Michał Bultrowicz
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-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
--
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
