Hi,
I've seen situations a few times now where somebody has sessions that
are "idle in transaction" for a long time but they feel like it should
be harmless because the transaction has no XID. However, the fact that
the transaction is idle doesn't mean it isn't running a query, because
there could be a cursor from which some but not all results were
fetched. That query is suspended, but still holds a snapshot and thus
still holds back xmin. You can see this from pg_stat_activity because
backend_xmin will be set, but I've found that this is easily missed
and sometimes confusing even when noticed. People don't necessarily
understand how it's possible to have a snapshot if the session is
idle. And even if somebody has great understanding of system
internals, pg_stat_activity doesn't distinguish between a session that
holds a snapshot because (a) the transaction was started with
repeatable read or serializable and it has already executed at least
one command that acquired a snapshot or alternatively (b) the
transaction has opened some cursors which it has not closed. (Is there
a (c)? As far as I know, it has to be one of those two things.)
So I think it would be useful to improve the pg_stat_activity output
in some way. For instance, the output could say "idle in transaction
(with open cursors)" or something like that. Or we could add a whole
new column that specifically gives a count of how many cursors the
session has open, or how many active cursors, or something like that.
I'm not exactly clear on the terminology here. It seems like the thing
we internally called a portal is basically a cursor, except there's
also an unnamed portal that gets used when you run a query without
using a cursor. And I think the cursors that could potentially hold
snapshots are the ones that are labelled PORTAL_READY. I think we
can't have a PORTAL_ACTIVE portal if we're idle, and that
PORTAL_{NEW,DEFINED,DONE,FAILED} portals are not capable of holding
any resources and thus not relevant. But I'm not 100% positive on
that, and I'm not exactly sure what terminology the user facing
reporting should use.
But I think it would be nice to do something, because the current
situation seems like it's more confusing than it needs to be.
Thoughts?
--
Robert Haas
EDB: http://www.enterprisedb.com