PG14中的idle_session_timeout
新添加的配置参数idle_session_timeout和idle_in_transaction_session_timeout类似，一个客户端等待查询时间太长，此时可通过这个时间超时杀死该客户端会话。但是该参数不在事务中生效。关于邮件列表讨论：https://postgr.es/m/763A0689-F189-459E-946F-F0EC4458980B@hotmail.com。描述很清晰。该参数信息：
$ SELECT * FROMpg_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 TIMEBETWEEN 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 //设置该参数后： $ SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 ROW) $ SELECT 1; ?COLUMN? ---------- 1 (1 ROW) $ SELECT * FROMpg_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 TIMEBETWEEN queries, WHEN NOT IN a TRANSACTION. extra_desc | A VALUE OF 0 turns offthe 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
之后，不做任何操作，10s后，错误日志中可以看到：
2021-01-1211:33:38.892 CET depesz@depesz 61973 [local] LOG: duration: 2.827 ms statement: select * from pg_settings wherename = 'idle_session_timeout' 2021-01-1211:33:48.892 CET depesz@depesz 61973 [local] FATAL: terminating connection due to idle-sessiontimeout 2021-01-1211:33:48.892 CET depesz@depesz 61973 [local] LOG: disconnection: session time: 0:01:59.743user=depesz database=depesz host=[local]
Psql会话不会看到任何东西，当执行一个查询时会看到断开：
$ SELECT 1; FATAL: terminating connection due TO idle-SESSIONtimeout server closed theconnection unexpectedly This probably means the server TERMINATEDabnormally BEFORE OR whileprocessing the request. The connection TOthe server was lost. Attempting reset: Succeeded.
为了增加说服力，在事务中操作：
$ 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)
正如所见，50s后还没断开。该值最大值未2147483647，单位毫秒，意味着最大空闲会话超时可以时24天、20小时、31分钟和23秒，有足够长时间检测空闲连接。
原文
https://www.depesz.com/2021/01/12/waiting-for-postgresql-14-add-idle_session_timeout/
