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

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.

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