Search code examples
transactionsasyncpg

How to rollback asyncpg transaction

Im using asyncpg to exec outer sql code (small and inner migration system). So in case of error in sql code I want to rollback all the DB changes in current transaction. But I cannot get transaction object to call rollback(). Please help!

My code is:

async with conn.transaction():
    try:
        await conn.execute(script)
    except asyncpg.exceptions.PostgresSyntaxError:
        logger('Got exception!')
        # what i have to write here to call rollback!?

Thanks a lot.

Solution

  • Inside of async with conn.transaction(): section transaction rolls back automatically if exception occurred there.

    But you can control flow manually:

    tr = conn.transaction()

try: 
    await tr.start() 
    await conn.execute('WRONG SQL STATEMENT / DO INTEGRITY ERROR HERE') 
except Exception as e:
    logger.error('Got exception {}', e)
    await tr.rollback()
else:
    tr.commit()