pg_blocking_pids

SELECT
  -- 被阻塞的会话
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  -- 直接阻塞者
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  -- 检查阻塞者是否也被阻塞
  CASE
    WHEN cardinality(pg_blocking_pids(blocking.pid)) > 0
    THEN 'Yes (not root)'
    ELSE 'ROOT BLOCKER'
  END AS is_blocker
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.pid <> blocking.pid;

示例：

 blocked_pid |                  blocked_query                   | blocking_pid |                  blocking_query                  |   is_blocker   
-------------+--------------------------------------------------+--------------+--------------------------------------------------+----------------
         209 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; |          194 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; | ROOT BLOCKER
         220 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; |          209 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; | Yes (not root)
         435 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; |          209 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; | Yes (not root)
         435 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; |          220 | SELECT * FROM demo_lock WHERE id = 1 FOR UPDATE; | Yes (not root)
(4 rows)

ccc=#