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
