Last Updated: February 25, 2016
·
438
· datasaur

View blocked / blocking processes

SELECT DISTINCT 
    bl.procpid AS blocked_pid,
    bl.usename AS USER,
    bl.current_query AS blocked_query,
    bl.query_start,
    relname AS blocked_on,
    lq.procpid AS blocking_pid,
    lq.usename AS USER,
    lq.current_query AS blocking_query,
    lq.query_start,
    pgl2.mode AS lock_type
FROM pg_stat_activity bl,
    pg_locks pgl1,
    pg_stat_activity lq,
    pg_locks pgl2,
    pg_class
WHERE bl.procpid = pgl1.pid
    AND NOT pgl1.granted
    AND pg_class.oid = pgl1.relation
    AND pgl2.relation = pgl1.relation
    AND pgl2.granted
    AND lq.procpid = pgl2.pid;