构建阻塞树（找出根阻塞者）

命令

WITH RECURSIVE tree AS (
  SELECT a.pid, a.pid AS root_pid, 0 AS level, ARRAY[a.pid] AS path
  FROM pg_stat_activity a
  WHERE cardinality(pg_blocking_pids(a.pid)) = 0
    AND EXISTS (SELECT 1 FROM pg_stat_activity w WHERE a.pid = ANY (pg_blocking_pids(w.pid)))
  UNION ALL
  SELECT w.pid, t.root_pid, t.level + 1, t.path || w.pid
  FROM tree t
  JOIN pg_stat_activity w ON t.pid = ANY (pg_blocking_pids(w.pid))
  WHERE w.pid <> ALL (t.path)
), node AS (
  SELECT DISTINCT ON (pid) pid, root_pid, level
  FROM tree
  ORDER BY pid, level DESC
)
SELECT repeat('  ', n.level) || n.pid AS pid_tree,
       n.root_pid,
       pg_blocking_pids(n.pid) AS blocked_by,
       a.usename,
       a.state,
       a.wait_event_type || ':' || a.wait_event AS wait,
       now() - a.xact_start AS xact_age,
       left(regexp_replace(a.query, '\s+', ' ', 'g'), 80) AS query
FROM node n
JOIN pg_stat_activity a USING (pid)
ORDER BY n.root_pid, n.level, n.pid;

作用

使用 pg_blocking_pids() 递归追踪等待链；从根阻塞者（不等待任何人）开始，以缩进树的形式显示每个等待中的会话、直接阻塞它的 PID 以及其事务时长。

适用场景

当应用出现大量超时或队列堆积时；用于在几十个等待者中找出需要干预的那一个根会话。

注意

根往往是一个被遗忘的、持有锁的 'idle in transaction' 会话；query 列显示的是该会话的最后一条语句，而不是获取锁的那条。等待 ACCESS EXCLUSIVE 的 DDL 也会阻塞排在它后面的简单 SELECT，并显示为中间节点。pg_blocking_pids() 会短暂锁定锁管理器；不要在紧密循环中运行此查询。可通过多个分支到达的会话只在其最深层级显示一次。