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_timeoutsetting | 0unit | mscategory | Client Connection Defaults /Statement Behaviorshort_desc | Sets the maximum allowed idle TIMEBETWEEN queries, WHEN NOT IN a TRANSACTION.extra_desc | A VALUE OF 0 turns off the timeout.context | USERvartype | INTEGERSOURCE | DEFAULTmin_val | 0max_val | 2147483647enumvals | [NULL]boot_val | 0reset_val | 0sourcefile | [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_timeoutsetting | 10000unit | mscategory | Client Connection Defaults /Statement Behaviorshort_desc | Sets the maximum allowed idle TIMEBETWEEN queries, WHEN NOT IN a TRANSACTION.extra_desc | A VALUE OF 0 turns offthe timeout.context | USERvartype | INTEGERSOURCE | configuration filemin_val | 0max_val | 2147483647enumvals | [NULL]boot_val | 0reset_val | 10000sourcefile | /home/pgdba/DATA/postgresql.auto.confsourceline | 3pending_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-sessiontimeout2021-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-SESSIONtimeoutserver 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秒,有足够长时间检测空闲连接。
参与评论
登录后参与讨论 0/1000