Database connection hanging after a test with transaction

[omind-marion]

Hi, I am developing an API using FastAPI and Databases. I recently updated most of the projects libraries, and I'm encountering some issue with my tests. I used to use pytest-asyncio, but since the fastapi update I am now using anyio.

I was not too sure if this was the correct repository to post my issue.

When I have a transaction the database connection seems to hang at the end. I have provided simples snippets of code to reproduce the issue. As well as the hanging piece of code located in the asyncpg library. There are no error message, and I have to quit the process manually to stop it.

Here are the libraries' versions:

databases 0.7.0
├── asyncpg *
└── sqlalchemy >=1.4.42,<1.5
fastapi. 0.89.1
└── starlette 0.22.0
    └── anyio >=3.4.0,<5
httpx 0.23.3
pytest 7.2.1

Fastapi main

app = FastAPI()
database = Database(..., init=...)

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    # this is where it fails
    await database.disconnect()

conftest.py

@pytest.fixture(scope='session')
def anyio_backend():
    return 'asyncio'


@pytest.fixture(scope="session")
async def client(anyio_backend):
    # httpx.AsyncClient
    async with AsyncClient(app=my_app, base_url="") as ac:
        # call startup event
        await my_app.router.startup()
        yield ac
        # call shutdown event
        await my_app.router.shutdown()

test.py

@pytest.mark.anyio
async def test_test(client):
    response = await client.get('/test')

router.py

@router.get("/test")
async def get_test():
    transaction = await main.database.transaction()

    try:
        await transaction.start()

    except UniqueViolationError:
        await transaction.rollback()
        raise HTTPException(status_code=400, detail="error")

    else:
        await transaction.commit()

    return 200

The hanging happens in asyncpg.pool.py in close()

            release_coros = [
                ch.wait_until_released() for ch in self._holders]
            await asyncio.gather(*release_coros)

I hope this is enough information.

Thank you for your times

[zevisert]

Possibly related, not entirely sure: https://github.com/encode/databases/pull/546

[zevisert]

Not related/fixed by #546, but I ran into something like this while working on that PR. Based on your example I think this is caused by your code in router.py.

You should not await a transaction's creation when using the low-level transaction management logic.

Try this:

 # file: router.py
 @router.get("/test")
 async def get_test():
-    transaction = await main.database.transaction()
+    transaction = main.database.transaction()

     try:
         await transaction.start()

     except UniqueViolationError:
         await transaction.rollback()
         raise HTTPException(status_code=400, detail="error")

     else:
         await transaction.commit()

     return 200

[zevisert]

See also: #390 and #262

[omind-marion]

Thanks for the time you took to respond to this issue. Priorities have shift in my projet, I won't be able to test this before a few weeks. I'll make sure to update this issue when I'm back at it.