블로킹 트리 구성(root blocker 찾기)

명령어

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()로 대기 체인을 재귀적으로 추적한다. 아무도 기다리지 않는 root blocker부터 시작해 모든 대기 세션을 직접 블로킹하는 PID와 트랜잭션 경과 시간과 함께 들여쓴 트리로 보여 준다.

사용 시점

애플리케이션에 대량 타임아웃이나 대기열 적체가 발생할 때. 수십 개의 대기 세션 중 개입이 필요한 단일 root 세션을 찾기 위해.

주의

root는 대개 락을 잡고 잊힌 'idle in transaction' 세션이다. query 컬럼은 락을 잡은 문장이 아니라 그 세션의 마지막 문장을 보여 준다. ACCESS EXCLUSIVE를 기다리는 DDL은 뒤에 대기하는 단순 SELECT도 블로킹하며 중간 노드로 나타난다. pg_blocking_pids()는 lock manager를 잠시 잠그므로 이 쿼리를 짧은 간격으로 반복 실행하지 않는다. 여러 경로로 도달 가능한 세션은 가장 깊은 수준에서 한 번만 표시된다.