How to handle asyncpg.exceptions.TooManyConnectionsError in Sqlalchemy

Question

I'm developing a project with async SQLAlchemy connected through asyncpg to PostgreSQL database. The problem is this: when I make too many connections to database it raises the following exceptions:

asyncpg.exceptions.TooManyConnectionsError: sorry, too many clients already

This is basically the limitation of Postgres itself and configured there. But it's pity, that the session does not handle this, for example by trying to connect multiple times. Is there any workaround that allows to get it working without exceptions?

Here is the setup:

from asyncio import current_task
from sqlalchemy.ext.asyncio import AsyncSession, async_scoped_session, create_async_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.pool import NullPool
from app.db.utils import get_database_url

engine = create_async_engine(get_database_url('postgresql+asyncpg'), poolclass=NullPool, echo=False)
async_session_factory = sessionmaker(
    bind=engine,
    expire_on_commit=False,
    class_=AsyncSession
)
AsyncSession = async_scoped_session(async_session_factory, scopefunc=current_task)
Base = declarative_base(bind=engine)

score 0 · Answer 1 · answered Jul 18 '22 at 23:03

I came out with the following workaround:

...

AsyncSessionBase = async_scoped_session(async_session_factory, scopefunc=current_task)

@asynccontextmanager
async def AsyncSession(max_trials=12):
    for i in range(max_trials):
        try:
            async with AsyncSessionBase() as session:
                yield session
        except asyncpg.exceptions.TooManyConnectionsError:
            continue
        return

It tries to create a session specified number of times and only after that raises the exceptions. asyncpg.exceptions.TooManyConnectionsError is the only exception that it handles so it won't affect logic of other exceptions.

But still, I don't know whether there is a native sqlalchemy solution or not.

How to handle asyncpg.exceptions.TooManyConnectionsError in Sqlalchemy

1 Answers1