PostgreSQL already comes with various time out parameters when it comes to sessions and statements. There is idle_in_transaction_session_timeout, idle_session_timeout, and there is statement_timeout. All of them are disabled by default but can be turned on to prevent either long running sessions or statements. Starting with PostgreSQL 17 there will be another time out related parameter: transaction_timeout. As the name implies, this one applies on the transaction level.

An easy test to see how this works is this:

postgres=# set transaction_timeout = '5s';
SET
postgres=# begin;
BEGIN
postgres=*# select pg_sleep(6);
FATAL:  terminating connection due to transaction timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=#

If idle_in_transaction_session_timeout and statement_timeout are set as well but transaction_timeout is set to a shorter time, then transaction_timeout will be the one which counts:

postgres=# set idle_in_transaction_session_timeout = '10s';
SET
postgres=# set statement_timeout = '10s';
SET
postgres=# set transaction_timeout = '5s';
SET
postgres=# begin;
BEGIN
postgres=*# select pg_sleep(6);
FATAL:  terminating connection due to transaction timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=#

Be aware that setting this on the instance level (postgresql.conf or postgresql.auto.conf) will make this active for all transactions and this is probably not what you want. Use it with care where you really need.

Post Views: 383
Thumbnail [60x60]
by
Daniel Westermann