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

On Mon, Jul 27, 2015 at 2:32 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> David Rowley wrote:
>> I've not looked into the feasibility of it, but if it were also possible to
>> have a "waiting_for" column which would store the process ID of the process
>> that's holding a lock that this process is waiting on, then it would be
>> possible for some smart guy to write some code which draws beautiful
>> graphs, perhaps in Pg Admin 4 of which processes are blocking other
>> processes. I imagine this as a chart with an icon for each process.
>> Processes waiting on locks being released would have an arrow pointing to
>> their blocking process, if we clicked on that blocking process we could see
>> the query that it's running and various other properties that are existing
>> columns in pg_stat_activity.
>
> 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.

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