Simultaneous get_or_create throws InterfaceError: Pool.release() received invalid connection

[jackomeara-alphasights]

An endpoint that uses get_or_create() is often called multiple times simultaneously (at the same millisecond). When the server starts, the first time this endpoint is called in this way one of these requests returns a 500 status:

"
File "/usr/local/lib/python3.12/site-packages/tortoise/models.py", line 1056, in get_or_create
async with in_transaction(connection_name=db.connection_name) as connection:
File "/usr/local/lib/python3.12/site-packages/tortoise/backends/base/client.py", line 283, in aexit
await self.connection._parent._pool.release(self.connection._connection)
File "/usr/local/lib/python3.12/site-packages/asyncpg/pool.py", line 841, in release
raise exceptions.InterfaceError(
asyncpg.exceptions._base.InterfaceError: Pool.release() received invalid connection: <PoolConnectionProxy <asyncpg.connection.Connection object at 0x7f8b065d0e60> 0x7f8b0654d1e0> is not a member of this pool
"

The other requests are successful. This issue only occurs if requests are sent at the same time.

Using:
fastapi==0.111.0
tortoise-orm==0.20.1
tortoise-vector==01.1
asyncpg==0.29.0

[droserasprout]

Likely related: #792

[j-dutton-alphasights]

Just to give an update here in case it helps/anyone runs into this, this appears to be a race condition specifically relating to the use of in_transaction, which is used by get_or_create under the hood.

The following code demonstrates the error:

async def init_tortoise():
    await Tortoise.init(config=settings.TORTOISE_ORM)

   ...
    await init_tortoise()

    BACKGROUND_TASKS.extend(
        [
            asyncio.create_task(timeout_jobs_background_task()),
            asyncio.create_task(schedule_retries_background_task()),
            asyncio.create_task(remove_stale_jobs_background_task()),
            asyncio.create_task(check_saturation_background_task()),
        ]
    )
    ...

We introduced a in_transaction into one of these tasks. We turned on debug logging, which displayed that actually, four connection pools were created, rather than one.

One likely solution seems to me that Tortoise.init could also be responsible for creating the connection pool, rather than allowing in_transaction to do so. I'm however quite new to this codebase so can't comment on the best way to manage.

We're temporarily remediating this by adding a query to a table inside our init_tortoise function, and we now see only one connection pool created.