Re: Handy describe_pg_lock function

Hi,

On 2019-11-08 14:49:25 +0800, Craig Ringer wrote:
> 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?

Yea, I think we need to make it easier for users to understand
locking. I kind of wonder whether part of the answer would be to change
the details that pg_locks shows, or add a pg_locks_detailed or such
(presumably a more detailed version would include walking the dependency
graph to at least some degree, and thus more expensive).

I think we probably could include the described lock as an extra column
for pg_locks, as part of a function call in the view targetlist. That
way one would not pay the price when selecting from pg_locks without
including the new columns.

Wonder if it'd be worth introducing a regdatabase type. It'd sure make
views like pg_stat_activity, pg_stat_statements, pg_locks, pg_shdepend
easier to interpret (if we change the views to use regdatabase) / query
(if not, it's just an added cast).

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

I think you'd need to filter for database oid before doing the lock type
identifcation. Object oids are not guaranteed to be unique across
databases. It's somewhat unlikely to hit in test scenarios, but in
longer lived databases it's quite possible (and e.g. more likely if a
lot of toasted values exist, as each new toast value advances the
nextoid counter). Presumably

Greetings,

Andres Freund