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

On Tue, Jan 26, 2016 at 1:40 PM, andres(at)anarazel(dot)de <andres(at)anarazel(dot)de>
wrote:
>
> On 2016-01-26 13:22:09 +0530, Amit Kapila wrote:
> > @@ -633,9 +633,11 @@ postgres 27093 0.0 0.0 30096 2752 ?
Ss 11:34 0:00 postgres: ser
> > <entry>Time when the <structfield>state</> was last
changed</entry>
> > </row>
> > <row>
> > - <entry><structfield>waiting</></entry>
> > - <entry><type>boolean</></entry>
> > - <entry>True if this backend is currently waiting on a lock</entry>
> > + <entry><structfield>wait_event</></entry>
> > + <entry><type>text</></entry>
> > + <entry>Wait event name if backend is currently waiting, otherwise
> > + <literal>process not waiting</>
> > + </entry>
> > </row>
> > <row>
>
> I still think this is a considerable regression in pg_stat_activity
> usability. There are lots of people out there that have queries that
> automatically monitor pg_stat_activity.waiting, and automatically go to
> pg_locks to understand what's going on, if there's one. With the above
> definition, that got much harder. Not only do I have to write
> WHERE wait_event <> 'process not waiting', but then also parse the wait
> event name, to know whether the process is waiting on a heavyweight
> lock, or something else!
>
> I do think there's a considerable benefit in improving the
> instrumentation here, but his strikes me as making live more complex for
> more users than it makes it easier.
>

Here, we have two ways to expose this functionality to user, first is
that we expose this new set of information (wait_type, wait_event)
separately either in new view or in pg_stat_activity and ask users
to migrate to this new information and mark pg_stat_activity.waiting as
deprecated and then remove it in future versions and second is remove
pg_stat_activity.waiting and expose new set of information which will
make users to forcibly move to this new set of information. I think both
the ways have it's pros and cons and they are discussed upthread and
based on that I have decided to move forward with second way.

> At the very least this should be
> split into two fields (type & what we're actually waiting on).
>

makes sense to me, so we can repersent wait_type as:
wait_type text, values can be Lock (or HWLock), LWLock, Network, etc.

Let me know if that is okay or you have something else in mind?

> I also
> strongly suspect we shouldn't use in band signaling ("process not
> waiting"), but rather make the field NULL if we're not waiting on
> anything.
>

Agree, will change in next version of patch.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com