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