A look at PostgreSQL 9.6 – Killing idle transactions automatically

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 🙂