Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL 9.6 idle_in_transaction_session_timeout parameter

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;
(1 row)
I’ll set it to one minute…

(postgres@[local]:5432) [postgres] > alter system set idle_in_transaction_session_timeout='1min';
Lets create a user:

(postgres@[local]:5432) [postgres] > create user u login password 'u';
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;
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 


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL Introduction