idle_in_transaction_session_timeout

A server configuration parameter determining the length of time after which sessions with open transactions are terminated

idle_in_transaction_session_timeout is a server configuration parameter determining the length of time after which sessions with open transactions are terminated. It is disabled by default.

idle_in_transaction_session_timeout was added in PostgreSQL 9.6.

Default

Default value for idle_in_transaction_session_timeout is: 0 (disabled).

Change history

Source code

GUC variable in src/backend/storage/lmgr/proc.c:

int                     IdleInTransactionSessionTimeout = 0;

GUC configuration in src/backend/utils/misc/guc.c:

	{
		{"idle_in_transaction_session_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT,
			gettext_noop("Sets the maximum allowed duration of any idling transaction."),
			gettext_noop("A value of 0 turns off the timeout."),
			GUC_UNIT_MS
		},
		&IdleInTransactionSessionTimeout,
		0, 0, INT_MAX,
		NULL, NULL, NULL
	},

The timeout is initiated and executed in PostgresMain() (src/backend/tcop/postgres.c).

Examples

Demonstrating the effect of idle_in_transaction_session_timeout with an unrealistically short setting of 5s:

postgres=# SET idle_in_transaction_session_timeout = '5s';
SET

postgres=# SHOW idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout 
-------------------------------------
 5s
(1 row)
postgres=# SELECT clock_timestamp(); clock_timestamp ---------------------------- 2019-08-06 16:32:44.603+01 (1 row) Time: 1.772 ms postgres=# BEGIN ; BEGIN

... do nothing for at least 5 seconds ...

postgres=*# SELECT 1;
FATAL:  terminating connection due to idle-in-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=# SELECT clock_timestamp();
       clock_timestamp        
------------------------------
 2019-08-06 16:32:52.92625+01
(1 row)

There will be an entry similar to the following in the server's log file:

[2019-08-06 16:32:50 CET] psql postgres postgres FATAL:  25P03: terminating connection due to idle-in-transaction timeout

Categories

GUC configuration item, User management