Saturday, October 3, 2009

Implementing wait_timeout in PostgreSQL

In MySQL, the wait_timeout setting can be used to automatically close connections that have been inactive for a certain amount of time.  The default is 8 hours.

Unfortunately, there is no equivalent in PostgreSQL (as of version 8.3).  However, it's easy to implement.  You just need to use the query_start column (of the pg_stat_activity view) to determine when a connection was last used.

Here is an example using Bash:


/usr/bin/psql -U postgres -t -c "SELECT procpid FROM pg_stat_activity WHERE (NOW() - query_start) > INTERVAL '$WAIT_TIMEOUT_MINUTES MINUTES' AND current_query = '<IDLE>'" | xargs kill

This script takes the timeout as a command-line parameter, and then kills off any connections that have been sitting idle for too long.

Add this to a cron job and you're all set.

If you're using PostgreSQL 8.4, then you can do this entirely using SQL, thanks to the new terminate_backend() function:

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE (NOW() - query_start) > INTERVAL 60 MINUTES' AND current_query = '<IDLE>'

This is especially useful if you're working in a Windows environment where there's no equivalent of xargs (that I know of), or if you don't have access to a command line (i.e. you're making a remote connection using psql) and therefore can't use kill.

Thanks to the anonymous commenter who pointed out an error in the original script.