作者
digoal
日期
2021-04-03
标签
PostgreSQL , check_client_connection_interval , long query , pool , POLLHUP , POLLRDHUP
背景
你有没有遇到过这种情况, 执行一个计算量或者扫描量特别大的SQL, 然后发现后悔了, pg_cancel或者pg_terminate都结束不掉, 就算客户端退出, SQL还在后台跑, 还在浪费资源, 只有等到执行完成或者执行到接收中断信号的时候才能被cancel.
PostgreSQL 14 preview - 协议层支持心跳包, 如果客户端已离线 可以 快速中断运行中的长SQL - Detect POLLHUP/POLLRDHUP while running queries.
注意这个功能和tcp心跳不太一样, tcp心跳解决的是允许long query的过程中, 由于可能长时间链路中都没有数据包传输, 中间的网络设备可能会主动kill这种会话, 导致long query执行失败.
《Linux Mac ssh 客户端长连接防断连 - tcp心跳 TCPKeepAlive,ServerAliveInterval,ServerAliveCountMax》
```
- TCP settings -
see "man tcp" for details
tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default复制
tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default复制
tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default复制
tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds;
# 0 selects the system default复制
```
PostgreSQL 14 增加了一个功能, 每隔一段时间检查客户端是否已离线, 如果离线则快速结束未完成query, 防止客户端已离线而数据库继续run未完成query. 间隔时间通过参数check_client_connection_interval控制.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c30f54ad732ca5c8762bb68bbe0f51de9137dd72
```
+#client_connection_check_interval = 0 # time between checks for client
+ # disconnection while running queries;
+ # 0 for never
- +/*
-
- Check if the client is still connected.
- */
+bool
+pq_check_connection(void)
+{
+#if defined(POLLRDHUP) - /*
-
- POLLRDHUP is a Linux extension to poll(2) to detect sockets closed by
-
- the other end. We don't have a portable way to do that without
-
- actually trying to read or write data on other systems. We don't want
-
- to read because that would be confused by pipelined queries and COPY
-
- data. Perhaps in future we'll try to write a heartbeat message instead.
- */
- struct pollfd pollfd;
- int rc;
- pollfd.fd = MyProcPort->sock;
- pollfd.events = POLLOUT | POLLIN | POLLRDHUP;
- pollfd.revents = 0;
- rc = poll(&pollfd, 1, 0);
- if (rc < 0)
- {
- ereport(COMMERROR,
- (errcode_for_socket_access(),
- errmsg("could not poll socket: %m")));
- return false;
- }
- else if (rc == 1 && (pollfd.revents & (POLLHUP | POLLRDHUP)))
- return false;
+#endif -
return true;
+} -
client_connection_check_interval (integer )client_connection_check_interval configuration parameter- Sets the time interval between optional checks that the client is still
- connected, while running queries. The check is performed by polling
- the socket, and allows long running queries to be aborted sooner if
- the kernel reports that the connection is closed.
- This option is currently available only on systems that support the
- non-standard
POLLRDHUP extension to the poll system call, including Linux.- If the value is specified without units, it is taken as milliseconds.
- The default value is
0 , which disables connection - checks. Without connection checks, the server will detect the loss of
- the connection only at the next interaction with the socket, when it
- waits for, receives or sends data.
- For the kernel itself to detect lost TCP connections reliably and within
- a known timeframe in all scenarios including network failure, it may
- also be necessary to adjust the TCP keepalive settings of the operating
- system, or the
, and settings of PostgreSQL .
Detect POLLHUP/POLLRDHUP while running queries.
author Thomas Munro tmunro@postgresql.org
Fri, 2 Apr 2021 19:52:30 +0000 (08:52 +1300)
committer Thomas Munro tmunro@postgresql.org
Fri, 2 Apr 2021 20:02:41 +0000 (09:02 +1300)
commit c30f54ad732ca5c8762bb68bbe0f51de9137dd72
tree 5086aba82cce9832ed537b726af643d8edf9b89c tree
parent 174edbe9f9c1538ab3347474e96d176223591cd1 commit | diff
Detect POLLHUP/POLLRDHUP while running queries.
Provide a new GUC check_client_connection_interval that can be used to
check whether the client connection has gone away, while running very
long queries. It is disabled by default.
For now this uses a non-standard Linux extension (also adopted by at
least one other OS). POLLRDHUP is not defined by POSIX, and other OSes
don't have a reliable way to know if a connection was closed without
actually trying to read or write.
In future we might consider trying to send a no-op/heartbeat message
instead, but that could require protocol changes.
Author: Sergey Cherkashin s.cherkashin@postgrespro.ru
Author: Thomas Munro thomas.munro@gmail.com
Reviewed-by: Thomas Munro thomas.munro@gmail.com
Reviewed-by: Tatsuo Ishii ishii@sraoss.co.jp
Reviewed-by: Konstantin Knizhnik k.knizhnik@postgrespro.ru
Reviewed-by: Zhihong Yu zyu@yugabyte.com
Reviewed-by: Andres Freund andres@anarazel.de
Reviewed-by: Maksim Milyutin milyutinma@gmail.com
Reviewed-by: Tsunakawa, Takayuki/綱川 貴之 tsunakawa.takay@fujitsu.com
Reviewed-by: Tom Lane tgl@sss.pgh.pa.us (much earlier version)
Discussion: https://postgr.es/m/77def86b27e41f0efcba411460e929ae%40postgrespro.ru
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
