Forums

[Postgres] Idle connection terminate

My application generates idle state connections. if it exceed 20 connections my app crash. i tried to set the idle_in_transaction_session_timeout

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

but

show idle_in_transaction_session_timeout;

as the result gives 0. How to inspect what part on my app generates idle connections and how to automate terminating idle connections on postgres? i use flask and sqlalchemy.

According to the comments on this stack overflow post, that will

will only close connections that opened a transaction and failed to close (commit or rollback) it within the given timeout (as the name "idle_in_transaction_session_timeout" suggests). It won't close connections that are just "idle"

So unfortunately it doesn't sound like it's doing what you want to do.

I'm not sure that there is a way to get Postgres on PythonAnywhere to kill idle transactions that aren't in the middle of an open transaction, unfortunately.

I think that the best thing to do would be to shut down connections that are idle from your own code -- that could normally indicate a coding error of some kind, for example code that is not closing database connections when it's done with them.