合 PG和GreenPlum中的statement_timeout、lock_timeout、idle_in_transaction_session_timeout、idle_session_timeout等常用的 timeout 参数
Tags： PGGreenPlumPostgreSQL整理自网络参数PG14statement_timeouttimeoutlock_timeoutidle_session_timeoutidle_in_transaction_session_timeoutclient_connection_check_interval
简介
PostgreSQL 有很多与超时相关的参数可供用户设置，包括:
- statement_timeout: Sets the maximum allowed duration of any statement. 用于限制单个 query 执行的时间 在PG 9.4中就提供了。
- lock_timeout：Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object. 在PG 9.4中就提供了。
- idle_in_transaction_session_timeout: Sets the maximum allowed idle time between queries, when in a transaction. 用于限制在事务中闲置的时长 在PG 9.6中就提供了。
- idle_session_timeout: Sets the maximum allowed idle time between queries, when not in a transaction. 用于限制连接处于空间的时长 PG 14新增
- client_connection_check_interval：Sets the time interval between optional checks that the client is still connected, while running queries. PG 14新增
statement_timeout
在PG 9.4中就提供了。
PostgreSQL 中长事务往往会带来一些问题，比如 table bloat（由于旧版本记录不能及时回收）、占用资源（锁、内存）等，因此有些实例会设置一个合理的 statement_timeout 来自动杀死运行时间过长的查询。
1 2 3 4 5 | postgres=# set session statement_timeout = '10s'; SET postgres=# select pg_sleep(1000); ERROR: canceling statement due to statement timeout postgres=# |
但这个参数对于 idle in transaction 的 session 不起作用，比如:
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=# set application_name = 'pgdb'; SET postgres=# set session statement_timeout = '10s'; SET postgres=# create table t1(id int); CREATE TABLE postgres=# insert into t1 values(1),(2); INSERT 0 2 postgres=# begin; BEGIN postgres=*# update t1 SET id=2 where id=1; UPDATE 1 |
开启事务但一直不 commit，该 session 一直处于 idle in transaction 状态，在另一个客户端查询:
1 2 3 4 5 | postgres=# select pid, application_name, xact_start, query_start, state from pg_stat_activity where application_name='pgdb'; pid | application_name | xact_start | query_start | state --------+------------------+-------------------------------+-------------------------------+--------------------- 201736 | pgdb | 2022-02-26 16:09:10.389653+00 | 2022-02-26 16:10:17.354243+00 | idle in transaction (1 row) |
statement_timeout (integer)
中止任何使用了超过指定时间量的语句。 如果log_min_error_statement被设置为ERROR或更低，语句如果超时也会被记录。 如果指定值时没有单位，则以毫秒为单位。一个零值（默认）将禁用超时。
超时从命令到达服务器的时间开始计算，一直到它被服务器完成为止。 如果单个简单查询消息中出现多个 SQL 语句，超时将分别应用于每个语句。（PostgreSQL 13 之前的版本通常将超时应用于整个查询字符串。 在扩展查询协议中，当任何与查询相关的消息(解析、绑定、执行、描述)到达时，超时就开始运行，并且可以通过执行完成或同步消息来取消超时。
我们不推荐在postgresql.conf中设置statement_timeout，因为它会影响所有会话。
lock_timeout
在PG 9.4中就提供了。
lock_timeout (integer)
如果任何语句在试图获取表、索引、行或其他数据库对象上的锁时等到超过指定的时间量，该语句将被中止。 该时间限制独立地应用于每一次锁获取尝试。该限制会应用到显式锁定请求（如LOCK TABLE或不带NOWAIT的SELECT FOR UPDATE）和隐式获得的锁。 如果指定值时没有单位，则以毫秒为单位。一个零值（默认）将禁用超时。
与statement_timeout不同，这个超时只在等待锁时发生。注意如果statement_timeout为非零，设置lock_timeout为相同或更大的值没有意义，因为事务超时将总是第一个被触发。 如果log_min_error_statement 被设置为ERROR 或更低，超时的语句将被记录。
我们不推荐在postgresql.conf中设置lock_timeout，因为它会影响所有会话。
1 2 3 4 5 | db1=# show lock_timeout; lock_timeout -------------- 0 (1 行记录) |
idle_in_transaction_session_timeout
idle_in_transaction_session_timeout是9.6中新增的参数。
