SELECT

    (select datname from pg_database where oid=a.database) AS "Database Name",

    a.locktype AS "Lock Type",

    a.relation::regclass AS "Relation Name",

    (select rsqname from pg_resqueue where oid=a.objid) AS "Resource Queue",

    count(*) AS "Total Waiters"

FROM pg_locks a

WHERE a.granted='f'

GROUP BY 1,2,3,4;



SELECT

    l.locktype AS "Blocker locktype",

    d.datname AS "Database",

    l.relation::regclass  AS "Blocking Table",

    a.usename AS "Blocking user",

    l.pid AS "Blocker pid",

    l.mppsessionid AS "Blockers SessionID",

    l.mode AS "Blockers lockmode",

    now()-a.query_start AS "Blocked duration",

    substring(a.current_query from 1 for 40) AS "Blocker Query"

FROM

    pg_locks l,

    pg_stat_activity a,

    pg_database d

WHERE l.pid=a.procpid

AND l.database=d.oid

AND l.granted = true

AND relation in ( select relation from pg_locks where granted='f')

ORDER BY 3;


SELECT

    l.locktype AS "Waiters locktype",

    d.datname AS "Database",

    l.relation::regclass  AS "Waiting Table",

    a.usename AS "Waiting user",

    l.pid AS "Waiters pid",

    l.mppsessionid AS "Waiters SessionID",

    l.mode AS "Waiters lockmode",

    now()-a.query_start AS "Waiting duration",

    substring(a.query from 1 for 40) AS "Waiters Query"

FROM

    pg_locks l,

    pg_stat_activity a,

    pg_database d

WHERE l.pid=a.pid

AND l.database=d.oid

AND l.granted = 'f'

ORDER BY 3;


Blocker information

Blocker information where LockType = "Transaction ID" is displayed below:


Greenplum 5x:

SELECT

    l.locktype AS "Blocker locktype",

    l.relation::regclass  AS "Blocking Table",

    a.usename AS "Blocking user",

    l.pid AS "Blocker pid",

    l.mppsessionid AS "Blockers SessionID",

    l.mode AS "Blockers lockmode",

    now()-a.query_start AS "Blocked duration",

    substring(a.current_query from 1 for 40) AS "Blocker Query"

FROM

    pg_locks l,

    pg_locks w,

    pg_stat_activity a

WHERE l.pid=a.procpid

AND l.transactionid=w.transactionid

AND l.granted = true

AND w.granted = false

AND l.transactionid is not NULL

ORDER BY 3;