I have a python app using psycop2 to communicate with a fly postgres database. After some time of inactivity, Once I try to querry the database I get
psycopg2.OperationalError: server closed the connection unexpectedly
Any ideas why this might happen? I have another connection in the app to the same database which seems to work fine (it is kept active). Do Postgres machines automatically close connections after some time of inactivity?
Yes, the connection is closed after a period of inactivity. There are probably good ways to avoid this problem.
In my case I had a bot that runs queries against a database in response to user commands. I changed the code so that in response to all commands the first thing it always did was to attempt to get the user’s data from the database (which it needed anyway most of the time)
I then added a try/except block that caught connection errors, rolled back the current transaction (I’m using a framework that wraps each request in a transaction), attempted one time to reconnect to the database and then re-ran the query.
try:
user = db.session.query(User).filter(Users.id == user_id).first()
except sqlalchemy.exc.OperationalError:
# we sometimes loose connection with the database and have to reconnect
# This is the first db query we make, so attempt to reconnect, one time only
log.warning("Lost connection to db - attempting to reconnect")
# sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back.
# (Background on this error at: https://sqlalche.me/e/14/8s2b)
db.session.rollback()
time.sleep(1)
db.session.begin()
user = db.session.query(User).filter(Users.id == user_id).first()
log.info("Reconnected to db")
This is kind of a dumb, clumsy way to do it, but it’s simple and good enough for us.
Thanks. Is this something specific to fly postgres? sharing VMs? sharing CPUs?
dedicated postgres app (meaning that it runs in a different VM)
I’ve also had to do something similiar to restore my connection to Redis due to timeouts on a quiet connection