GP - To gather orphan process lock information

Generating the report of locks if its related to orphan process
SELECT 
 w.relation::regclass AS "Table",
 w.mode               AS "Waiters Mode",
 w.pid                AS "Waiters PID",
 w.mppsessionid       AS "Waiters SessionID",
 b.mode               AS "Blockers Mode",
 b.pid                AS "Blockers PID",
 b.mppsessionid       AS "Blockers SessionID",
 (SELECT 'Hostname: ' || c.hostname ||' Content: '|| c.content || ' Port: ' || port 
 FROM gp_segment_configuration c 
 WHERE c.content=b.gp_segment_id AND role='p') AS "Blocking Segment"
FROM 
 pg_catalog.pg_locks AS w, pg_catalog.pg_locks AS b 
Where ((w."database" = b."database" AND w.relation  = b.relation)
OR w.transactionid = b.transaction)
AND w.granted='f'
AND b.granted='t'
AND w.mppsessionid <> b.mppsessionid
AND w.mppsessionid IN 
(SELECT l.mppsessionid 
 FROM pg_locks l 
 WHERE l.granted = true AND relation IN
(SELECT relation FROM pg_locks WHERE granted='f')
 )
AND w.gp_segment_id = b.gp_segment_id
ORDER BY 1;

Comments

Post a Comment

Popular posts from this blog

GP - Kerberos errors and resolutions

How to set Optimizer at database level in greenplum

GP - SQL Joins