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

On Tue, Jun 23, 2015 at 2:33 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Jun 22, 2015 at 4:40 PM, Merlin Moncure <mmoncure(at)gmail(dot)com>
wrote:
> > Instead of changing the column, can't we add a new one? Adjusting
> > columns in PSA requires the innumerable queries written against it to
> > be adjusted along with all the wiki instructions to dev ops for
> > emergency stuck query detection etc etc. I would also prefer to
> > query 'waiting' in some cases, especially when in emergency
> > situations; it's faster to type.
>
> If people feel strongly about backward compatibility, yes, we can do
> that. However, if waiting continues to mean "on a heavyweight lock"
> for backward compatibility, then you could sometimes have waiting =
> false but wait_state non-null. That seems confusing enough to be a
> bad plan, at least to me.
>

That's right if we leave the 'waiting' as it is for the sake of backward
compatibility, then it will be confusing after we add wait_event to
pg_stat_activity and if we change it such that for any kind of wait_event
waiting will be true (or entirely remove waiting), then it will break the
backward compatibility. So we have below alternatives here:

1. Remove/Change 'waiting' in pg_stat_activity and break the backward
compatibility. I think we should try to avoid going via this route.

2. Add 2 new columns to pg_stat_activity
waiting_resource - true for waits other heavy wait locks, false
otherwise
wait_event - description code for the wait event

3. Add new view 'pg_stat_wait_event' with following info:
pid - process id of this backend
waiting - true for any form of wait, false otherwise
wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
wait_event - Lock (Relation), Lock (Relation Extension), etc

Do you think 2nd or 3rd could be viable way to proceed for this feature?

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