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?
