PostgreSQL Locks

The following script displays PostgreSQL locks

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    locktype,
    mode,
    granted,
    relation::regclass AS relation_name,
    page,
    tuple,
    virtualxid,
    transactionid,
    classid,
    objid,
    objsubid,
    virtualtransaction,
    backend_start,
    query
FROM 
    pg_locks l
JOIN 
    pg_stat_activity a 
ON 
    l.pid = a.pid
ORDER BY 
    relation_name, 
    locktype, 
    mode;

The following lists blocking activity

WITH blocking_info AS (
    SELECT
        blocked_locks.pid AS blocked_pid,
        blocked_activity.usename AS blocked_user,
        blocking_locks.pid AS blocking_pid,
        blocking_activity.usename AS blocking_user,
        blocked_activity.query AS blocked_query,
        blocking_activity.query AS blocking_query,
        blocking_activity.application_name AS blocking_application,
        blocking_activity.client_addr AS blocking_client,
        blocked_activity.application_name AS blocked_application,
        blocked_activity.client_addr AS blocked_client
    FROM
        pg_catalog.pg_locks blocked_locks
    JOIN
        pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN
        pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    JOIN
        pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE
        NOT blocked_locks.granted
)
SELECT
    blocked_pid,
    blocked_user,
    blocking_pid,
    blocking_user,
    blocked_query,
    blocking_query,
    blocked_application,
    blocked_client,
    blocking_application,
    blocking_client
FROM
    blocking_info
ORDER BY
    blocked_pid;