On Sun, Nov 10, 2019 at 05:45:08PM +0800, Craig Ringer wrote: > On Sun, 10 Nov 2019 at 13:42, Tom Lane <t...@sss.pgh.pa.us> wrote: > > > Andres Freund <and...@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