A runtime-precondition miss vocabulary, distinct from the AppliesTo capability axes

[erikdarlingdata]

Split out of #2545 so it is tracked as its own work rather than sitting in a comment. This is the shared foundation every extension-backed collector would need, and it is useful on its own.

The distinction

We have two capability axes, both derived from the collectors' own AppliesTo gates:

• engine edition (On Azure SQL DB, twelve reads tell you to go start a capture that cannot exist there #2511) — Azure SQL Database cannot read system_health, permanently.
• engine kind (Sweep the PostgreSQL AppliesTo gates too, and ship the IL guard first (#2532) #2536) — a SQL Server collector is never sent at a PostgreSQL target, permanently.

Both answer "this collector can never run here", and both are properties of the target, fixed for the life of a connection. That is what makes the sweep-and-derive machinery (#2518) sound: every fact a gate reads is a scalar, and the derivation walks a finite cross-product of them.

A missing extension is a different kind of fact, and it does not belong on that axis:

1. It is mutable at runtime. Engine kind, edition and version cannot change under a live connection. Someone can CREATE EXTENSION pg_stat_kcache while the service is running. A collector gated off at connect time would stay off until a reconnect — silently, and with no way for the operator to tell why installing the extension we recommended changed nothing.
2. It is set-valued. CollectorTargetInfo carries nine facts today, all bool or int, none set-valued. "Which of these N extensions are installed" is a set, which is 2^N combinations if swept naively. (There is a way out — the IL decoder in CollectorEngineCapabilityDerivationTests already knows which facts each gate reads, so only extensions something genuinely gates on need sweeping — but it is still surgery on the sweep model, and point 1 makes it the wrong surgery.)
3. The answer is actionable, and the others are not. "Your engine cannot do this" is final. "pg_stat_kcache is not installed — add it to shared_preload_libraries and it will start collecting" is a setup step. Those deserve different words.

What to build

A third miss shape alongside empty / not_collected / unavailable, evaluated at read time rather than at gate time: the read attempts, finds the surface absent, and answers naming the precondition and how to satisfy it.

Because it is re-evaluated per read, an extension installed five minutes ago is picked up on the next cycle — which is exactly what someone following our own advice expects to happen, and is the property an AppliesTo gate could not give.

Why it is worth having independently of extensions

The same shape covers preconditions we already handle inconsistently:

• Query Store not enabled on a monitored database — a setup step, currently indistinguishable from "no data".
• The system_health XE session stopped on a SQL Server that supports it — genuinely fixable, and today it lands in the same unavailable bucket as engines that can never have it, which On Azure SQL DB, twelve reads tell you to go start a capture that cannot exist there #2511 deliberately separated at the engine level but not at the session level.
• HasMsdbAccess — already an AppliesTo fact, and arguably in the wrong place for the same reason: it describes a grant, which someone can change without a restart.

That last one is worth checking early. If msdb access belongs in this vocabulary rather than on the capability axis, this issue is not new machinery for PostgreSQL — it is the right home for something SQL Server already gets slightly wrong.

Relationship to #2545

#2545 (extension-aware PostgreSQL collection) is the umbrella and a stated stretch goal. This is the piece worth doing first and independently: no sweep changes, no IL-guard changes, and it makes each extension-backed collector a self-contained addition afterwards.

[erikdarlingdata]

Built in #2557. Three corrections to the framing above, one of which changes what this issue is worth.

1. HasMsdbAccess — yes, but the wrongness is not where the issue puts it

Checked first, as the issue asks. HasMsdbAccess comes from HAS_DBACCESS(N'msdb'), probed once in DarlingServerConnector.ConnectAsync, and it genuinely is a GRANT somebody can change without a restart. On both properties this issue uses to separate the axes — mutable, actionable — it belongs in this vocabulary.

But it is not currently reported on the capability axis either, so "arguably in the wrong place" is not quite the defect. TargetsWithEngineEdition deliberately varies it, and AFixableGate_IsNotReportedAsAnEngineGap already asserts that job_history / running_jobs / agent_status are not engine gaps on a box edition. That answer is correct and #2511 got it right on purpose.

The two real defects are:

• Nothing reported it at all. get_running_jobs answered empty — "No running SQL Agent jobs found" — which is an affirmative claim about the server's Agent rather than "we cannot see it". Fixed in A runtime-precondition miss vocabulary, evaluated at read time (#2546) #2557 for the reachable half: when HAS_DBACCESS returns 1 but the job tables deny, the collector runs, is refused, and records the denial with the grant to issue, and the read now surfaces that.
• ServerRuntime is cached for the life of the connection. It is dropped only on a config change, a removal, or a connect/collection failure — so on a healthy server the msdb probe is frozen until the service restarts, and a GRANT genuinely does not take effect. This is the sharper version of the issue's point 1, and it is real.

I did not fix the second half, because the two honest fixes are both a different lane and one of them is a call to make rather than take:

1. Remove HasMsdbAccess from the three AppliesTo gates and let the collectors run and fail into the non-fatal bucket. This fixes it completely, needs no sweep change (dropping a gate read cannot violate "every fact a gate reads is varied"), and lands the answer in the vocabulary A runtime-precondition miss vocabulary, evaluated at read time (#2546) #2557 just built. The cost is the wasted query per collector per cycle on a no-msdb login, which gating it off deliberately removed — and it is a change to collection dispatch, not to a read vocabulary. Worth its own issue.
2. Stamp has_msdb_access onto servers (migration rung + Lite twin + viewer probe). The read could then state it positively — but the value stays connect-frozen, so we would ship advice that does not take. Strictly worse than not shipping it.

So the answer to "does this materially change the value of the issue" is yes: this is not speculative machinery for PostgreSQL, it is the right home for something the SQL Server side already gets slightly wrong. Half of it is closed; the other half is a gate decision.

2. system_health stopped is not detectable from the store today

The issue lists it as a candidate call site. It cannot be wired without a collector change.

SystemHealthEventsCollector reads sys.dm_xe_session_targets JOIN sys.dm_xe_sessions WHERE xes.name = N'system_health'. A stopped session simply is not in dm_xe_sessions, so the join returns zero rows, the collector records SUCCESS, and nothing distinguishes it from a genuinely quiet ring buffer. It also does not go through RunXeTolerantAsync, so it cannot raise SESSION_MISSING — only deadlocks, blocked_process_report and long_query_completions do.

Those three are wired in #2557 instead. Making the system_health case reportable means having the collector record session state, which is a separate change and probably the right follow-up.

3. The mechanism was already there — the reads just never asked

The strongest thing found while building this, and it makes the issue more valuable than it reads:

• DarlingWorker.PostgresFaultOutcome already classifies a missing relation or function into the non-fatal bucket and writes CREATE EXTENSION pg_stat_statements into collection_log.error_message, in so many words.
• The SQL Server permissions catch does the same on both SKUs through SqlServerPermissionErrors.
• query_store_health has recorded actual_state per database, hourly, all along.

And no read reported any of it. Two Query Store reads were guessing in prose — "Query Store may not be enabled on target databases" — which is equally true of a server where it is enabled and the window simply reached past what the raw tier retains.

That means the extension story in #2545 does not need new detection machinery at all. The detection exists; #2557 is the read that reports it. get_pg_top_queries already answers with the stored CREATE EXTENSION today.

Leaving this open — the HasMsdbAccess gate decision above is still on it.

[erikdarlingdata]

Shipped in #2557 (merged to dev): a fourth miss word, precondition, evaluated at read time, wired at six call sites across both SKUs.

The premise held — no capability-sweep or IL-guard change was needed. This asks a different question of the store, not a new question of the gates, and CollectorEngineCapability is read from and never modified. The "after the read, never before" invariant is untouched.

The finding that makes this not speculative machinery: every fact was already collected, already classified, and already carried its own remedy — and no read reported any of it. PostgresFaultOutcome has been writing CREATE EXTENSION pg_stat_statements into collection_log.error_message all along; SESSION_MISSING already existed; query_store_health has recorded actual_state hourly. Meanwhile two Query Store reads were guessing in prose — "Query Store may not be enabled on target databases" is equally true of a server where it IS enabled and the window simply reached past retention. That also means #2545's extension story needs no new detection at all.

Two corrections to this issue, both mine. system_health being stopped is not detectable from the store: the collector joins dm_xe_session_targets to dm_xe_sessions, so a stopped session yields zero rows and logs SUCCESS, never passing through RunXeTolerantAsync. The three captures that do classify it were wired instead. And on HasMsdbAccess — I was right that it is a grant and belongs in this vocabulary, wrong that it is misreported on the capability axis; it already is not. The reachable half is fixed, and the dispatch half is now #2558.

A claude[bot] review caught a real defect on the way: actual_state = 'ERROR' fell through to the OFF wording, so a database whose Query Store had broken was told to turn it on — an ALTER already in effect, which silently no-ops — and in a mixed scope the sentence named the OFF database while dropping the broken one entirely.