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

On Thu, Jun 25, 2015 at 6:46 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> 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?
>
> 3) sounds best to me. Keeping 'waiting' even makes sense in that case,
> because it'll tell whether wait_event_type is currently being blocked
> on. We can leave the former contents in until the next thing is being
> blocked...

So, that's still redefining the "waiting" column, because it will now
indicate whether we are waiting on some wait event, not whether we are
waiting on specifically a heavyweight lock. But that doesn't bother
me, because I think it's going to be darn confusing if we keep
"waiting" around with the specific meaning of "waiting for a
heavyweight lock" while also now having a notion of "waiting for
something else". I like the idea of indicating both the most recent
wait event and whether or not we are still waiting for it - we refined
current_query to query not too long ago, and I certainly think that
was a significant improvement even if it broke some people's scripts.

I am pretty unconvinced that it's a good idea to try to split up the
wait event into two columns. I'm only proposing ~20 wait states, so
there's something like 5 bits of information here. Spreading that
over two text columns is a lot, and note that Amit's text would
basically recapitulate the contents of the first column in the second
one, which I cannot get excited about.

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