Blockierungsbaum aufbauen (Root Blocker finden)

Befehl

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;

Was es tut

Verfolgt die Wartekette rekursiv mit pg_blocking_pids(); ausgehend vom Root Blocker (der auf niemanden wartet) zeigt es jede wartende Session als eingerückten Baum mit den PIDs, die sie direkt blockieren, und ihrem Transaktionsalter.

Wann einsetzen

Wenn die Anwendung massenhaft Timeouts oder sich stauende Warteschlangen sieht; um unter Dutzenden Wartenden die eine Root-Session zu finden, bei der eingegriffen werden muss.

Vorsicht

Der Root ist oft eine vergessene 'idle in transaction'-Session, die die Sperre hält; die Spalte query zeigt die LETZTE Anweisung dieser Session, nicht die, die die Sperre genommen hat. Eine DDL, die auf ACCESS EXCLUSIVE wartet, blockiert auch einfache SELECTs, die sich dahinter einreihen, und erscheint als Zwischenknoten. pg_blocking_pids() sperrt kurz den Lock Manager; führe diese Abfrage nicht in einer engen Schleife aus. Eine über mehrere Zweige erreichbare Session wird einmal angezeigt, auf ihrer tiefsten Ebene.