sqlalchemy random error Exception during reset or similar - no impact on functionality

Question

I have an application running and from time to time this stack trace shows in console. However the app continues to run smoothly. Anyway, I don't really like this kind of unpredictable behavior in the app. Can I avoid such error messages? All queries are returned with correct data, updates work fine. No idea why this message keeps popping in logs.

Exception during reset or similar
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 663, in _finalize_fairy
    fairy._reset(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _reset
    pool._dialect.do_rollback(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 631, in do_rollback
    dbapi_connection.rollback()
psycopg2.OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

This is how I create session and query example:

engine = create_engine(
    settings.SQLALCHEMY_DATABASE_URI,
    pool_pre_ping=True,
    echo=False,
    connect_args={"connect_timeout": 30},
    pool_size=20,
    max_overflow=100,
)
Base.metadata.create_all(engine)
SessionLocal = sessionmaker(expire_on_commit=True, autocommit=False, autoflush=False, bind=engine)

Query example:

def get_user_name(username: str, s=None) -> str:
    if s is None:
        s = SessionLocal()
    try:
        user_obj = s.query(User).filter_by(username=username)
        if len(list(user_obj)) < 1:
            return username
        return user_obj.first().first_name
    except Exception as ex:
        s.rollback()
        logger.exception(f"Failed while Error : {ex}")

Answer 1

According to this GitHub issue, the exception is typically logged due to connections not being returned to the pool and doesn't necessarily have to do something with the server. More precisely:

that exception is not actually "raising", it's just being logged. this is typically due to a database connection not being returned to the pool when the program ends, but it's not typical that it indicates the server closed the connection, so I would assume this is because Python shutdown is non-deterministic and the connection is in a bad state.

Try closing the session within a finally block:

def get_user_name(username: str, s=None) -> str:
    if s is None:
        s = SessionLocal()
    try:
        user_obj = s.query(User).filter_by(username=username)
        if len(list(user_obj)) < 1:
            return username
        return user_obj.first().first_name
    except Exception as ex:
        s.rollback()
        logger.exception(f"Failed while Error : {ex}")
    finally:
        s.close()