【译】求助！我无法取消 POSTGRESQL 查询！

原创外瑞古德 2022-06-23

1458

原文链接：https://www.cybertec-postgresql.com/en/cancel-hanging-postgresql-query/
作者：Laurenz Albe

有时 PostgreSQL 查询需要很长时间。并且很容易中断，但有时会无法取消查询。本文讨论了可能存在的原因。我还将向您展示如何解决此问题的技巧。

如何取消正在运行的查询？

PostgreSQL 协议有中断正在运行的语句的规定。这是通过打开一个新连接并发送带有密钥的 CancelRequest 消息来完成的。该密钥是在原始连接开始期间由服务器发送的。如果没有该密钥，每个人都可以取消您的查询，这将是一个不可接受的安全问题。

C 库 libpq 提供了函数 PQgetCancel()和 PQcancel()来取消查询，其他数据库 API 应该有类似的规定。在交互式 psql 会话中，您可以简单地按 Ctrl+C 来发送取消请求，并且 GUI 客户端通常有一个用于此目的的按钮。

但是也可以通过调用数据库函数 pg_cancel_backend()来取消其他人的查询。另一种选择是 pg_terminate_backend()，它更进一步并终止其他人的数据库会话。为了能够使用这些功能，您必须是超级用户或默认角色 pg_signal_backend 的成员（下一节将解释原因），或者您必须以与要连接的会话相同的数据库用户身份连接到数据库（您可以取消自己的声明）。

服务器如何响应取消请求?
PostgreSQL 中的进程间通信很大程度上依赖于信号。

当 postmaster 进程收到一个CancelRequest 时，它会向相应数据库会话的后端进程发送信号 SIGINT。这也是函数 pg_cancel_backend() 所做的。pg_terminate_backend() 发送信号 SIGTERM。

现在每个 PostgreSQL 进程都有一个信号处理程序，在收到这些信号时处理这些信号。此信号处理程序不会立即中断后端进程，但会为进程设置全局变量。 SIGINT 将设置 QueryCancelPending，而 SIGTERM 将设置 ProcDiePending。这些变量充当标志，后端进程有责任在方便时尽快对它们做出反应。这确保了在不方便的时间不会中断任何进程，例如，当共享内存处于不一致状态时。

取消查询可能不起作用的原因?

有几个可能的原因：

执行卡在不包含 CHECK_FOR_INTERRUPTS() 的循环中。那将是一个 PostgreSQL 错误，修复方法是添加另一个对宏的调用。
执行卡在 SQL 语句中调用的第三方 C 函数中。在这种情况下，您应该将其作为错误报告给函数的作者。
执行卡在无法中断的系统调用中。这表明操作系统或硬件级别存在问题。请注意，当进程在内核空间中时，信号的传递会被推迟。

除非你绝望，否则不要使用 kill -9

在 PostgreSQL 后端进程上使用普通 kill 是非常好的。这将发送 SIGTERM 信号，因此它与为该后端调用 pg_terminate_backend() 相同。如果这没有效果，那么很容易使用 kill -9，它会发送 SIGKILL。该信号无法被捕获并立即终止该过程。问题是邮局主管会检测其子进程之一是否没有完全关闭。然后它将杀死所有其他 PostgreSQL 进程并进行崩溃恢复，这会导致整个数据库中断，这可能需要几秒钟到几分钟的时间。

请注意，虽然在后端使用 kill -9 会缩短停机时间，但在 postmaster 进程本身上使用 kill -9 会产生更糟糕的影响，因此应不惜一切代价避免使用。它打开了一个时间窗口，在此期间可以启动新的 postmaster，而旧 postmaster 的一些孩子还活着，这可能会导致磁盘上的数据损坏。永远不要用 kill -9 杀死 postmaster 进程！

有时甚至 kill -9 也无法杀死 PostgreSQL 后端。这意味着后端卡在不间断的系统调用中，例如在不再可用的网络附加存储上执行 I/O。如果这种情况持续存在，摆脱该进程的唯一方法是重新启动操作系统。

在不使服务器崩溃的情况下取消卡住的查询的技巧

有时您可以通过以下操作来避免崩溃恢复和中断。此示例使用 Linux 上的 GNU 调试器；您必须根据需要使其适应其他环境。

一个挂起函数的例子

我们编写了这个简单的 C 函数（源文件 loop.c）：

#include "postgres.h"
#include "fmgr.h"
 
#include <unistd.h>
 
PG_MODULE_MAGIC;
 
PG_FUNCTION_INFO_V1(loop);
 
Datum loop(PG_FUNCTION_ARGS)
{
    /* an endless loop */
    while(1)
        sleep(2);
}

像这样构建共享库（根据需要更改包含路径）：

gcc -I /usr/pgsql-14/include/server \
    -fPIC -shared -o loop.so loop.c

并将文件复制到 PostgreSQL 共享库目录（您可以使用“pg_config --libdir”获取）。

定义和调用函数

将 SQL 中的函数定义为超级用户：

CREATE FUNCTION loop() RETURNS void
   LANGUAGE c AS 'loop';

