`IndexError` on force_rollback of Transaction in tests.

[EdgyNortal]

MRE:

import databases

@pytest.fixture(scope="session")
def db():
    return  databases.Database("postgres://...")

@pytest.fixture()
async def transaction(db):
    await db.connect()
    async with db.transaction(force_rollback=True):
        yield db

async def test_example(transaction):
    await transaction.execute("select 1")

___________________________________ ERROR at teardown of test_example ___________________________________

db = <databases.core.Database object at 0x7f121c90ac80>

    @pytest.fixture()
    async def transaction(db):
        await db.connect()
>       async with db.transaction(force_rollback=True):

tests/query/test_core.py:306: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv/lib/python3.10/site-packages/databases/core.py:435: in __aexit__
    await self.rollback()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <databases.core.Transaction object at 0x7f121ca7d210>

    async def rollback(self) -> None:
        print("rollback called")
        async with self._connection._transaction_lock:
>           assert self._connection._transaction_stack[-1] is self
E           IndexError: list index out of range

.venv/lib/python3.10/site-packages/databases/core.py:482: IndexError

I have included prints in the __aenter__ and __aexit__ of the Transaction class.

__aenter__
Connection: <databases.core.Connection object at 0x7f121ca7d0c0>
TransactionStack: [<databases.core.Transaction object at 0x7f121ca7d210>]

--------------------------------------- Captured stdout teardown ----------------------------------------
__aexit__
Connection: <databases.core.Connection object at 0x7f121c4496c0>
TransactionStack: []

Somehow the transaction connection on entry != the connection on exit.

Can someone point out what I am doing wrong, or how this could be possible?

[EdgyNortal]

    @property
    def _connection(self) -> "Connection":
        # Returns the same connection if called multiple times
        return self._connection_callable()

It would appear, this isn't entirely true or behaving as expected?

[EdgyNortal]

        self, *, force_rollback: bool = False, **kwargs: typing.Any
    ) -> "Transaction":
        def connection_callable() -> Connection:
            return self

        return Transaction(connection_callable, force_rollback, **kwargs)

Based on the instantiation of the transaction, it would seem the above connection changing should be impossible.

[EdgyNortal]

The transaction self object is the same on both side of enter/exit, and repeated calls to connection_callable in __aenter__ does indeed return the same connection. Just on __aexit__ the connection_callable returns a different connection.

[EdgyNortal]

import databases

@pytest.fixture(scope="session")
def db(config):
    return  databases.Database(config.postgres_dsn)

@pytest.fixture()
async def transaction(db):
    await db.connect()
    t = await db.transaction()
    print(t._connection)
    try:
        yield db
    finally:
        print(t._connection)
        await t.rollback()

async def test_example(transaction):
    await transaction.execute("select 1")

___________________________________ ERROR at teardown of test_example ___________________________________

db = <databases.core.Database object at 0x7ffbe3706230>

    @pytest.fixture()
    async def transaction(db):
        await db.connect()
        t = await db.transaction()
        print(t._connection)
        try:
            yield db
        finally:
            print(t._connection)
>           await t.rollback()

tests/query/test_core.py:313: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <databases.core.Transaction object at 0x7ffbe3499630>

    async def rollback(self) -> None:
        print("rollback called")
        async with self._connection._transaction_lock:
>           assert self._connection._transaction_stack[-1] is self
E           IndexError: list index out of range

.venv/lib/python3.10/site-packages/databases/core.py:487: IndexError
----------------------------------------- Captured stdout setup -----------------------------------------
<databases.core.Connection object at 0x7ffbe3499570>
--------------------------------------- Captured stdout teardown ----------------------------------------
<databases.core.Connection object at 0x7ffbe3705f60>

Reproducible with low_level transaction as well.

[zanieb]

Thanks for all the details! Patches welcome :) otherwise I'll investigate when I get a chance

[TouwaStar]

There is a similar issue when force_rollback is set on a database level at least when using postgresql backend.

i noticed you also tested this with a postgresql backend @EdgyNortal.

I'm thinking this might be a problem on the db integration layer as when asked for connection its always returning a new object (which i dont think is correct when i see how core is trying to reuse the same connection?)

    def connection(self) -> "PostgresConnection":
        return PostgresConnection(self, self._dialect)

From what i was able to capture its the PostgressConnection that is changing while the Core connection sometimes changed sometimes stayed the same... (in both cases the exact same error was raised) Notice how during one run the core connection stayed the same, the other time it changed, but the postgress connection is always different

I might look at this some more but i am a bit out of my depth here so help is appreciated @zanieb

[Safintim]

I have a similar problem.

traceback:

