Repository navigation
Read PostgreSQL plans: the MCP must answer with a plan, not a pointer to one #2567
Description
Activity
Still blocked on storage, but #2565 is answered and it changes one of the three no-plan cases listed here — worth recording before anyone builds this.
The mechanism is a collector running
EXPLAIN (GENERIC_PLAN, FORMAT JSON)over statements already inpg_stat_statements, becauseauto_explainwrites to the server log and a SQL connection cannot read it (measurements on #2565).Consequences for this read:
The middle case does not exist as written. "Capture is configured but this query never crossed the threshold" is an
auto_explainconcept — a duration threshold the server applies. We capture on OUR schedule, for statements WE select frompg_stat_statements. So the honest middle case becomes "this statement is in the store but we have not explained it yet", which is a different sentence and a different fix (widen what the collector explains, or wait a cycle). Reusing the threshold wording would describe a mechanism we do not use.A fourth case appears, and it is the one most likely to bite.
EXPLAINcan fail per-statement where collection is otherwise healthy: the statement references an object the monitoring login cannot see, or is a utility statementEXPLAINrefuses, orGENERIC_PLANrejects it because the parameters are not in a position the planner can generalise. That is per-statement, not per-server, so it cannot be reported as a server-level precondition — it belongs on the row.And the plan we return is not the plan that ran.
GENERIC_PLANis what the planner would choose for an unknown parameter. For a parameter-sensitive workload it can differ materially from the executed plan. The read must say so in its own words — an agent with no viewer will otherwise treat it as observed truth, which is exactly the "guessing in prose" failure this issue warns against, arriving from the opposite direction.What is unchanged and confirmed:
queryidserializes as a string (#2548/#2553); the spine ispg_statement_stats'queryid; and per #2547 an Aurora-gated read is SHOWN on stock PostgreSQL with thenot_collectedsentence rather than hidden.Retention wording is now precise: #2566's recommendation puts PostgreSQL plans in their own dimension registered under the existing
plan_content_retention_days, so "the plan existed and was aged out" is a real, nameable state with a knob to cite — not a guess.Half-unblocked: #2565 is closed (mechanism is
auto_explain, threshold-driven). #2566 is still open but now has a recommendation on it. Two things from the measurement work change this issue directly.The "no plan" list is missing the state that will dominate
This issue names three honest answers and is right that they must not collapse. There are five, and the two missing ones are the ones a first implementation will get wrong.
4. The plan was captured and cannot be attributed.
auto_explainputs no query identifier in the plan — measured on PostgreSQL 17, even withcompute_query_id=on. The id exists only in the log line prefix, and only whenlog_line_prefixcarries%Q, where it matchespg_stat_statements.queryidexactly. Without it, plans are being written and none of them can be joined to a queryid — so a read keyed on queryid finds nothing while the server is doing everything asked of it.This is worse than the other four states because every other signal says capture is working. It is now a
plan_attributionfacet onpg_plan_capture_readiness(#2584, merged), so this read can distinguish it and give the specific remedy, which is alog_line_prefixedit and needs no restart.5. The plan aged out of the SERVER log before we collected it, which is distinct from our own retention. On this Aurora fleet
rds.log_retention_periodis 4320 minutes (3 days), and plans come back through the RDS log API. A plan older than that is gone at the source and noplan_content_retention_dayssetting of ours affects it. Two different retention boundaries, two different sentences — collapsing them would tell someone to raise a knob that cannot help.So the vocabulary this read needs is: not-configured (
precondition), configured-but-unattributable (precondition, different remedy), never-crossed-the-threshold (genuinely fine), aged-out-of-the-server-log, aged-out-of-our-store. All five are now measurable rather than guessable, which is the standard #2557 set.The queryid-as-string point is more load-bearing here than elsewhere
Confirmed in the raw data:
pg_stat_statements.queryidfor a trivial query came back as-4828029293864693941. That is 19 digits and negative — comfortably past 2^53, soJSON.parsemangles it. This read's entire spine is that value, so the #2548/#2553 string-on-the-wire rule is not a nicety here; get it wrong and the join silently returns the wrong plan rather than no plan.One shape consequence from #2566
My recommendation there is to store the plan and the queryid and discard
Query Text— becauseauto_explainlogs literals verbatim, and that is both a data-handling exposure and the reason plan dedup cannot work on the raw blob.If that is accepted, this read cannot echo the captured statement text back and must resolve it from
pg_stat_statementsvia the queryid. That is better anyway — the text from there is normalised and literal-free — but it makes the join mandatory rather than convenient, so the read cannot be built to work without it.Still blocked on #2566 for storage shape.
- added 4 commits that reference this issue
on Aug 25, 2026 Shipped in #2614.
get_pg_plansreturns the plan as navigable JSON — not an id, not an opaque string. #2538's constraint is met: an agent has no viewer to follow a reference into.Every requirement this issue listed:
- queryid as a string on the wire (get_pg_top_queries loses queryid precision: an int8 identity serialized as a JSON number #2548/get_pg_top_queries: make it parse, and stop rounding the PostgreSQL int8 identities (#2554, #2548) #2553), and accepted as one too. Passing it in as a number would make the rounding silent, so a value that will not parse exactly is rejected with the reason.
- Joins the query side —
query_idagainstget_pg_top_queries, which is where the normalized text and the call counts live. - A home in both UIs — the web Activity tab, directly under Top Query Shapes.
- The three empty states kept apart, using stored facts rather than prose that guesses between them: capture not configured (naming the specific unsatisfied facet from
pg_plan_capture_readinesswith its remedy), configured but never crossed the threshold (the healthy answer), or aged out underplan_content_retention_days.
One thing this issue anticipated that turned out differently: it expected the read might be Aurora-only. It is the reverse. The mechanism is
auto_explain, which writes to the server log, and reading that log needspg_read_server_filesplus an explicitGRANT EXECUTE ON FUNCTION pg_read_file— impossible on Aurora and RDS, which have no filesystem. So this is a self-hosted capability that is dark on Aurora, and the capability phrase names the log rather than the engine.Nothing here re-derives redaction: plans are stripped at collection (#2566), so there is no un-redacted copy in the store for a read to leak.
Child of #2538, blocked on #2566 (storage) and #2565 (mechanism).
The reads that make captured plans useful, once there are any.
What it has to do
PgStatementStatsCollectorcollectspg_stat_statements/aurora_stat_statements()keyed byqueryid, so the read has a spine already — plan for a queryid, not plan floating on its own.queryidis a string on the wire. Per get_pg_top_queries loses queryid precision: an int8 identity serialized as a JSON number #2548/get_pg_top_queries: make it parse, and stop rounding the PostgreSQL int8 identities (#2554, #2548) #2553,int8queryids exceed 2^53 andJSON.parsesilently rounds them. Any new read must serialize it as a string from day one rather than repeating that.CollectorCatalograther than enumerating — hand-maintained read lists have decayed three times.Behaviour when there is no plan
This is most of the work, and the machinery now exists. The honest answers are all different and must not collapse into one:
auto_explainabsent, or loaded withlog_min_duration = -1) — aprecondition(A runtime-precondition miss vocabulary, distinct from the AppliesTo capability axes #2546/A runtime-precondition miss vocabulary, evaluated at read time (#2546) #2557), fixable, with the specific remedy.plan_content_retention_daysgoverns this and the read should say so rather than implying none was captured.Two Query Store reads were previously guessing in prose between exactly this kind of ambiguity ("Query Store may not be enabled on target databases"), which #2557 replaced with measured facts. Do not reintroduce the pattern here.
Aurora-only, probably
If the mechanism chosen in #2565 depends on
pg_stat_statements, this read inherits itsAppliesTogate — and per #2547 the Aurora-only reads are shown on stock PostgreSQL with thenot_collectedsentence, not hidden. Follow that.