Montar a árvore de bloqueios (encontrar o bloqueador raiz)

Comando

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;

O que faz

Segue recursivamente a cadeia de espera com pg_blocking_pids(); a partir do bloqueador raiz (que não espera ninguém), mostra cada sessão em espera como uma árvore indentada com os PIDs que a bloqueiam diretamente e a idade de sua transação.

Quando usar

Quando a aplicação vê timeouts em massa ou filas se acumulando; para encontrar, entre dezenas de sessões em espera, a única sessão raiz que exige intervenção.

Atenção

A raiz costuma ser uma sessão 'idle in transaction' esquecida que segura o lock; a coluna query mostra o ÚLTIMO comando dessa sessão, não o que obteve o lock. Um DDL esperando ACCESS EXCLUSIVE também bloqueia SELECTs simples enfileirados atrás dele e aparece como nó intermediário. pg_blocking_pids() trava brevemente o lock manager; não execute esta consulta em um loop apertado. Uma sessão alcançável por vários ramos é mostrada uma única vez, em seu nível mais profundo.