...
File "/project/.venv/lib/python3.12/site-packages/pytest_asyncio/plugin.py", line 294, in async_finalizer
    |     await gen_obj.__anext__()
    |   File "/project/tests/conftest.py", line 34, in _connected_database
    |     await _database.disconnect()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 141, in disconnect
    |     await self._global_transaction.__aexit__()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 426, in __aexit__
    |     await self.rollback()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 473, in rollback
    |     assert self._transaction is not None
    | AssertionError
    +---------------- 2 ----------------
    | Traceback (most recent call last):
    |   File "/project/.venv/lib/python3.12/site-packages/_pytest/runner.py", line 544, in teardown_exact
    |     fin()
    |   File "/project/.venv/lib/python3.12/site-packages/_pytest/fixtures.py", line 1046, in finish
    |     raise exceptions[0]
    |   File "/project/.venv/lib/python3.12/site-packages/_pytest/fixtures.py", line 1035, in finish
    |     fin()
    |   File "/project/.venv/lib/python3.12/site-packages/pytest_asyncio/plugin.py", line 302, in finalizer
    |     event_loop.run_until_complete(async_finalizer())
    |   File "/home_dir/.pyenv/versions/3.12.2/lib/python3.12/asyncio/base_events.py", line 685, in run_until_complete
    |     return future.result()
    |            ^^^^^^^^^^^^^^^
    |   File "/project/.venv/lib/python3.12/site-packages/pytest_asyncio/plugin.py", line 294, in async_finalizer
    |     await gen_obj.__anext__()
    |   File "/project/tests/conftest.py", line 40, in _database
    |     async with connected_database.transaction(force_rollback=True):
    |   File "//project/.venv/lib/python3.12/site-packages/databases/core.py", line 426, in __aexit__
    |     await self.rollback()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 473, in rollback
    |     assert self._transaction is not None
    | AssertionError

An example to reproduce the problem:

conftest.py

import asyncio
import typing

import pytest
from databases import Database

async def clear_database(database: Database) -> None:
    await database.execute("DELETE FROM person")

async def create_tables(database: Database) -> None:
    await database.execute("CREATE TABLE IF NOT EXISTS person (id INT)")

@pytest.fixture(name="connected_database", scope="session")
async def _connected_database() -> typing.AsyncGenerator[Database, None]:
    _database: Database = Database(
        url="postgres://user:password@localhost:5432/db",
        force_rollback=True,
    )

    try:
        await _database.connect()

        await create_tables(_database)
        await clear_database(_database)

        yield _database
    finally:
        await _database.disconnect()

@pytest.fixture(name="database")
async def _database(connected_database: Database) -> typing.AsyncGenerator[Database, None]:

    async with connected_database.transaction(force_rollback=True):
        yield connected_database

@pytest.fixture(scope="session")
def event_loop() -> typing.Generator[asyncio.AbstractEventLoop, None, None]:
    loop: asyncio.AbstractEventLoop = asyncio.get_event_loop()
    yield loop
    loop.close()

test_insert_person.py

from databases import Database
from loguru import logger

async def test_insert_person(database: Database) -> None:

    person_id: int = 10
    actual = await database.fetch_val("INSERT INTO person (id) VALUES(:person_id) RETURNING id", values={"person_id": person_id})

    assert actual == person_id
    logger.debug("Test success")

dependecies

python = "^3.12"
databases = {extras = ["asyncpg"], version = "^0.9.0"}
pytest = "^8.1.0"
pytest-asyncio = "^0.21.0"
loguru = "^0.7.2"

The query (database.fetch_val) in the test and subsequent logging works out without errors. The problem occurs when trying to rollback transaction.

This error is only in new versions (started 0.8.0). It works on 0.7.0

[zekiblue]

I have a similar problem.

traceback:

...
File "/project/.venv/lib/python3.12/site-packages/pytest_asyncio/plugin.py", line 294, in async_finalizer
    |     await gen_obj.__anext__()
    |   File "/project/tests/conftest.py", line 34, in _connected_database
    |     await _database.disconnect()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 141, in disconnect
    |     await self._global_transaction.__aexit__()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 426, in __aexit__
    |     await self.rollback()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 473, in rollback
    |     assert self._transaction is not None
    | AssertionError
    +---------------- 2 ----------------
    | Traceback (most recent call last):
    |   File "/project/.venv/lib/python3.12/site-packages/_pytest/runner.py", line 544, in teardown_exact
    |     fin()
    |   File "/project/.venv/lib/python3.12/site-packages/_pytest/fixtures.py", line 1046, in finish
    |     raise exceptions[0]
    |   File "/project/.venv/lib/python3.12/site-packages/_pytest/fixtures.py", line 1035, in finish
    |     fin()
    |   File "/project/.venv/lib/python3.12/site-packages/pytest_asyncio/plugin.py", line 302, in finalizer
    |     event_loop.run_until_complete(async_finalizer())
    |   File "/home_dir/.pyenv/versions/3.12.2/lib/python3.12/asyncio/base_events.py", line 685, in run_until_complete
    |     return future.result()
    |            ^^^^^^^^^^^^^^^
    |   File "/project/.venv/lib/python3.12/site-packages/pytest_asyncio/plugin.py", line 294, in async_finalizer
    |     await gen_obj.__anext__()
    |   File "/project/tests/conftest.py", line 40, in _database
    |     async with connected_database.transaction(force_rollback=True):
    |   File "//project/.venv/lib/python3.12/site-packages/databases/core.py", line 426, in __aexit__
    |     await self.rollback()
    |   File "/project/.venv/lib/python3.12/site-packages/databases/core.py", line 473, in rollback
    |     assert self._transaction is not None
    | AssertionError

