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

Greg Stark <stark(at)mit(dot)edu> writes:
> On Wed, Mar 20, 2013 at 6:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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[]

> I've wanted to use pg_locks as a demonstration for recursive queries
> many times and ran into the same problem. It's just too hard to figure
> out which lock holders would be blocking which other locks.

> I would like to be able to generate the full graph showing indirect
> blocking. This seems to be not quite powerful enough to do it though.
> I would have expected something that took whole pg_lock row values or
> something like that.

I wanted to write the function so it would inspect the lock data
structures directly rather than reconstruct them from pg_locks output;
coercing those back from text to internal form and matching up the lock
identities is a very large part of the inefficiency of the
isolationtester query. Moreover, the pg_locks output fails to capture
lock queue ordering at all, I believe, so the necessary info just isn't
there for determining who's blocking whom in the case of conflicting
ungranted requests.

Now a disadvantage of that approach is that successive calls to the
function won't necessarily see the same state. So if we wanted to break
down the results into direct and indirect blockers, we couldn't do that
with separate functions; we'd have to think of some representation that
captures all the info in a single function's output.

Also, I intentionally proposed that this just return info relevant to a
single process, in hopes that that would make it cheap enough that we
could do the calculations while holding the lock data structure LWLocks.
(Not having written the code yet, I'm not totally sure that will fly.)
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.

regards, tom lane