Re: RFC: replace pg_stat_activity.waiting with something more descriptive

On 7/27/15 1:46 PM, Robert Haas wrote:
> On Mon, Jul 27, 2015 at 2:43 PM, Alvaro Herrera
> <alvherre@2ndquadrant.com> wrote:
>> Robert Haas wrote:
>>> On Mon, Jul 27, 2015 at 2:32 PM, Alvaro Herrera
>>> <alvherre@2ndquadrant.com> wrote:
>>
>>>> I think this is already possible, is it not?  You just have to look for
>>>> an identically-identified pg_locks entry with granted=true.  That gives
>>>> you a PID and vxid/xid.  You can self-join pg_locks with that, and join
>>>> to pg_stat_activity.
>>>>
>>>> I remember we discussed having a layer of system views on top of
>>>> pg_stat_activity and pg_locks, probably defined recursively, that would
>>>> show the full graph of waiters/lockers.
>>>
>>> It isn't necessarily the case that A is waiting for a unique process
>>> B.  It could well be the case that A wants AccessExclusiveLock and
>>> many processes hold a variety of other lock types.
>>
>> Sure, but I don't think this makes it impossible to figure out who's
>> locking who.  I think the only thing you need other than the data in
>> pg_locks is the conflicts table, which is well documented.
>>
>> Oh, hmm, one thing missing is the ordering of the wait queue for each
>> locked object.  If process A holds RowExclusive on some object, process
>> B wants ShareLock (stalled waiting) and process C wants AccessExclusive
>> (also stalled waiting), who of B and C is woken up first after A
>> releases the lock depends on order of arrival.
>
> Agreed - it would be nice to expose that somehow.

+1. It's very common to want to know who's blocking who, and not at all 
easy to do that today. We should at minimum have a canonical example of 
how to do it, but something built in would be even better.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com