CODEWITHSUNDEEP
pythonsqlalchemyfastapiuvicornasyncpg
Dmitry
Dmitry

Reputation: 357

FastApi sqlalchemy Connection was closed in the middle of operation

I have an async FastApi application with async sqlalchemy, source code (will not provide schemas.py because it is not necessary):

database.py

from sqlalchemy import (
    Column,
    String,
)
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.decl_api import DeclarativeMeta

from app.config import settings


engine = create_async_engine(settings.DATABASE_URL)
Base: DeclarativeMeta = declarative_base()
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)


class Titles(Base):
    __tablename__ = "titles"
    id = Column(String(100), primary_key=True)
    title = Column(String(100), unique=True)


async def get_session() -> AsyncSession:
    async with async_session() as session:
        yield session

routers.py

import .database
from fastapi_utils.cbv import cbv
from fastapi_utils.inferring_router import InferringRouter


router = InferringRouter()


async def get_titles(session: AsyncSession):
    results = await session.execute(select(database.Titles)))
    return results.scalars().all()


@cbv(router)
class TitlesView:
    session: AsyncSession = Depends(database.get_session)

    @router.get("/titles", status_code=HTTP_200_OK)
    async def get(self) -> List[TitlesSchema]:
        results = await get_titles(self.session)
        return [TitlesSchema.from_orm(result) for result in results]

main.py

from fastapi import FastAPI

from app.routers import router 


def create_app() -> FastAPI:
    app = FastAPI()
    app .include_router(routers, prefix="/", tags=["Titles"])

    return printer_app


app = create_app()

It runs with docker:

CMD ["uvicorn", "main:app", "--reload", "--host", "0.0.0.0", "--port", "8000", "--limit-max-requests", "10000"]

And it has Postgres database with default settings in docker too. It all runs at docker-swarm. Works fine at first, accepts all requests. But if you leave it for 15-30 minutes (I did not count), and then make a request, it will not work:

<class 'asyncpg.exceptions.ConnectionDoesNotExistError'>: connection was closed in the middle of operation

And right after that I send the next request and it doesn't throw an error. What could it be? How do I get rid of the ConnectionDoesNotExistError?

Upvotes: 5

Views: 5714

Answers (3)

Dmitry
Dmitry

Reputation: 357

I solve that using pool_pre_ping setting like that:

engine = create_async_engine(DB_URL, pool_pre_ping=True)

https://docs.sqlalchemy.org/en/14/core/pooling.html

Upvotes: 7

NikGovn
NikGovn

Reputation: 51

I have the same problem with asyncpg. Solve problem by this part of code (docs - https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html#using-multiple-asyncio-event-loops )

from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.pool import NullPool

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@host/dbname",
    poolclass=NullPool,
)

Upvotes: -1

alex_noname
alex_noname

Reputation: 32143

I will quote the answer from here, I think it might be useful. All credit to q210.

In our case, the root cause was that ipvs, used by swarm to route packets, have default expiration time for idle connections set to 900 seconds. So if connection had no activity for more than 15 minutes, ipvs broke it. 900 seconds is significantly less than default linux tcp keepalive setting (7200 seconds) used by most of the services that can send keepalive tcp packets to keep connections from going idle.

The same problem is described here moby/moby#31208

To fix this we had to set the following in postgresql.conf:

tcp_keepalives_idle = 600               # TCP_KEEPIDLE, in seconds;
                                       # 0 selects the system default
tcp_keepalives_interval = 30            # TCP_KEEPINTVL, in seconds;
                                       # 0 selects the system default
tcp_keepalives_count = 10               # TCP_KEEPCNT;
                                       # 0 selects the system default

These settings are forcing PostgreSQL to keep connections from going idle by sending keepalive packets more often than ipvs default setting (that we can't change in docker-swarm, sadly).

I guess the same could be achieved by changing corresponding linux settings (net.ipv4.tcp_keepalive_time and the like), 'cause PostgreSQL uses them by default, but in our case changing these was a bit more cumbersome.

Upvotes: 1

Related Questions