Thread: 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, 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