有控制地批量终止长时间 'idle in transaction' 的会话

命令

-- 1) Dry run first: who will be affected?
SELECT pid, usename, application_name, client_addr,
       now() - state_change AS idle_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

-- 2) Terminate; wait up to 5 s for each process to exit
SELECT pid, usename, now() - state_change AS idle_for,
       pg_terminate_backend(pid, 5000) AS terminated
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

作用

先列出事务保持打开并空闲超过指定时间的会话，然后终止它们；timeout 参数会一直等到进程实际退出（或超时到期）。

适用场景

用于紧急干预：被遗忘的、持有锁的会话造成阻塞，或阻碍 xmin horizon 使 VACUUM 停滞时。

注意

pg_cancel_backend 对 'idle in transaction' 会话无效（没有正在运行的查询可取消）；需要 pg_terminate_backend，且所有未提交的变更都会被回滚。用 usename/application_name 缩小过滤范围；排除复制、备份和监控会话。连接池会重新打开被终止的连接。如果超时到期，函数会发出警告并返回 false。作为永久解决方法，请按角色设置 idle_in_transaction_session_timeout。