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;
Nice sql.
ReplyDelete