Re: Let's invent a function to report lock-wait-blocking PIDs

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> I propose that we should add a backend function that simplifies this
>>> type of query. The API that comes to mind is (name subject to
>>> bikeshedding)
>>>
>>> pg_blocking_pids(pid int) returns int[]

+1

> If we want a global view of the who-blocks-whom situation, I think we'll
> need another approach. But since this way solves isolationtester's
> problem fairly neatly, I was hopeful that it would be useful for other
> apps too.

What about a function

pg_is_lock_exclusive(lock, lock) returns boolean
pg_is_lock_exclusive(lock[], lock[]) returns boolean

I suppose that the lock type would be text ('ExclusiveLock'), but we
could also expose a new ENUM type for that (pg_lock_mode). If we do
that, we can also provide operators such as the following… I did try to
search for some existing ones but failed to do so.

pg_lock_mode & pg_lock_mode
pg_lock_mode | pg_lock_mode

Equiped with that, it should be possible to come up with a recursive
query on pg_locks that displays the whole graph, and we should then
provide as one of our system views.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support