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
