Show blocking locks

blocking.sql

select
    object,
    case "lock requested"
        when 'AccessExclusiveLock' then 'access exclusive'
        when 'ExclusiveLock' then 'exclusive'
        when 'ShareRowExclusiveLock' then 'share row exclusive'
        when 'ShareLock' then 'share'
        when 'ShareUpdateExclusiveLock' then 'share update exclusive'
        when 'RowExclusiveLock' then 'row exclusive'
        when 'RowShareLock' then 'row share'
        when 'AccessShareLock' then 'access share'
        else "lock requested"
    end "lock requested",

    -- only show the most heavy lock type that's been granted
    case
        when array_agg("lock granted") @> array['AccessExclusiveLock'] then 'access exclusive'
        when array_agg("lock granted") @> array['ExclusiveLock'] then 'exclusive'
        when array_agg("lock granted") @> array['ShareRowExclusiveLock'] then 'share row exclusive'
        when array_agg("lock granted") @> array['ShareLock'] then 'share'
        when array_agg("lock granted") @> array['ShareUpdateExclusiveLock'] then 'share update exclusive'
        when array_agg("lock granted") @> array['RowExclusiveLock'] then 'row exclusive'
        when array_agg("lock granted") @> array['RowShareLock'] then 'row share'
        when array_agg("lock granted") @> array['AccessShareLock'] then 'access share'
        else array_agg("lock granted")::text
    end "lock granted",

    "blocked pid",
    "blocked query",
    "running pid",
    "running query",
    "xact age",
    "query age"

from (
    select
        case blocked.locktype
            when 'relation' then 'relation ' || blocked.relation::regclass
            when 'extend' then 'extend ' || blocked.relation::regclass
            when 'transactionid' then 'transaction ' || blocked.transactionid
            when 'advisory' then 'advisory ' || ((blocked.classid::bigint << 32) | blocked.objid::bigint)
            when 'tuple' then 'tuple ' || blocked.relation::regclass || '/' || blocked.page || '/' || blocked.tuple
            else blocked.locktype || ' (todo)'
        end "object",
        blocked.mode "lock requested",
        running.mode "lock granted",
        blocked.pid "blocked pid",
        coalesce(
          blocked_query.query,
          case when pg_is_in_recovery() then '-- replication'
          else '-- SERIALIZABLE transaction'
          end
        ) "blocked query",
        running.pid "running pid",
        coalesce(
          running_query.query,
          case when running.pid is not null then
            case when pg_is_in_recovery() then '-- replication'
            else '-- SERIALIZABLE transaction'
            end
          end
        ) "running query",
        date_trunc('second', now() - running_query.xact_start) "xact age",
        date_trunc('second', now() - running_query.query_start) "query age"

    from pg_locks blocked
    left join pg_locks running
        on running.granted
        and blocked.locktype = running.locktype
        and running.pid != blocked.pid
        and (
            -- match locks depending on type
            (
                blocked.locktype in ('extend', 'relation') and
                (blocked.locktype, blocked.database, blocked.relation) =
                (running.locktype, running.database, running.relation)
            ) or (
                blocked.locktype = 'advisory' and
                (blocked.database, blocked.classid, blocked.objid) =
                (running.database, running.classid, running.objid)
            ) or (
                blocked.locktype = 'transactionid' and blocked.transactionid = running.transactionid
            ) or (
                blocked.locktype = 'tuple' and
                (blocked.database, blocked.relation, blocked.page, blocked.tuple) =
                (running.database, running.relation, running.page, running.tuple)
            ) or (
                -- TODO: unforeseen lock type
                blocked.locktype not in ('extend', 'relation', 'advisory', 'transactionid', 'tuple')
            )
        )
    left join pg_stat_activity blocked_query on blocked_query.pid = blocked.pid
    left join pg_stat_activity running_query on running_query.pid = running.pid
    where not blocked.granted

    -- ignore compatible lock combinations
    and (blocked.mode, running.mode) not in (
        ('AccessShareLock', 'AccessShareLock'),
        ('AccessShareLock', 'RowShareLock'),
        ('AccessShareLock', 'RowExclusiveLock'),
        ('AccessShareLock', 'ShareUpdateExclusiveLock'),
        ('AccessShareLock', 'ShareLock'),
        ('AccessShareLock', 'ShareRowExclusiveLock'),
        ('AccessShareLock', 'ExclusiveLock'),

        ('RowShareLock', 'AccessShareLock'),
        ('RowShareLock', 'RowShareLock'),
        ('RowShareLock', 'RowExclusiveLock'),
        ('RowShareLock', 'ShareUpdateExclusiveLock'),
        ('RowShareLock', 'ShareLock'),
        ('RowShareLock', 'ShareRowExclusiveLock'),

        ('RowExclusiveLock', 'AccessShareLock'),
        ('RowExclusiveLock', 'RowShareLock'),
        ('RowExclusiveLock', 'RowExclusiveLock'),
        ('RowExclusiveLock', 'ShareUpdateExclusiveLock'),

        ('ShareUpdateExclusiveLock', 'AccessShareLock'),
        ('ShareUpdateExclusiveLock', 'RowShareLock'),
        ('ShareUpdateExclusiveLock', 'RowExclusiveLock'),

        ('ShareLock', 'AccessShareLock'),
        ('ShareLock', 'RowShareLock'),
        ('ShareLock', 'ShareLock'),

        ('ShareRowExclusiveLock', 'AccessShareLock'),
        ('ShareRowExclusiveLock', 'RowShareLock'),

        ('ExclusiveLock', 'AccessShareLock')
    )
) x

group by "blocked pid", "running pid", object, "lock requested", "blocked query", "running query", "xact age", "query age"

order by "blocked pid", "running pid";

This snippet is great, thanks a lot! We made a slack bot that reports really slow queries on our prod databases using pg_stat_activity, but we couldn't see what was causing some locks. We just threw this snippet in there now and it helps a ton with that bot's usefulness.