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? -- Craig Ringer http://www.2ndQuadrant.com/ 2ndQuadrant - PostgreSQL Solutions for the Enterprise
