Re: procpid?

Since the CF is upon us and discussion is settling, let's see if I can 
wrap this bikeshedding up into a more concrete proposal that someone can 
return to later.  The ideas floating around have gelled into:

-Add a new pg_stat_sessions function that is implemented similarly to 
pg_stat_activity.  For efficiency and simplicity sake, internally this 
will use the same sort of SRF UI that pg_stat_get_activity does inside 
src/backend/utils/adt/pgstatfuncs.c  There will need to be some 
refactoring here to reduce code duplication between that and the new 
function (which will presumably named pg_stat_get_sessions)

-The process ID field here will be named "pid" to match other system 
views, rather than the current "procpid"

-State information such as whether the session is idle, idle in a 
transaction, or has a query visible to this backend will be presented as 
booleans similar to the current waiting field.  A possible additional 
state to expose is the concept of "active", which ends up being derived 
using logic like "visible && !idle && !idle_transaction && !waiting" in 
some monitoring systems.

-A case could be made for making some of these state fields null, 
instead true or false, in situations where the session is not visible.  
If you don't have rights to see the connection activity, setting idle, 
idle_transaction, and active all to null may be the right thing to do.  
More future bikeshedding is likely on this part, once an initial patch 
is ready for testing.  I'd want to get some specific tests against the 
common monitoring goals of tools like check_postgres and the Munin 
plug-in to see which implementation makes more sense for them as input 
on that.

-It is still useful to set current_query to descriptive text in the 
cases where the transaction is <IDLE> etc.  That text is not ambiguous 
with a real query, it is useful for a human-readable view, and it 
improves the potential for pg_stat_sessions to fully replace a 
deprecated pg_stat_activity (instead of just co-existing with it).  That 
the query text is overloaded with this information seems agreed to be a 
good thing; it's just that filtering on the state information there 
should not require parsing it.  The additional booleans will handle 
that.  If idle sessions can be filtered using "WHERE NOT idle", whether 
the current_query for them reads "<IDLE>" or is null won't matter to 
typical monitoring use.  Given no strong preference there, using 
"<IDLE>" is both familiar and more human readable.

I'll go add this as a TODO now.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us