Construire l'arbre de blocage (trouver le bloqueur racine)

Commande

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;

Ce que fait la commande

Suit récursivement la chaîne d'attente avec pg_blocking_pids() ; à partir du bloqueur racine (qui n'attend personne), affiche chaque session en attente sous forme d'arbre indenté avec les PID qui la bloquent directement et l'âge de sa transaction.

Quand l'utiliser

Lorsque l'application subit des timeouts massifs ou des files qui s'accumulent ; pour trouver, parmi des dizaines de sessions en attente, l'unique session racine sur laquelle intervenir.

Attention

La racine est souvent une session 'idle in transaction' oubliée qui détient le verrou ; la colonne query affiche la DERNIÈRE instruction de cette session, pas celle qui a pris le verrou. Un DDL attendant ACCESS EXCLUSIVE bloque aussi les simples SELECT placés derrière lui et apparaît comme un nœud intermédiaire. pg_blocking_pids() verrouille brièvement le lock manager ; n'exécutez pas cette requête en boucle serrée. Une session atteignable par plusieurs branches n'est affichée qu'une fois, à son niveau le plus profond.