Обсуждение: visibility of open cursors in pg_stat_activity

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

Hi,

On 2023-10-26 11:47:32 -0400, Robert Haas wrote:
> 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.)

Does it really matter on that level for the user whether a snapshot exists
because of repeatable read or because of a cursor?  If users don't understand
backend_xmin - likely largely true - then the consequences of holding a
snapshot open because of repeatable read (or even just catalog snapshots!) is
as severe as an open cursor.


> 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.

Given snapshots held for other reasons, I think we should expose them
similarly, if we do something for cursors. Otherwise people might start to
worry only about idle-txn-with-cursors and not the equally harmful
idle-txn-with-snapshot.

Maybe something roughly like
  idle in transaction [with {snapshot|cursor|locks}]
?


> 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.

Portals are very weirdly underdocumented and surprisingly complicated :/


> 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.

I think you can also basically use an unnamed portal as a cursor with the
extended protocol. The only thing is that there can only be one of them.

The interesting distinction likely is whether we have cursors that are not
active.


> 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.

I think it'd be nice to make idle-in-txn a bit more informative. Not sure
though how much that helps most users, it's still quite granular information.

I still would like a view that shows what's holding back the horizon on a
system wide basis. Something like a view with the following columns and one
row for each database

  datname
  horizon
  horizon_cause = {xid, snapshot, prepared_xact, replication_connection, ...}
  xid_horizon
  xid_horizon_pid
  snapshot_horizon
  snapshot_horizon_pid
  prepared_xact_horizon
  prepared_xact_horizon_id
  replication_connection_horizon
  replication_connection_horizon_pid
  physical_slot_horizon
  physical_slot_horizon_pid
  physical_slot_horizon_name
  logical_slot_horizon
  logical_slot_horizon_pid
  logical_slot_horizon_name

Perhaps with one additional row with a NULL datname showing the system wide
horizons (one database could have the oldest xid_horizon and another the
oldest logical_slot_horizon, so it's not a simple order by).


I recently mused in some other thread that I really would like to have an
approximate xid->timestamp mapping, so that we could assign an age to these in
a unit that makes sense to humans.  Particularly snapshots / xmin can be very
confusing in that regard because a relatively recent transaction can hold back
the overall horizon further than the time the transaction started, if some old
transaction was still running at the time.


Perhaps we could add at least timestamps to these in some other
way. E.g. recording a timestamp whenever a transaction is prepared, a slot is
released... Likely recording one whenever a snapshot is acquired would be too
expensive tho - but we could use state_change as an approximation?

Greetings,

Andres Freund

On Thu, Oct 26, 2023 at 1:41 PM Andres Freund <andres@anarazel.de> wrote:
> Does it really matter on that level for the user whether a snapshot exists
> because of repeatable read or because of a cursor?  If users don't understand
> backend_xmin - likely largely true - then the consequences of holding a
> snapshot open because of repeatable read (or even just catalog snapshots!) is
> as severe as an open cursor.

Sure it matters. How is the user supposed to know what they need to go
fix? If there's application code that says BEGIN TRANSACTION
SERIALIZABLE, that's a different thing to look for than if there's
application code that fails to close a cursor somewhere.

> Given snapshots held for other reasons, I think we should expose them
> similarly, if we do something for cursors. Otherwise people might start to
> worry only about idle-txn-with-cursors and not the equally harmful
> idle-txn-with-snapshot.
>
> Maybe something roughly like
>   idle in transaction [with {snapshot|cursor|locks}]
> ?

Well every transaction is going to have a lock on its own VXID, if
nothing else. And in almost all interesting cases, more than that.

The point for me is that if you're using cursors, "idle in
transaction" is misleading in a way that it isn't if you have a
snapshot due to serializability or something. Consider two users. Each
begins a transaction, then each runs a query that returns a large
number of rows, considerably in excess of what will fit in the network
buffer. Each user then reads half of the rows and then goes into the
tank to process the data they have received thus far. User A does this
by sending the query using the simple query protocol and reading the
results one at a time using single-row mode. User B does this by
sending the query using the extended query protocol and fetches the
rows in batches by sending successive Execute messages each with a
non-zero row count. When user A goes into the tank, their session is
shown as active. When user B goes into the tank, their session is
shown as idle-in-transaction. But these situations are actually very
similar to each other. In both cases, execution is suspended because
the client is thinking.

The case of holding a snapshot because of repeatable read or
serializable isolation is, in my view, different. In that case, while
it's true that the session is holding onto resources that might cause
some problems for other things happening on the system, saying that
the session is idle in transaction is still accurate. The problems are
caused by transaction-lifespan resources. But in the case where there
are active cursors, the backend is actually in the middle of executing
a query, or maybe many of them, but at least one. Sure, at the exact
moment that we see the status as "idle in transaction", it isn't
actively trying to run any of them, but that feels like a pedantic
argument. If you put a pot of water on the stove to boil and wait for
it to heat up, are you actively cooking or are you idle? As here, I
think the answer is "something in between."

> I still would like a view that shows what's holding back the horizon on a
> system wide basis. Something like a view with the following columns and one
> row for each database

Seems like it's just the same information we already have in
pg_stat_activity, pg_prepared_xacts, and pg_replslots. Maybe
reformatting is useful but it doesn't seem groundbreaking. It would be
groundbreaking if we could surface information that's not visible now,
like the names and associated queries of cursors in sessions not our
own. But that would be much more expensive to expose.

> I recently mused in some other thread that I really would like to have an
> approximate xid->timestamp mapping, so that we could assign an age to these in
> a unit that makes sense to humans.  Particularly snapshots / xmin can be very
> confusing in that regard because a relatively recent transaction can hold back
> the overall horizon further than the time the transaction started, if some old
> transaction was still running at the time.
>
> Perhaps we could add at least timestamps to these in some other
> way. E.g. recording a timestamp whenever a transaction is prepared, a slot is
> released... Likely recording one whenever a snapshot is acquired would be too
> expensive tho - but we could use state_change as an approximation?

I'm not saying this is a terrible idea or anything, but in my
experience the problem isn't usually that people don't understand that
old XIDs are old -- it's that they don't know where to find the XIDs
in the first place.

--
Robert Haas
EDB: http://www.enterprisedb.com