Transaction time out workaround for PostgreSQL

Question

AFAIK, PostgreSQL 8.3 does not support transaction time out. I've read about supporting this feature in the future and there's some discussion about it. However, for specific reasons, I need a solution for this problem. So what I did is a script that runs periodically:

1) Based on locks and activity, query in order to retrieve processID of the transactions that is taking too long, and keeping the oldest (trxTimeOut.sql):

SELECT procpid
FROM
(
    SELECT DISTINCT age(now(), query_start) AS age, procpid
    FROM pg_stat_activity, pg_locks
    WHERE pg_locks.pid = pg_stat_activity.procpid
) AS foo
WHERE age > '30 seconds'
ORDER BY age DESC
LIMIT 1

2) Based on this query, kill the corresponding process (trxTimeOut.sh):

psql -h localhost -U postgres -t -d test_database -f trxTimeOut.sql | xargs kill

Although I've tested it and seems to work, I'd like to know if it's an acceptable approach or should I consider a different one?

Answer 1

PostgreSQL provides idle_in_transaction_session_timeout since version 9.6, to automatically terminate transactions that are idle for too long.

It's also possible to set a limit on how long a command can take, through statement_timeout, independently on the duration of the transaction it's in, or why it's stuck (busy query or waiting for a lock).

To auto-abort transactions that are stuck specifically waiting for a lock, see lock_timeout.

These settings can be set at the SQL level with commands like SET shown below, or can be set as defaults to a database with ALTER DATABASE, or to a user with ALTER USER, or to the entire instance through postgresql.conf.

SET statement_timeout=10000;   -- time out after 10 seconds