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.