Insertion in transaction presisted after rollback?

[yeraydiazdiaz]

Hello all,

While investigating an issue in Databases I couldn't understand a problem regarding rolling back transactions when two connections are present. For example:

import asyncio
import logging

import asyncpg

logging.basicConfig(level=logging.DEBUG)

logger = logging.getLogger(__name__)


DB_DSN = "postgresql://0.0.0.0:5432/databases_test"


async def truncate(db, expect=None):
    logger.info("Truncating")
    async with db.acquire() as conn:
        logger.info("%s", conn)
        await conn.execute("TRUNCATE foo")


async def insert(db):
    logger.info("Inserting")
    async with db.acquire() as conn:
        logger.info("%s", conn)
        async with conn.transaction():
            logger.info("Inside transaction context: inserting")
            await db.execute("INSERT INTO foo VALUES (2)")
            raise Exception
            # this somehow survives?!


async def select(db, expect=None):
    logger.info("Selecting")
    async with db.acquire() as conn:
        logger.info("%s", conn)
        result = await conn.execute("SELECT * FROM foo")
        assert result == expect, result


async def main():
    db = await asyncpg.create_pool(DB_DSN)
    await truncate(db)
    try:
        await insert(db)
    except Exception:
        pass
    await select(db)


if __name__ == "__main__":
    asyncio.run(main())

After running this script I expected the table foo to be empty, since it was truncated and the insert was rolled back because an exception is raised in the transaction block. However, the insert is persisted and the assertion fails later on which is surprising to me. I also tried manually calling transaction.start and rollback but the changes are still persisted.

I couldn't find any issues mentioning this behaviour, hopefully someone can explain why this is?

Thank you.

[elprans]

await db.execute("INSERT INTO foo VALUES (2)")

This should be conn.execute, not db.execute. You started a transaction on a different connection.

[yeraydiazdiaz]

Of course! That makes sense. Thank you!