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

On 23 June 2015 at 05:37, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> When a PostgreSQL system wedges, or when it becomes dreadfully slow
> for some reason, I often find myself relying on tools like strace,
> gdb, or perf to figure out what is happening. This doesn't tend to
> instill customers with confidence; they would like (quite
> understandably) a process that doesn't require installing developer
> tools on their production systems, and doesn't require a developer to
> interpret the results, and perhaps even something that they could
> connect up to PEM or Nagios or whatever alerting system they are
> using.
>
> There are obviously many ways that we might think about improving
> things here, but what I'd like to do is try to put some better
> information in pg_stat_activity, so that when a process is not
> running, users can get some better information about *why* it's not
> running. The basic idea is that pg_stat_activity.waiting would be
> replaced by a new column pg_stat_activity.wait_event, which would
> display the reason why that backend is waiting. This wouldn't be a
> free-form text field, because that would be too expensive to populate.

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.

Obviously this is blue-skies stuff, but if we had a few to provide that
information it would be a great step forward towards that.

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services