一、生产场景
psql -Uroot -dpostgres -pxxx
psql (12.7)
Type "help" for help.
postgres=# SELECT pid, datname, usename, application_name, client_addr, state, now() - query_start AS exec_time, regexp_replace(query, E'[\n\r\t ]+', ' ', 'g') AS actual_query FROM pg_stat_activity WHERE pid = 25795;
pid | datname | usename | application_name | client_addr | state | exec_time | actual_query
-------+---------+---------+------------------+-------------+--------+----------------------+---------------------------------------------------------
25795 | appdb | appuser | Navicat | 192.0.2.10 | active | 1 day 23:22:09.36829 | SELECT "app_schema"."fun_example"('********', '********');
(1 row)
postgres=# \q
[postgres@db-node-03 ~]$ ps -o pid,ppid,stat,wchan:32,lstart,etime,comm -p 25795
PID PPID STAT WCHAN STARTED ELAPSED COMMAND
25795 5277 Ss inet_csk_accept Wed Nov 11 10:05:15 2026 1-23:22:13 postgres
[postgres@db-node-03 ~]$ pstack 25795
#0 0x00002b88f8acc9c0 in __accept_nocancel () from /lib64/libpthread.so.0
#1 0x00002b8e312a7ba1 in dbgcomm_listen_for_proxy () from /opt/postgresql/lib/postgresql/plugin_debugger.so
#2 0x00002b8e312a6aa5 in attach_to_proxy () from /opt/postgresql/lib/postgresql/plugin_debugger.so
#3 0x00002b8e312a50f8 in dbg_newstmt () from /opt/postgresql/lib/postgresql/plugin_debugger.so
#4 0x00002b8e314c6497 in exec_stmt () from /opt/postgresql/lib/postgresql/plpgsql.so
#5 0x00002b8e314c92e4 in exec_stmts () from /opt/postgresql/lib/postgresql/plpgsql.so
#6 0x00002b8e314c98e2 in exec_stmt_block () from /opt/postgresql/lib/postgresql/plpgsql.so
#7 0x00002b8e314c76c6 in exec_stmt () from /opt/postgresql/lib/postgresql/plpgsql.so
#8 0x00002b8e314c8ef6 in plpgsql_exec_function () from /opt/postgresql/lib/postgresql/plpgsql.so
#9 0x00002b8e314bce70 in plpgsql_call_handler () from /opt/postgresql/lib/postgresql/plpgsql.so
#10 0x0000000000602152 in ExecEvalFuncExprFusage ()
#11 0x0000000000604bce in ExecInterpExpr ()
#12 0x000000000062e87f in ExecResult ()
#13 0x0000000000607f02 in standard_ExecutorRun ()
#14 0x00002b8901f800c5 in pgss_ExecutorRun () from /opt/postgresql/lib/postgresql/pg_stat_statements.so
#15 0x000000000074c34b in PortalRunSelect ()
#16 0x000000000074d690 in PortalRun ()
#17 0x000000000074983c in exec_simple_query ()
#18 0x000000000074aa62 in PostgresMain ()
#19 0x0000000000482164 in ServerLoop ()
#20 0x00000000006dd823 in PostmasterMain ()
#21 0x0000000000482dbf in main ()
[postgres@db-node-03 ~]$ gdb -q -nx -batch -ex 'thread apply all bt 20' -p 25795
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
0x00002b88f8acc9c0 in __accept_nocancel () from /lib64/libpthread.so.0
Thread 1 (Thread 0x2b88f88cc7c0 (LWP 25795)):
#0 0x00002b88f8acc9c0 in __accept_nocancel () from /lib64/libpthread.so.0
#1 0x00002b8e312a7ba1 in dbgcomm_listen_for_proxy () from /opt/postgresql/lib/postgresql/plugin_debugger.so
#2 0x00002b8e312a6aa5 in attach_to_proxy () from /opt/postgresql/lib/postgresql/plugin_debugger.so
#3 0x00002b8e312a50f8 in dbg_newstmt () from /opt/postgresql/lib/postgresql/plugin_debugger.so
#4 0x00002b8e314c6497 in exec_stmt () from /opt/postgresql/lib/postgresql/plpgsql.so
#5 0x00002b8e314c92e4 in exec_stmts () from /opt/postgresql/lib/postgresql/plpgsql.so
#6 0x00002b8e314c98e2 in exec_stmt_block () from /opt/postgresql/lib/postgresql/plpgsql.so
#7 0x00002b8e314c76c6 in exec_stmt () from /opt/postgresql/lib/postgresql/plpgsql.so
#8 0x00002b8e314c8ef6 in plpgsql_exec_function () from /opt/postgresql/lib/postgresql/plpgsql.so
#9 0x00002b8e314bce70 in plpgsql_call_handler () from /opt/postgresql/lib/postgresql/plpgsql.so
#10 0x0000000000602152 in ExecEvalFuncExprFusage ()
#11 0x0000000000604bce in ExecInterpExpr ()
#12 0x000000000062e87f in ExecResult ()
#13 0x0000000000607f02 in standard_ExecutorRun ()
#14 0x00002b8901f800c5 in pgss_ExecutorRun () from /opt/postgresql/lib/postgresql/pg_stat_statements.so
#15 0x000000000074c34b in PortalRunSelect ()
#16 0x000000000074d690 in PortalRun ()
#17 0x000000000074983c in exec_simple_query ()
#18 0x000000000074aa62 in PostgresMain ()
#19 0x0000000000482dbf in ServerLoop ()
[Inferior 1 (process 25795) detached]
[postgres@db-node-03 ~]$ strace -tt -T -s 0 \
> -e trace=accept,accept4,recvfrom,recvmsg,poll,ppoll,select,pselect6 \
> -p 25795
strace: Process 25795 attached
09:28:11.327019 accept(13, ^Cstrace: Process 25795 detached
<detached ...>
[postgres@db-node-03 ~]$
结论
栈明确是 plugin_debugger.so → dbgcomm_listen_for_proxy() → accept(),而锁检查没有发现锁等待。这把问题定位为调试插件等待代理连接;插件源码也显示该处会等待代理接入.
二、测试验证
plugin_debugger.so造成的会话杀不掉问题。业务任意连接到数据库,调试过程,但是没有正确退出会话,导致进程常驻。pg_terminate_backend杀不掉会话。调用pldbg_attach_to_port函数接管并终止会话。
| 函数 | 作用 |
|---|---|
pldbg_attach_to_port(...) |
让一个新的调试代理接管正在等待连接的目标,成功后返回调试会话句柄。在查到的插件实现中,传入值实际用于查找目标的 backend ID;函数名和目录中的 portnumber 参数名容易误导。 |
pldbg_abort_target(句柄) |
通过已建立的调试连接发送“中止目标”指令。参数是上一函数返回的句柄,不是 PID。 |
postgres=# \c xxxdb
SELECT n.nspname, p.proname,
pg_get_function_identity_arguments(p.oid) AS args,
has_function_privilege(p.oid, 'EXECUTE') AS can_execute
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE p.proname IN ('pldbg_attach_to_port', 'pldbg_abort_target')
ORDER BY n.nspname, p.proname;
nspname | proname | args | can_execute
---------+----------------------+--------------------+-------------
public | pldbg_abort_target | session integer | t
public | pldbg_attach_to_port | portnumber integer | t
(2 rows)
1. 环境复现
curl -fL --connect-timeout 5 --max-time 30 \
-o work/pldebugger.tar.gz \
https://codeload.github.com/EnterpriseDB/pldebugger/tar.gz/refs/heads/master
tar -tzf work/pldebugger.tar.gz | head -25
mkdir -p /tmp/pg_pldbg_lab
tar -xzf /tmp/pldebugger.tar.gz -C /tmp/pg_pldbg_lab
chown -R postgres:postgres /tmp/pg_pldbg_lab
su - postgres -c \
'cd /tmp/pg_pldbg_lab/pldebugger-master &&
make USE_PGXS=1 PG_CONFIG=/data/pg/bin/pg_config -j2'
make -C /tmp/pg_pldbg_lab/pldebugger-master \
USE_PGXS=1 PG_CONFIG=/data/pg/bin/pg_config install
编译成功;make install 将插件文件放入 /data/pg 的库和扩展目录。
CREATE EXTENSION pldbgapi;
CREATE FUNCTION public.lab_debug_target()
RETURNS integer
LANGUAGE plpgsql
AS $$
BEGIN
RETURN 42;
END
$$;
2. 复现:终止,PID 仍在
先在临时实例 55432 准备目标连接要执行的脚本:
[root@test-101 ~]# cat > /tmp/pg_pldbg_lab/target.sql <<'SQL'
SELECT pg_backend_pid() AS target_pid;
SELECT public.pldbg_oid_debug('public.lab_debug_target()'::regprocedure::oid) AS armed;
SELECT public.lab_debug_target();
SQL
[root@test-101 ~]# su - postgres -c 'PGAPPNAME=pldbg_lab_target /data/pg/bin/psql -h /tmp/pg_pldbg_lab -p 55432 -d postgres -X -v ON_ERROR_STOP=1 -f /tmp/pg_pldbg_lab/target.sql' > /tmp/pg_pldbg_lab/target.out 2>&1 &
[1] 1990
pldbg_oid_debug() 在这个目标连接中给函数设本地断点。
第三条 SELECT 命中断点后,目标等待调试代理,因此后台运行的 psql 暂时不结束。此时目标输出文件为:
target_pid
------------
2014
(1 row)
armed
-------
0
(1 row)
psql:/tmp/pg_pldbg_lab/target.sql:3: NOTICE: PLDBGBREAK:3
这里 2014 是 PostgreSQL PID,PLDBGBREAK:3 中的 3 是 backend ID。从另一个连接观察到:
SELECT pid, application_name, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE application_name = 'pldbg_lab_target';
pid | application_name | state | wait_event_type | wait_event | query
------+------------------+--------+-----------------+------------+-----------------------------------
2014 | pldbg_lab_target | active | | | SELECT public.lab_debug_target();
(1 row)
接着向 PID 2014 发送终止请求:
SELECT pg_terminate_backend(2014) AS signal_sent;
signal_sent
-------------
t
(1 row)
约两秒后再次查看:
SELECT pid, state, wait_event_type, wait_event,
pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE pid = 2014;
pid | state | wait_event_type | wait_event | blockers
------+--------+-----------------+------------+----------
2014 | active | | | {}
(1 row)
主机进程也仍在,内核等待位置是 accept:
[root@test-101 ~]# ps -o pid,ppid,stat,wchan:32,comm -p 2014
PID PPID STAT WCHAN COMMAND
2014 1954 Ss inet_csk_accept postgres
再次核对 backend ID:
SELECT backendid, pg_stat_get_backend_pid(backendid) AS pid
FROM pg_stat_get_backend_idset() AS backendid
WHERE pg_stat_get_backend_pid(backendid) = 2014;
backendid | pid
-----------+------
3 | 2014
(1 row)
3. 新连接:接管并中止
我新开了一个 psql 连接,并在该连接内连续执行下面两条函数调用:
[postgres@test-101 ~]$ /data/pg/bin/psql -X -h /tmp/pg_pldbg_lab -p 55432 -d postgres
psql (14.7)
Type "help" for help.
postgres=# SELECT public.pldbg_attach_to_port(3);
pldbg_attach_to_port
----------------------
1
(1 row)
postgres=# SELECT public.pldbg_abort_target(1);
pldbg_abort_target
--------------------
t
(1 row)
attach_to_port(3) 使用上一步查到的 backend ID 3,返回调试会话句柄 1。
abort_target(1) 使用的是这个句柄,不是 PID 或 TCP 端口。
立即查询目标:
postgres=# SELECT pid, state FROM pg_stat_activity WHERE pid = 2014;
pid | state
-----+-------
(0 rows)
postgres=# SELECT pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2026-11-13 18:16:17.068834+08
(1 row)
操作系统中 PID 也已消失:
[root@test-101 ~]# ps -o pid,ppid,stat,comm -p 2014
PID PPID STAT COMMAND
此时 target.out 在先前 NOTICE 后新增:
psql:/tmp/pg_pldbg_lab/target.sql:3: ERROR: canceling statement due to user request
CONTEXT: PL/pgSQL function lab_debug_target() line 1 at RETURN
psql:/tmp/pg_pldbg_lab/target.sql:3: FATAL: terminating connection due to administrator command
服务器日志对应的关键记录:
2026-11-13 18:19:21.355 CST [2014] ERROR: canceling statement due to user request
2026-11-13 18:19:21.355 CST [2014] CONTEXT: PL/pgSQL function lab_debug_target() line 1 at RETURN
2026-11-13 18:19:21.356 CST [2014] FATAL: terminating connection due to administrator command
这说明目标离开调试等待点后,先取消了当前语句,随后处理了第 4 步已经发出的终止请求。测试实例的启动时间未变,没有观察到实例重启。
4. 增加实验对照:不预先终止,只调用调试中止
第二轮使用新目标连接。脚本故意在函数后放一条 pg_sleep(60):如果中止调试后连接仍活着,它就会执行到这条 SQL,便于观察。
[root@test-101 ~]# cat > /tmp/pg_pldbg_lab/target2.sql <<'SQL'
SELECT pg_backend_pid() AS target_pid;
SELECT public.pldbg_oid_debug('public.lab_debug_target()'::regprocedure::oid) AS armed;
SELECT public.lab_debug_target();
SELECT pg_sleep(60);
SQL
[root@test-101 ~]# su - postgres -c 'PGAPPNAME=pldbg_lab_control /data/pg/bin/psql -h /tmp/pg_pldbg_lab -p 55432 -d postgres -X -f /tmp/pg_pldbg_lab/target2.sql' > /tmp/pg_pldbg_lab/target2.out 2>&1 &
[1] 2174
这次没有使用 ON_ERROR_STOP=1,让 psql 在被调试的函数报错后继续执行下一条 SQL。目标命中断点后,观察结果为:
SELECT a.pid, b.backendid, a.state, a.query
FROM pg_stat_activity a
JOIN pg_stat_get_backend_idset() AS b(backendid)
ON a.pid = pg_stat_get_backend_pid(b.backendid)
WHERE a.application_name = 'pldbg_lab_control';
pid | backendid | state | query
------+-----------+--------+-----------------------------------
2198 | 3 | active | SELECT public.lab_debug_target();
(1 row)
在另一个新的代理连接内调用,仍然是 backend ID 3,这次返回的句柄也恰好是 1:
postgres=# SELECT public.pldbg_attach_to_port(3);
pldbg_attach_to_port
----------------------
1
(1 row)
postgres=# SELECT public.pldbg_abort_target(1);
pldbg_abort_target
--------------------
t
(1 row)
随即查询同一个目标 PID:
postgres=# SELECT pid, state, query
FROM pg_stat_activity
WHERE pid = 2198;
pid | state | query
------+--------+----------------------
2198 | active | SELECT pg_sleep(60);
(1 row)
因此,pldbg_abort_target() 已取消被调试的函数,但 PID 2198 没有退出。
它继续执行了脚本的下一条语句。这正是第一轮所需的对照。
最后,为清理这个测试连接,我才发送正常终止请求:
postgres=# SELECT pg_terminate_backend(2198) AS signal_sent;
signal_sent
-------------
t
(1 row)
postgres=# SELECT pid FROM pg_stat_activity WHERE pid = 2198;
pid
-----
(0 rows)
第二轮 target2.out 的结尾依次记录函数取消和后来清理 pg_sleep 会话时的连接终止:
psql:/tmp/pg_pldbg_lab/target2.sql:3: NOTICE: PLDBGBREAK:3
psql:/tmp/pg_pldbg_lab/target2.sql:3: ERROR: canceling statement due to user request
CONTEXT: PL/pgSQL function lab_debug_target() line 1 at RETURN
psql:/tmp/pg_pldbg_lab/target2.sql:4: FATAL: terminating connection due to administrator command
实验结论:
pldbg_abort_target负责让目标退出调试等待并取消当前函数;它本身不是“断开连接”。
第一轮能最终断开,是因为目标之前已有一个待处理的pg_terminate_backend请求。
尾结
大佬早在博客上说过这个问题的一般性解决方法:https://www.modb.pro/db/428937。
针对此插件出现的不可杀会话的问题不止一次。遂另辟思路,针对这个插件的问题解决提供了一个新的思路,仅供参考,欢迎交流。
20261120更新
这个方法在实践中遇到局限性。
使用边界:适用场景与调用条件
pldbg_attach_to_port()、pldbg_abort_target() 适用于目标 PostgreSQL 后端已经进入 plugin_debugger 的调试代理等待流程,并且新的调试代理能够成功接管它的场景。pg_terminate_backend() 返回 t 但 PID 仍在,只是启动排查的现象;单凭这个现象、active 状态或空白的 wait_event,不能认定调试器方法可用。测试案例中,栈/等待点与接管成功共同验证了这条路径;实际遇到一种场景的 pstack 证实目标停在 dbgcomm_listen_for_proxy() 的 accept(),但接管检查未通过。
| 调用前要确认的条件 | 模拟 | 生产 |
|---|---|---|
同一实例内目标确实停在 plugin_debugger 等代理,而非只凭 SQL 状态猜测 |
命中本地断点,PLDBGBREAK:3、inet_csk_accept |
pstack 指向 dbgcomm_listen_for_proxy(),ss 显示目标监听 127.0.0.1:46061 |
当前数据库有 pldbg_attach_to_port、pldbg_abort_target,调用者有 EXECUTE 权限 |
实验实例安装 pldbgapi 并成功调用 |
两个函数存在;attach 已实际执行到插件检查,abort 未调用,故其权限未单独核实 |
传给 pldbg_attach_to_port() 的是目标的实际 backend ID,目标 PID 和 backend_start 仍匹配 |
PLDBGBREAK:3;传入 3 |
virtualtransaction = 325/992647;传入 325,仍报错;先前传入的 312 是不可靠编号 |
| 插件共享目标记录仍存在、状态允许接管,且代理连接可以完成 | 接管返回调试会话句柄 1 |
target backend is not listening for a connection;记录缺失、状态不符或插件版本差异尚待核实 |
pldbg_abort_target() 使用本次成功接管返回的句柄,并在同一个代理连接中调用 |
pldbg_abort_target(1) = t |
接管未返回句柄,因此没有调用中止函数 |
调用顺序:先核实 PID、backend_start、目标 backend ID 和插件等待点;再从装有调试器函数且具备执行权限的新数据库连接调用 pldbg_attach_to_port(<目标 backend ID>)。只有它成功返回调试会话句柄,才在同一个连接调用 pldbg_abort_target(<返回句柄>),最后复查原 PID 是否消失。函数名中的 port 和目录形参名 portnumber 容易误导:在本文所核对的插件实现中,接管参数是 backend ID;pldbg_abort_target 参数是调试会话句柄。两者都不是操作系统 PID,也不能把 ss 查到的直接作为参数。生产插件的构建版本未核实,因此仍应以实际安装版本的实现为准。