An example to reproduce the problem:

conftest.py

import asyncio
import typing

import pytest
from databases import Database

async def clear_database(database: Database) -> None:
    await database.execute("DELETE FROM person")

async def create_tables(database: Database) -> None:
    await database.execute("CREATE TABLE IF NOT EXISTS person (id INT)")

@pytest.fixture(name="connected_database", scope="session")
async def _connected_database() -> typing.AsyncGenerator[Database, None]:
    _database: Database = Database(
        url="postgres://user:password@localhost:5432/db",
        force_rollback=True,
    )

    try:
        await _database.connect()

        await create_tables(_database)
        await clear_database(_database)

        yield _database
    finally:
        await _database.disconnect()

@pytest.fixture(name="database")
async def _database(connected_database: Database) -> typing.AsyncGenerator[Database, None]:

    async with connected_database.transaction(force_rollback=True):
        yield connected_database

@pytest.fixture(scope="session")
def event_loop() -> typing.Generator[asyncio.AbstractEventLoop, None, None]:
    loop: asyncio.AbstractEventLoop = asyncio.get_event_loop()
    yield loop
    loop.close()

test_insert_person.py

from databases import Database
from loguru import logger

async def test_insert_person(database: Database) -> None:

    person_id: int = 10
    actual = await database.fetch_val("INSERT INTO person (id) VALUES(:person_id) RETURNING id", values={"person_id": person_id})

    assert actual == person_id
    logger.debug("Test success")

dependecies

python = "^3.12"
databases = {extras = ["asyncpg"], version = "^0.9.0"}
pytest = "^8.1.0"
pytest-asyncio = "^0.21.0"
loguru = "^0.7.2"

The query (database.fetch_val) in the test and subsequent logging works out without errors. The problem occurs when trying to rollback transaction.

This error is only in new versions (started 0.8.0). It works on 0.7.0

were you able to find a solution?

[Safintim]

@zekiblue I had to switch back to the old version (0.7.0).

[pkucmus]

I'm facing the exact same problem as https://github.com/encode/databases/issues/570#issuecomment-1976586329, tough my code is simpler:

Basic database.py setup:

from databases import Database

database = Database(
    url=str(settings.database_dsn),  # some DSN
    force_rollback=settings.unit_testing  # True
)

conftest.py

@pytest.fixture()
async def db():
    await database.connect()
    # here database._global_transaction._transaction holds a databases.backends.postgres.PostgresTransaction object
    yield database
    await database.disconnect()

test_nothing.py

async def test_nothing(db):
    db._global_transaction._transaction  # is None

Somewhere along the way that transaction is lost, maybe it's something pytest-asyncio is doing? If it's not clear the db in test_nothing is what the db fixture yields - _global_transaction is the same instance in both but for some reason the databases.core.Transaction._transaction property returns None. Looses track of _ACTIVE_TRANSACTIONS along the way? No idea

Tested on: 0.9.0 - broken 0.8.0 - broken (same problem, had to go down to SQLAlchemy ^1.4) 0.7.0 - works

0.7.0 does not have the assert in question, but I guess that only hides another problem https://github.com/encode/databases/blob/6b0c767588f501d5edaabb1bdf665fcf1ded88ea/databases/core.py#L407-L412

[pkucmus]

Seems to be a pytest-asyncio issue. No idea if there's something encode devs can do to improve the situation. I left a solution for this here. But depending on what happens here or there the solution might be very temporary.

[zanieb]

I believe anyio's pytest plugin might solve this problem? Some prior discussion at https://github.com/agronholm/anyio/pull/497

[andrewswait]

Have been banging my head against this same problem. Have come up with a hacky stop-gap decorator that just overrides the fixture to ensure the context manager hasn't been exited at the point of test execution:

@fixture
def database(): ...

def with_database(fn):
    @functools.wraps(fn)
    async def wrapper(*args, **kwargs):
        async with Database(DATABASE_URL, force_rollback=True) as db:
            kwargs.update(database=db)
            return await fn(*args, **kwargs)

    return wrapper

@pytest.mark.asyncio
@with_database
async def test_bulk_create_events(database: Database):
    ...

It's late on Friday though so I may have overlooked something -- it's working for now at least. I found I had to create an empty database fixture (rather than just passing a kwarg called that in the decorator), otherwise pytest complains if you have other fixtures for a given test function.

I left a solution for this here

Hopefully this gets merged soon ^ 🤞🏻

[pkucmus]

I'm back at it again, @zanieb is right (as they tend to be 😄) anyio solves it (and in general feels like a better approach than what pytest-asyncio is doing, IMO as least). I'm leaving a 👉 gist 👈 for future reference and would love to make a PR with docs explaining this. But someone from encode should check if this could be an endorsed approach.