Live PostgreSQL Lock Tree (pg_blocking_pids) & Safe Killer

Supanote Core Team @supanote · 2 min read

plain (sql) 1 day ago · 69 lines · 4 views

Artifact

Verified Blueprint PostgreSQL

Recursive PostgreSQL lock tree query using pg_blocking_pids() to pinpoint root blocker transactions holding DDL or row locks.

✓ Builds an indented visual blocking hierarchy from root idle-in-transaction blockers down to queued victims
✓ Surfaces exact lock modes, wait events, state durations, and truncated SQL statements
✓ Generates ready-to-run pg_cancel_backend() and pg_terminate_backend() commands only for root blockers

Quick Fetch

-- 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;

1-- Live PostgreSQL Hierarchical Lock Tree & Root Blocker Inspector (PG 14 - PG 18)

2-- Run during migration hangs or row-lock pileups to identify the exact root cause PID

4WITH RECURSIVE activity AS (

5 SELECT

6 pid,

7 usename,

8 application_name,

9 client_addr,

10 state,

11 wait_event_type || ':' || wait_event AS wait_info,

12 now() - xact_start AS xact_age,

13 now() - query_start AS query_age,

14 pg_blocking_pids(pid) AS blocked_by,

15 left(regexp_replace(query, 's+', ' ', 'g'), 140) AS sql_preview

16 FROM pg_stat_activity

17 WHERE pid <> pg_backend_pid()

18),

19blockers AS (

20 -- Root blockers: processes that block someone else, but are NOT blocked themselves

21 SELECT

22 a.pid,

23 a.usename,

24 a.application_name,

25 a.state,

26 a.wait_info,

27 a.xact_age,

28 a.query_age,

29 0 AS depth,

30 ARRAY[a.pid] AS path,

31 a.sql_preview

32 FROM activity a

33 WHERE cardinality(a.blocked_by) = 0

34 AND EXISTS (

35 SELECT 1 FROM activity w WHERE a.pid = ANY(w.blocked_by)

36 )

38 UNION ALL

40 -- Blocked waiters recursively attached underneath their blocker

41 SELECT

42 w.pid,

43 w.usename,

44 w.application_name,

45 w.state,

46 w.wait_info,

47 w.xact_age,

48 w.query_age,

49 b.depth + 1 AS depth,

50 b.path || w.pid AS path,

51 w.sql_preview

52 FROM activity w

53 JOIN blockers b ON b.pid = ANY(w.blocked_by)

54 WHERE NOT (w.pid = ANY(b.path))

55)

56SELECT

57 repeat(' -> ', depth) || pid::text AS lock_hierarchy_pid,

58 state,

59 coalesce(wait_info, 'CPU / IDLE') AS waiting_on,

60 date_trunc('second', xact_age) AS tx_duration,

61 application_name AS app,

62 sql_preview,

63 CASE

64 WHEN depth = 0 THEN format('SELECT pg_terminate_backend(%s); -- ROOT BLOCKER', pid)

65 ELSE ''

66 END AS safe_remediation_sql

67FROM blockers

68ORDER BY path;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Live PostgreSQL Lock Tree (pg_blocking_pids) & Safe Killer

Replies 0

Related PostgreSQL & Database, Cache & SQL Blueprints

Production PgBouncer Transaction Connection Pooling Config

PostgreSQL Slow Query Logging & auto_explain Configuration

PostgreSQL Logical Replication & CDC WAL Settings

PostgreSQL 18 Asynchronous I/O (io_uring) & NVMe Config