Waiting for PostgreSQL 14 – Add idle_session_timeout.

On 6th of January 2021, Tom Lane committed patch:

Add idle_session_timeout.
 
This GUC variable works much like idle_in_transaction_session_timeout,
in that it kills sessions that have waited too long for a new client
query.  But it applies when we're not in a transaction, rather than
when we are.
 
Li Japin, reviewed by David Johnston and Hayato Kuroda, some
fixes by me
 
Discussion: https://postgr.es/m/763A0689-F189-459E-946F-F0EC4458980B@hotmail.com

Description is clear, I think, so let's just see it in action.

Before, to make sure about units:

$ SELECT * FROM pg_settings WHERE name = 'idle_session_timeout' \gx
-[ RECORD 1 ]---+-------------------------------------------------------------------------------
name            | idle_session_timeout
setting         | 0
unit            | ms
category        | Client Connection Defaults / Statement Behavior
short_desc      | Sets the maximum allowed idle TIME BETWEEN queries, WHEN NOT IN a TRANSACTION.
extra_desc      | A VALUE OF 0 turns off the timeout.
context         | USER
vartype         | INTEGER
SOURCE          | DEFAULT
min_val         | 0
max_val         | 2147483647
enumvals        | [NULL]
boot_val        | 0
reset_val       | 0
sourcefile      | [NULL]
sourceline      | [NULL]
pending_restart | f

OK. So, let's set the value to something short-ish, reload conf, and try to make it happen:

$ SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 ROW)
 
$ SELECT 1;
 ?COLUMN? 
----------
        1
(1 ROW)
 
$ SELECT * FROM pg_settings WHERE name = 'idle_session_timeout' \gx
-[ RECORD 1 ]---+-------------------------------------------------------------------------------
name            | idle_session_timeout
setting         | 10000
unit            | ms
category        | Client Connection Defaults / Statement Behavior
short_desc      | Sets the maximum allowed idle TIME BETWEEN queries, WHEN NOT IN a TRANSACTION.
extra_desc      | A VALUE OF 0 turns off the timeout.
context         | USER
vartype         | INTEGER
SOURCE          | configuration file
min_val         | 0
max_val         | 2147483647
enumvals        | [NULL]
boot_val        | 0
reset_val       | 10000
sourcefile      | /home/pgdba/DATA/postgresql.auto.conf
sourceline      | 3
pending_restart | f

After this, I didn't do anything. And after 10 seconds, I saw in logs:

2021-01-12 11:33:38.892 CET depesz@depesz 61973 [local] LOG:  duration: 2.827 ms  statement: select * from pg_settings where name = 'idle_session_timeout' 
2021-01-12 11:33:48.892 CET depesz@depesz 61973 [local] FATAL:  terminating connection due to idle-session timeout
2021-01-12 11:33:48.892 CET depesz@depesz 61973 [local] LOG:  disconnection: session time: 0:01:59.743 user=depesz database=depesz host=[local]

In the psql session I didn't see immediately anything, as it's always the case with connection drop, but when I tried to run a query:

$ SELECT 1;
FATAL:  terminating connection due TO idle-SESSION 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.

For sanity sake I tried also with transaction:

$ BEGIN;
BEGIN
 
*$ SELECT now();
              now              
-------------------------------
 2021-01-12 11:36:32.131091+01
(1 ROW)
 
...
 
*$ SELECT clock_timestamp(), now(), clock_timestamp() - now();
        clock_timestamp        |              now              |    ?COLUMN?     
-------------------------------+-------------------------------+-----------------
 2021-01-12 11:37:23.368718+01 | 2021-01-12 11:36:32.131091+01 | 00:00:51.237627
(1 ROW)

As you can see despite the fact that I was idle for ~ 50 seconds, the connection wasn't dropped.

Just for completeness sake – since the value can be set up to 2147483647, and the unit is miliseconds, that means that the max idle_session_timeout can be 24 days, 20 hours, 31 minutes, and ~ 23 seconds. Long enough for detection of idle connections.

I think it will help dbas to get rid of useless connections, and, as usual, thanks to all involved.