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;