Re: Handy describe_pg_lock function

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

> Andres Freund <andres(at)anarazel(dot)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