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

On Thu, Jun 25, 2015 at 6:46 AM, Andres Freund <andres@anarazel.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