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(at)2ndQuadrant(dot)com> writes:
>> On 20 March 2013 18:02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 blocking on. Related to that, I also wish we had a way to provide more info about why we're blocked on an XID, since just pointing 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 enough to 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 be reasonable to have a separate function that would copy everything from the proc array into local memory so you could query it from there to your hearts content? Bonus if it could also copy all/parts of the statistics file.