The SQL diagnostic

Run this read-only query against the PostgreSQL environment you are investigating. Interpret the results in the context of your workload and privileges.

WITH current_db AS (
    SELECT oid AS db_oid
    FROM pg_database
    WHERE datname = current_database()
)
SELECT
    l.pid,
    a.usename,
    a.state,
    l.locktype,
    l.mode,
    l.granted,
    l.waitstart,
    CASE WHEN c.oid IS NOT NULL
         THEN n.nspname || '.' || c.relname END
         AS relation_name,
    l.transactionid
FROM pg_locks AS l
CROSS JOIN current_db AS d
LEFT JOIN pg_stat_activity AS a ON a.pid = l.pid
LEFT JOIN pg_class AS c
    ON c.oid = l.relation AND l.database = d.db_oid
LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE l.pid IS DISTINCT FROM pg_backend_pid()
  AND (l.database = d.db_oid
       OR l.database IS NULL OR l.database = 0)
ORDER BY l.granted ASC, l.waitstart NULLS LAST, l.pid
LIMIT 200;

Adapted from DBA-019. Uses LEFT JOIN to retain lock rows without a live backend and limits relation-name lookup to the current database.

How to read the results

PostgreSQL tracks relation locks, transaction-ID locks and other lock types. Many row-level locks are not represented as an obvious table-and-row entry in pg_locks. A waiting writer can instead show a wait on a transaction ID.

This is a broad inspection report capped at 200 rows. It is not a complete lock-graph calculation, and it is not a list of which sessions should be terminated.

What to check next

1. Look for ungranted requests. Inspect the lock mode, wait start and session state for rows with granted = false.
2. Watch DDL carefully. Strong relation locks requested by schema changes can create queues behind long transactions.
3. Resolve who blocks whom separately. Use DBA-018 and pg_blocking_pids() for blocker relationships; joining locks against locks is easy to get wrong.
4. Use safe timeouts for planned migrations. Consider a deliberate lock_timeout as part of a change plan, not as an afterthought during an outage.

Related DBA tasks

Continue the investigation with these recipes from SQL Recipes for DBAs: DBA-016 (long-running transactions), DBA-017 (cancel or terminate when justified), and DBA-018 (blocking-session chains).