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);
$$;
--
