Postgres lock_timeout and statement_timeout settings

[pythonweb2]

Postgres supports setting a lock timeout inside of a transaction: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT

Since alter table statements take out an ACCESS EXCLUSIVE lock, it is safer to set the lock_timeout and statement_timeout settings inside of the DDL transactions.

Is it possible to do this inside of the alembic migrations, and possible retry a few times if the statement fails to acquire the locks? This would avoid deadlocking the database in busy tables.

Edit: There is also commands like LOCK NOWAIT which may be better at preventing deadlock conditions, which maybe could just be emitted as raw SQL.

[zzzeek]

you have access to the connection in both the env.py script as well as individual migration script so you can do what you want with it, add retry decorators, etc. your own script actually does the BEGIN transaction, so you can emit "SET" statements on that connection immediately within the begin() block.

TLDR you need to work out these routines on a psycopg(2)/asyncpg connection in the way you'd like them to proceed then you can implement that directly in env.py.

[pythonweb2]

This is what I currently have in my env.py:

def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.
    """
    configuration = config.get_section(config.config_ini_section, default={})
    configuration["sqlalchemy.url"] = get_url().render_as_string(hide_password=False)

    connectable = sa.engine_from_config(
        configuration,
        prefix="sqlalchemy.",
        poolclass=sa.pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata, compare_server_default=True)

        with context.begin_transaction():
            context.run_migrations()

So there is the run_migrations() method that I am using. Would it be possible to retry an individual migration? That way I can still try to apply them in order. I guess I could just do it in like a for loop or something, is that what you are suggesting?

[zzzeek]

Well run_migrations() is going to run all the requested migrations, so if you wanted retry on individual migrations I'd create a function decorator that can do this and then apply it to the individual migrations themselves, like this:

@my_retry_decorator(tries=3)
def upgrade() -> None:
    # ...

to get transactions around each upgrade() with the ability for them to rollback and run again, there are a few approaches:

1. keep all of the normal alembic transaction mechanics in place and use SAVEPOINT (easiest, but im not sure how robust PG's error conditions are with SAVEPOINT)
2. keep normal alembic transaction mechanics in place and use autocommit_block to break out of them, but this looks iffy because it is also hardwiring AUTOCOMMIT mode right now, which means you'd need to emit the "BEGIN" command explicitly, may or may not be awkward
3. turn off alembic transaction mechanics entirely by using a real begin() in env.py; if you do this, then your decorator will have to do a "conn.commit()" to close out that transaction, then do the retry stuff:

def run_migrations_online() -> None:
    # .,..

    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata, compare_server_default=True)

       # the MigrationContext will not begin or commit any transactions when an external 
       # transaction is present
        with connection.begin():
            context.run_migrations()

then your decorator would have to:

from alembic import op
def my_retry_decorator(tries):
    def go(fn):
         conn = op.get_bind()
         conn.commit()  # close out the transaction that was running

         for i in range(tries):
             try:
                 with conn.begin():
                     fn()
             except Exception as e:
                 if i == tries - 1:
                     raise
                 print("got exception, retrying")
             else:
                 break
 
         conn.begin()   # resume "outer" transaction        
    return go

[zzzeek]

an Alembic feature that could help here would be an enhancement to autocommit_block() that provides better control over the connection's state, either an isolation_level argument to that method, or another method like suspend_transaction() that gives you the connection in a "clean" state, where you can then call begin() on it or do whatever transaction control you want. I dont want alembic providing a "retry" feature directly since retry decorators are extremely varied in what they need to accomplish and how.