然后，作为任意用户，调用该函数：

SELECT loop();

执行将挂起。您可以尝试取消查询，但它会继续运行。

识别挂起的后端进程并向其发送终止信号

使用相同的数据库用户打开另一个数据库连接。然后找出后台进程的进程ID，它标识了数据库会话：


SELECT pid, query
FROM pg_stat_activity
WHERE query LIKE '%loop%';

知道进程 ID 后，向进程发送 SIGTERM：

SELECT pg_terminate_backend(12345);

当然，参数应该是上一步得到的进程ID。该函数返回 TRUE，因为信号已发送，但查询继续执行。

使用调试器附加

确保安装了 GNU 调试器 gdb。要获得可读的堆栈跟踪，您应该安装 PostgreSQL 服务器的调试符号，尽管这对于我将向您展示的技巧不是必需的。以 PostgreSQL 用户身份登录数据库服务器机器，通常称为 postgres。像这样调用 gdb（使用 postgres 可执行文件的正确路径和正确的进程 ID）：

gdb /usr/pgsql-14/bin/postgres 12345

一旦你得到提示“(gdb)”，你就会使用命令“bt”生成一个堆栈跟踪。这将类似于以下内容：

__GI___clock_nanosleep (clock_id=clock_id@entry=0, flags=flags@entry=0, 
    req=req@entry=0x7ffdaf61cde0, rem=rem@entry=0x7ffdaf61cde0)
    at ../sysdeps/unix/sysv/linux/clock_nanosleep.c:71
#1  0x00007f113d864897 in __GI___nanosleep (req=req@entry=0x7ffdaf61cde0, 
    rem=rem@entry=0x7ffdaf61cde0) at ../sysdeps/unix/sysv/linux/nanosleep.c:25
#2  0x00007f113d8647ce in __sleep (seconds=0) at ../sysdeps/posix/sleep.c:55
#3  0x00007f113e623139 in loop () from /usr/pgsql-14/lib/loop.so
#4  0x00000000006d71fb in ExecInterpExpr (state=0x13837b8, econtext=0x13834e0, 
    isnull=<optimized out>) at executor/execExprInterp.c:1260
#5  0x000000000070e391 in ExecEvalExprSwitchContext (isNull=0x7ffdaf61ced7, 
    econtext=0x13834e0, state=0x13837b8)
    at executor/../../../src/include/executor/executor.h:339
#6  ExecProject (projInfo=0x13837b0)
    at executor/../../../src/include/executor/executor.h:373
#7  ExecResult (pstate=<optimized out>) at executor/nodeResult.c:136
#8  0x00000000006da8b2 in ExecProcNode (node=0x13833d0)
    at executor/../../../src/include/executor/executor.h:257
#9  ExecutePlan (execute_once=<optimized out>, dest=0x137f4c0, direction=<optimized out>, 
    numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, 
    use_parallel_mode=<optimized out>, planstate=0x13833d0, estate=0x13831a8)
    at executor/execMain.c:1551
[...]

堆栈跟踪是确定问题所在的最好的帮助。

如果您不想执行下一步，您可以退出并输入“detach”以将调试器从进程中分离并允许它继续。

通过使挂起的后端干净地退出来取消执行

上面的堆栈跟踪显示当前执行不在postgreSQL代码中，而是在自定义函数中（在/usr/pgsql-14/lib/loop.so中的loop() 中）。这意味着让进程退出是非常安全的。如果执行是在 PostgreSQL 服务器内部的某个地方，那么 PostgreSQL 可能正在修改共享状态、持有自旋锁或类似的东西的风险很小。如果您知道 PostgreSQL 源代码，那么查看调用堆栈将帮助您评估该风险。现在，如果你敢，请调用ProcessInterrupts()，这将导致进程退出，因为设置了ProcDiePending`：

(gdb) print ProcessInterrupts()
[Inferior 1 (process 12345) exited with code 01]
The program being debugged exited while in a function called from GDB.
Evaluation of the expression containing the function
(ProcessInterrupts) will be abandoned.
(gdb) quit

修复允许用户取消执行的功能

为了改善这种情况，功能代码应该这样修改：

#include "postgres.h"
#include "fmgr.h"
#include "miscadmin.h"
 
#include <unistd.h>
 
PG_MODULE_MAGIC;
 
PG_FUNCTION_INFO_V1(loop);
 
Datum loop(PG_FUNCTION_ARGS)
{
    /* an endless loop */
    while(1)
    {
        CHECK_FOR_INTERRUPTS();
        sleep(2);
    }
}

上述更改将导致该函数每两秒检查一次中断，以便可以安全地取消执行。

结论

通过向后端发送 SIGINT 信号来取消查询。如果这和发送 SIGTERM 都不能中断后端，您可以使用 gdb 附加到挂起的后端并直接调用 ProcessInterrupts() 以使其退出。

postgresql 墨力翻译

最后修改时间：2022-06-23 14:13:26

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

【译】求助！我无法取消 POSTGRESQL 查询！

评论