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.