Построить дерево блокировок (найти корневой блокирующий процесс)

Команда

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 показывает ПОСЛЕДНИЙ оператор этой сессии, а не тот, что взял блокировку. DDL, ожидающий ACCESS EXCLUSIVE, также блокирует простые SELECT, вставшие в очередь за ним, и отображается как промежуточный узел. pg_blocking_pids() кратковременно блокирует менеджер блокировок; не запускайте этот запрос в плотном цикле. Сессия, достижимая по нескольким ветвям, показывается один раз, на самом глубоком уровне.