Nothing surfaces idle-in-transaction sessions, which is what pins the xmin horizon we already collect #2540

Activity

1. erikdarlingdata

   added

   enhancementNew feature or requestNew feature or request

   on Aug 22, 2026
2. erikdarlingdata

   added 3 commits that reference this issue on Aug 23, 2026

   Read and expose the session states, with the causal claim gated on th…

   ...

   dc717a3

   Pin the session-states collector and read, including the claim it mus…

   ...

   a582c5f

   Document the session-states surface, and correct four stale counts #2560

   ...

   4098a8e
3. erikdarlingdata commented on Aug 23, 2026

   

   erikdarlingdata

   on Aug 23, 2026

   OwnerAuthor

   More actions

   Building this in #2563. Two corrections to the framing above, both measured rather than reasoned, plus an answer to the note at the bottom.

   idle in transaction does not imply the horizon is pinned

   The issue reads as though idle in transaction and "holds the horizon" are the same condition. They are not, and the gap is large enough that a read built on the issue as written would be wrong about half the sessions it flagged.

   Four idle-in-transaction shapes created on a live PostgreSQL 16.15 target:

   what the session did	backend_xmin	backend_xid	pins the horizon
   BEGIN; SELECT …; (READ COMMITTED)	NULL	NULL	no
   BEGIN; UPDATE …; matching 0 rows	NULL	NULL	no
   BEGIN; UPDATE …; matching a row	NULL	set	yes, via its xid
   BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT …;	set	NULL	yes, via its snapshot

   All four sat idle in transaction indefinitely with identical state, state_change and xact_start. Two of them pinned nothing whatsoever — under READ COMMITTED the snapshot is released at the end of each statement, and a write that changed no rows never gets a transaction id to hold.

   Two consequences for the design:

   1. backend_xmin alone is not the causal link the issue names it as — it is NULL for the writing transaction, which is the single most-cited real-world case. The link is the greater of age(backend_xmin) and age(backend_xid). (PgXminHorizonCollector already does this and its inline comment says why; the issue text predates that.)
   2. The stored column has to be able to say "pinned nothing" as distinct from "pinned something small". It is -1, not 0 — 0 reads as holds the newest possible xid, which is the opposite finding, and it is the one that gets somebody to kill a harmless session.

   So the third bullet under "What it should answer" — long-running transactions generally, because a slow transaction pins the horizon just as effectively as an idle one — is the correct framing and the first bullet is the loose one. What pins is the xid or the snapshot. The state string is a symptom that correlates.

   pg_stat_activity has a pg_monitor trap, and it is quieter than #2542's

   Worth recording because it does not look like the pg_stats one. Without pg_monitor, PostgreSQL does not refuse the read. It returns every row, with state, state_change, xact_start, backend_start and query_id all NULL and the query text replaced by an insufficient-privilege literal — while leaving backend_xmin and backend_xid visible.

   That combination is the bad one: the horizon still reads as pinned and every column that could explain it is gone. On one capture over the same nine backends, a pg_monitor role saw four sessions idle in transaction and a role without it saw zero, because is_idle_in_transaction is derived from a state that came back NULL.

   It is flagged per row off the privilege literal rather than off state IS NULL, because background workers legitimately report a NULL state under full privilege — checkpointer, walwriter and the autovacuum launcher all do.

   Answering the note on query text

   pg_stat_activity shows query text, which is subject to the same handling as any other captured statement — worth checking what the existing pg collectors already do with query before adding another copy of that decision.

   Checked, and the answer came out different from pg_blocking rather than the same. That collector stores blocked_query / blocking_query as returned, and that is right for it: blocking is an exceptional condition, the rows are rare, and the text is the finding. This collector fills on a duration floor an ordinary application crosses, so the identical column would mean routinely accumulating literal parameter values in the store to answer a question that does not need them. (Confirmed on the rig — a probe session's row came back carrying its literal argument verbatim.)

   So no raw query text is stored. Instead:

   • query_id — PostgreSQL's own normalised fingerprint, which joins to pg_statement_stats, whose text is already $1-normalised. Full actionability, zero literals. Nullable for three separate reasons the read keeps apart: PG13 has no such column, 14+ reports NULL when compute_query_id is off, and a redacted row reports NULL with everything else privileged.
   • command_tag — the leading SQL keyword matched against a closed whitelist. A whitelist and not a substring, because ORMs prepend comment blocks, so the first token of a real statement can be a comment opener followed by whatever the application put in it.

   Version floors

   pg_attribute read for the view on a live PostgreSQL 13.23 instance (13 is the documented floor): 21 columns, including leader_pid, backend_type, backend_xid and backend_xmin. query_id is the only column this needs that 13 lacks, so it is version-substituted with a typed NULL rather than gating the collector off, and the PG13 form was executed against that instance.

   Everything else in the issue stands, including the read of #2530's chain — the collector runs on standbys deliberately, since a standby holds its own transactions and with hot_standby_feedback on its xmin propagates to the primary, which is one of the four causes pg_xmin_horizon already attributes.
4. erikdarlingdata

   added 5 commits that reference this issue on Aug 23, 2026

   Say what total_sessions counts, and why it is not filtered to client …

   ...

   de924a1

   Put the sessions panel on the Vacuum tab of both UIs, first in the ca…

   ...

   ef3ecbc

   Measure the redacted column set instead of enumerating it, and make a…

   ...

   a89aebd

   Fix the red build, a cross-surface band disagreement, and a stored co…

   ...

   24ebd39

   Merge pull request #2563 from erikdarlingdata/feature/2540-pg-session…

   ...

   6083b62
5. erikdarlingdata commented on Aug 23, 2026

   

   erikdarlingdata

   on Aug 23, 2026

   OwnerAuthor

   More actions

   Shipped in #2563 (merged to dev): pg_session_states, the 12th PostgreSQL collector, at schema v86, plus get_pg_session_states and a panel placed first on the Vacuum tab of both UIs — that tab calls itself a causal chain, and this is the link upstream of what used to be panel 1.

   This issue's central premise was wrong

   I wrote that backend_xmin is the causal link between an idle-in-transaction session and the pinned horizon. Measured against a live target, two of four idle-in-transaction shapes pin nothing at all:

   • a READ COMMITTED transaction that only read — its snapshot is released at statement end;
   • one whose UPDATE matched zero rows — no xid was ever assigned.

   Both report backend_xmin and backend_xid NULL. So backend_xmin alone is NULL for the writing case, which is the one everybody cites. The real link is GREATEST(age(xmin), age(xid)), stored as horizon_age, and it is -1 rather than 0 when nothing is held — the tool refuses the vacuum argument on those rows explicitly.

   The corollary matters as much: duration in idle in transaction is not by itself evidence of anything.

   The permissions trap, measured

   Without pg_monitor, PostgreSQL does not refuse the read — it returns the rows with every privileged column NULL and substitutes the literal <insufficient privilege> for the query text. Same capture, nine backends: a privileged role saw 4 idle-in-transaction sessions, an unprivileged one saw 0. The horizon still reads as pinned and nothing explains why. Detection keys on that literal rather than on state IS NULL, because a NULL state is also the honest answer for checkpointer, walwriter and the autovacuum launcher under full privilege.

   Also measured column by column: six columns survive redaction (pid, application_name, datname, usename, backend_xid, backend_xmin), and leader_pid and wait_event* are redacted — which nothing in the codebase had previously stated.

   No raw query text is stored, deliberately

   pg_stat_activity.query carries literal parameter values inline. This collector fires on a duration floor an ordinary application crosses, so storing it would mean routinely accumulating user data to answer a question that does not need it — a departure from PgBlockingCollector, which fires on an exceptional condition where the text is the finding. The command keyword is a closed whitelist, not a substring: many ORMs prepend a comment block, so a leading-N-characters rule would carry literals straight out of a WHERE clause. Statement identity is query_id's job.

   Two defects only running it live could find

   1. A SQL comment containing a literal comment opener. PostgreSQL block comments nest, so the whole collection failed to parse — every cycle. No text assertion could see it; a nesting-depth test now pins it.
   2. A three-way band disagreement, from review. A row that was a passing horizon holder and a long idle transaction got severity: "Warning" from the MCP, a finding string saying "not a finding", and no viewer highlight at all — three answers in one JSON object. The suggested fix (gate the Warning branch on PeakHorizonAge < 0) was the dangerous direction: is_horizon_holder means oldest on the instance, not holds, so on a busy instance several genuinely long idle transactions take turns being oldest, none clears the sustained threshold, and that gate would have painted every one of them Healthy. The band was kept and the finding text and viewer flag changed to agree with it.

   A third, backend_duration_ms, was stored and never read — fixed as a category rather than an instance, via EveryStoredColumn_IsReadBackBySomeRead, which derives both sides from the shipped artifacts so it cannot pass while they drift. The column now surfaces as backend age, which earns its place: a connection created ten minutes ago that has been idle in transaction for all ten is a pool bug, while a three-day-old worker holding one for ten minutes is a forgotten commit.

   Version floor verified against a live PostgreSQL 13.23 as well as 16: query_id arrived in 14, so below that it is substituted rather than named — naming it is a parse error, not a NULL.

   Not verified: the rendered appearance of either UI, and no live Aurora run.
6. erikdarlingdata

   closed this as completedon Aug 23, 2026
7. MisterZeus

   added a commit that references this issue on Aug 24, 2026

   Collect the session states behind a pinned xmin horizon (erikdarlingd…

   ...

   eac89f9