Hi all I recently found the need to pretty-print the contents of pg_locks. So here's a little helper to do it, for anyone else who happens to have that need. pg_identify_object is far from adequate for the purpose. Reckon I should turn it into C and submit?
CREATE FUNCTION describe_pg_lock(IN l pg_locks, OUT lock_objtype text, OUT lock_objschema text, OUT lock_objname text, OUT lock_objidentity text, OUT lock_objdescription text) LANGUAGE sql VOLATILE RETURNS NULL ON NULL INPUT AS $$ SELECT *, CASE WHEN l.locktype IN ('relation', 'extend') THEN 'relation ' || lo.lock_objidentity WHEN l.locktype = 'page' THEN 'relation ' || lo.lock_objidentity || ' page ' || l.page WHEN l.locktype = 'tuple' THEN 'relation ' || lo.lock_objidentity || ' page ' || l.page || ' tuple ' || l.tuple WHEN l.locktype = 'transactionid' THEN 'transactionid ' || l.transactionid WHEN l.locktype = 'virtualxid' THEN 'virtualxid ' || l.virtualxid WHEN l.locktype = 'speculative token' THEN 'speculative token' WHEN lock_objidentity IS NOT NULL THEN l.locktype || ' ' || lo.lock_objidentity ELSE l.locktype END FROM ( SELECT * FROM pg_identify_object('pg_class'::regclass, l.relation, 0) WHERE l.locktype IN ('relation', 'extend', 'page', 'tuple') UNION ALL SELECT * FROM pg_identify_object(l.classid, l.objid, l.objsubid) WHERE l.locktype NOT IN ('relation', 'extend', 'page', 'tuple') ) AS lo(lock_objtype, lock_objschema, lock_objname, lock_objidentity); $$; -- Craig Ringer http://www.2ndQuadrant.com/ 2ndQuadrant - PostgreSQL Solutions for the Enterprise