Postgres 9.6 parameter idle_in_transaction_session_timeout
A common issue with databases: The guy at floor 2 did some work on the database and then rashly left for lunch because the lady on floor 1 is already waiting for him. Unfortunately he did not close his transaction and now his session is blocking others. In PostgreSQL this might be even worse as all tuples modified by this transaction are not available for vacuum until the transaction is either committed or aborted. In PostgreSQL 9.6 there will be a way to minimize this risk.
As always with PostgreSQL: If someone wants to add something to PostgreSQL core it starts with a mail tread. The result was a new parameter called idle_in_transaction_session_timeout. Lets see how this works.
The default value of idle_in_transaction_session_timeout is 0, which means disabled:
(postgres@[local]:5432) [postgres] > show idle_in_transaction_session_timeout; idle_in_transaction_session_timeout ------------------------------------- 0 (1 row)
I’ll set it to one minute…
(postgres@[local]:5432) [postgres] > alter system set idle_in_transaction_session_timeout='1min'; ALTER SYSTEM
Lets create a user:
(postgres@[local]:5432) [postgres] > create user u login password 'u'; CREATE ROLE
Using that user login and start a transaction without doing any further work:
postgres@centos7:/home/postgres/ [PG1] psql postgres u -W Password for user u: psql (9.6beta1 dbi services build) Type "help" for help. (u@[local]:5432) [postgres] > begin; BEGIN
Btw:Â Being idle in transaction is reported in the process list, too:
(postgres@[local]:5432) [postgres] > \! ps -ef | grep idle postgres 2712 2702 0 08:12 ? 00:00:00 postgres: postgres postgres [local] idle postgres 3072 2702 0 08:14 ? 00:00:00 postgres: u postgres [local] idle in transaction postgres 3412 2711 0 08:17 pts/0 00:00:00 sh -c ps -ef | grep idle postgres 3414 3412 0 08:17 pts/0 00:00:00 grep idle
After one minute this is reported in the log file:
2016-05-18 08:17:32.352 CEST - 1 - 3072 - [local] - u@postgres FATAL: terminating connection due to idle-in-transaction timeout
An attempt to do anything in the session that user u opened results in:
(u@[local]:5432) [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.
Works well … no more killing sessions because guys head for lunch
Login with Google