Locked and locking queries in postgresql

Locked and locking queries in postgresql.md

CREATE VIEW blocking_processes AS
SELECT 
    pg_locks2.pid as blocking_pid,
    ka.usename as blocking_user,
    ka.query as blocking_query,
    pg_locks1.pid as blocked_pid,
    psa.usename as blocked_user, 
    psa.query as blocked_query, 
    to_char(age(now(), psa.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks pg_locks1
    JOIN pg_catalog.pg_stat_activity psa 
        ON pg_locks1.pid = psa.pid
    JOIN pg_catalog.pg_locks pg_locks2 
        ON pg_locks1.locktype = pg_locks2.locktype
        and pg_locks1.database is not distinct from pg_locks2.database
        and pg_locks1.relation is not distinct from pg_locks2.relation
        and pg_locks1.page is not distinct from pg_locks2.page
        and pg_locks1.tuple is not distinct from pg_locks2.tuple
        and pg_locks1.virtualxid is not distinct from pg_locks2.virtualxid
        and pg_locks1.transactionid is not distinct from pg_locks2.transactionid
        and pg_locks1.classid is not distinct from pg_locks2.classid
        and pg_locks1.objid is not distinct from pg_locks2.objid
        and pg_locks1.objsubid is not distinct from pg_locks2.objsubid
        and pg_locks1.pid <> pg_locks2.pid 
    JOIN pg_catalog.pg_stat_activity ka 
        ON pg_locks2.pid = ka.pid
WHERE pg_locks2.granted and not pg_locks1.granted
ORDER BY psa.query_start;

[Based on] (http://ghostwritten-insomnia.blogspot.de/2013/04/show-blocking-postgres-processes-and.html) which in turn is [based on] (http://www.postgresql.org/message-id/17082AAFC33A934082836458CB53494311F900@MONEXCH01.na.lzb.hq)