SQLAlchemy Operational Errors

by

I am running a Flask application and have been getting this error when someone tries to access the application after about a day of inactivity:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SSL connection has been closed unexpectedly

The app then completely stops working until it’s restarted, throwing this error when the request is retried:

sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back. (Background on this error at: https://sqlalche.me/e/14/8s2b)

I’ve already tried adding keepalive arguments into the connection

db = SQLAlchemy(app, engine_options={'connect_args': {
'keepalives': 1,
'keepalives_idle': 30,
'keepalives_interval': 10,
'keepalives_count': 5
}})

I also have configured the app to use the pool_pre_ping option:

app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {"pool_pre_ping": True}

And this is the query that’s failing:

stmt = db.session.query(User).filter(User.username == username)
account = db.session.scalars(stmt).first()

How can I prevent these operational errors and subsequent PendingRollbackErrors from happening?

  • What versions of sqlalchemy and flask-sqlalchemy are you using?

    – 

  • Also I think you are mixing the query api with the newer select based api. I think stmt should be stmt = select(User).where(User.username == username). I don’t see how that would cause those exceptions though instead of just failing immediately but maybe you using some hybrid < 2. edit: removed extra paren

    – 




Leave a Comment