Live PostgreSQL Lock Tree (pg_blocking_pids) & Safe Killer
-- Live PostgreSQL Hierarchical Lock Tree & Root Blocker Inspector (PG 14 - PG 18)
-- Run during migration hangs or row-lock pileups to identify the exact root cause PID
WITH RECURSIVE activity AS (
SELECT
pid,
usename,
application_name,
client_addr,
state,
wait_event_type || ':' || wait_event AS wait_info,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
pg_blocking_pids(pid) AS blocked_by,
left(regexp_replace(query, 's+', ' ', 'g'), 140) AS sql_preview
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
),
blockers AS (
-- Root blockers: processes that block someone else, but are NOT blocked themselves
SELECT
a.pid,
a.usename,
a.application_name,
a.state,
a.wait_info,
a.xact_age,
a.query_age,
0 AS depth,
ARRAY[a.pid] AS path,
a.sql_preview
FROM activity a
WHERE cardinality(a.blocked_by) = 0
AND EXISTS (
SELECT 1 FROM activity w WHERE a.pid = ANY(w.blocked_by)
)
UNION ALL
-- Blocked waiters recursively attached underneath their blocker
SELECT
w.pid,
w.usename,
w.application_name,
w.state,
w.wait_info,
w.xact_age,
w.query_age,
b.depth + 1 AS depth,
b.path || w.pid AS path,
w.sql_preview
FROM activity w
JOIN blockers b ON b.pid = ANY(w.blocked_by)
WHERE NOT (w.pid = ANY(b.path))
)
SELECT
repeat(' -> ', depth) || pid::text AS lock_hierarchy_pid,
state,
coalesce(wait_info, 'CPU / IDLE') AS waiting_on,
date_trunc('second', xact_age) AS tx_duration,
application_name AS app,
sql_preview,
CASE
WHEN depth = 0 THEN format('SELECT pg_terminate_backend(%s); -- ROOT BLOCKER', pid)
ELSE ''
END AS safe_remediation_sql
FROM blockers
ORDER BY path;