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

On 3/20/13 10:36 PM, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On 20 March 2013 18:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The API that comes to mind is (name subject to
>>> bikeshedding)
>>>
>>> pg_blocking_pids(pid int) returns int[]
>
>> Useful. Can we also have an SRF rather than an array?
>
> I thought about that, but at least for the isolationtester use-case,
> the array result is clearly easier to use.  You can get from one to the
> other with unnest() or array_agg(), so I don't really feel a need to
> provide both.  Can you generate use-cases where the set-result approach
> is superior?

Unless pg_blocking_pids(..) RETURNS SETOF would be significantly faster than unnest(), not directly, BUT...

Anytime I'm looking at locks I almost always want to know not only who's blocking who, but what they're actually
blockingon. Related to that, I also wish we had a way to provide more info about why we're blocked on an XID, since
justpointing your finger at a backend often doesn't do much to tell you what caused the block in the first place.
 

So from that standpoint, I'd prefer that pg_blocking_pids returned enough info to tell me exactly which locks were
blocking.

*thinking*

Actually, is it possible for a backend to have more than one ungranted lock? If not then I suppose that would be good
enoughto tell you which lock had the problem.
 

On the performance side, I've also often wished for a way to pull data from pg_* tables/functions atomically; would it
bereasonable to have a separate function that would copy everything from the proc array into local memory so you could
queryit from there to your hearts content? Bonus if it could also copy all/parts of the statistics file.