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

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company