ブロッキングツリーを構築(根本のブロッカーを特定)

コマンド

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()はロックマネージャを短時間ロックするため、このクエリを短い間隔のループで実行しない。複数の分岐から到達できるセッションは、最も深いレベルで1回表示される。