Re: visibility of open cursors in pg_stat_activity - Mailing list pgsql-hackers

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