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

Robert Haas wrote:
> On Mon, Jul 27, 2015 at 2:32 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)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.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services