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

Andres Freund <andres@anarazel.de> writes:

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 the actual reason why pg_locks is so bare-bones is that it's
not supposed to require taking any locks of its own internally. If,
for example, we changed the database column so that it requires a lookup
in pg_database, then the view would stop working if someone had an
exclusive lock on pg_database --- pretty much exactly the kind of case
you might wish to be investigating with that view.

I don't have any objection to adding a more user-friendly layer
to use for normal cases, but I'm hesitant to add any gotchas like
that into the basic view.

regards, tom lane

On Sun, 10 Nov 2019 at 13:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andres Freund <andres@anarazel.de> writes:

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 the actual reason why pg_locks is so bare-bones is that it's
not supposed to require taking any locks of its own internally. If,
for example, we changed the database column so that it requires a lookup
in pg_database, then the view would stop working if someone had an
exclusive lock on pg_database --- pretty much exactly the kind of case
you might wish to be investigating with that view.

I don't have any objection to adding a more user-friendly layer
to use for normal cases, but I'm hesitant to add any gotchas like
that into the basic view.

Yeah.

You can always query pg_catalog.pg_lock_status() directly, but that's not
really documented. I'd be fine with adding a secondary view.

That reminds me, I've been meaning to submit a decent "find blocking lock
relationships" view for some time too. It's absurd that people still have
to crib half-broken code from the wiki (
https://wiki.postgresql.org/wiki/Lock_Monitoring) to get a vaguely
comprehensible summary of what's waiting for what. We now
have pg_blocking_pids(), which is fantastic, but it's not AFAIK rolled into
any user-friendly view to help users out so they have to roll their own.

Anyone inclined to object to the addition of an official "pg_lock_details"
view with info like in my example function, and a "pg_lock_waiters" or
"pg_locks_blocked" view with info on blocking/blocked-by relationships? I'd
be inclined to add a C level function to help describe the lock subject of
a pg_locks row, then use that in system_views.sql for the "pg_lock_details"
view. Then build a "pg_lock_waiters" view on top of it
using pg_blocking_pids(). Reasonable?

--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise

On Sun, Nov 10, 2019 at 05:45:08PM +0800, Craig Ringer wrote:

On Sun, 10 Nov 2019 at 13:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andres Freund <andres@anarazel.de> writes:

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 the actual reason why pg_locks is so bare-bones is that it's
not supposed to require taking any locks of its own internally. If,
for example, we changed the database column so that it requires a lookup
in pg_database, then the view would stop working if someone had an
exclusive lock on pg_database --- pretty much exactly the kind of case
you might wish to be investigating with that view.

I don't have any objection to adding a more user-friendly layer
to use for normal cases, but I'm hesitant to add any gotchas like
that into the basic view.

Yeah.

You can always query pg_catalog.pg_lock_status() directly, but that's not
really documented. I'd be fine with adding a secondary view.

That reminds me, I've been meaning to submit a decent "find blocking lock
relationships" view for some time too. It's absurd that people still have
to crib half-broken code from the wiki (
https://wiki.postgresql.org/wiki/Lock_Monitoring) to get a vaguely
comprehensible summary of what's waiting for what. We now
have pg_blocking_pids(), which is fantastic, but it's not AFAIK rolled into
any user-friendly view to help users out so they have to roll their own.

Anyone inclined to object to the addition of an official "pg_lock_details"
view with info like in my example function, and a "pg_lock_waiters" or
"pg_locks_blocked" view with info on blocking/blocked-by relationships? I'd
be inclined to add a C level function to help describe the lock subject of
a pg_locks row, then use that in system_views.sql for the "pg_lock_details"
view. Then build a "pg_lock_waiters" view on top of it
using pg_blocking_pids(). Reasonable?

Very.

+1

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate