Background-job self-heal covers compression jobs only - a dead CAGG refresh or retention job is never detected or re-armed #3816

Summary

The #1581 background-job self-heal covers compression and columnstore jobs only. A policy_refresh_continuous_aggregate or policy_retention job that dies the same way is not detected, not alerted, and not re-armed — and a dead CAGG refresh job is the upstream cause of the retention holds in #3812.

The scope gap

TimescaleSupport.StuckCompressionJobsSql (Darling/PerformanceMonitor.Darling.Storage/TimescaleSupport.cs:5715) ends:

WHERE j.proc_name LIKE '%compression%'
   OR j.proc_name LIKE '%columnstore%'

Everything downstream inherits that filter: the -infinity dead-job detection (TimescaleSupport.cs:5672, "next_start is -infinity — the scheduler will never run it again"), the stuck-Running bound, the EvaluateCompressionJobsAsync self-alert, and the TryRearmJobAsync self-heal wired in at DarlingWorker.cs:5243-5246.

The failure mode #1581 was written for is not compression-specific. next_start = -infinity is how the TimescaleDB scheduler retires any job it has given up on. A CAGG refresh job and a retention job can both reach it.

Why the other two hourly checks do not cover it

Both ride the same connection and cadence, and neither sees a dead job:

• Store Job Over Cadence (Query Store aggregate tax scales serially with fleet size — measure, alert, and evaluate before large onboardings #2136) — JobCadenceReadSql (TimescaleSupport.cs:5794) ends WHERE js.last_run_status = 'Success'. That is correct for what it measures (a failed run's duration is not a cadence signal, as the doc comment says), but it means a job that is failing or has stopped running entirely is filtered out of the reading set, not flagged by it. The check gets quieter as the job gets sicker.
• Retention Held (Retention held by the coverage gate is invisible to store metrics and has no automatic release (use1: 5 query_store_stats policies held 16 days, raw tier at 65 GB / +13 GB per day) #2813) — judges a paused policy whose tier has outgrown its horizon. A retention job that is dead rather than paused reports a plausible state and is not a hold, so it does not qualify.

js.total_failures is recorded into collect.store_metrics by StoreSelfMetrics (StoreSelfMetrics.cs:151) and exposed through get_store_metrics, but nothing alerts on it. It is a series someone has to go looking at.

Why this one matters more than compression

A dead policy_refresh_continuous_aggregate job stops a rollup from materializing. Silently. The store keeps collecting, the raw tier keeps filling, and the first externally visible consequence is the coverage gate refusing to arm that tier's retention policy — at which point the operator is told to run --backfill-rollups, which will fix the symptom and leave the dead job in place to cause it again.

That is the loop worth closing: #3812 makes the release automatic, this makes the cause detectable.

Suggested fix

Widen the existing pass rather than adding a second one. The per-proc reasoning differs, so this is not literally a one-line LIKE change:

• Detection: generalize StuckCompressionJobsSql to read every collect-scoped policy job, carrying proc_name through so the evaluator can band per family. The -infinity NULLIF handling and the stuck-Running bound apply unchanged.
• Self-heal: TryRearmJobAsync is proc-agnostic (alter_job(job_id, next_start => now())), so re-arming a dead refresh or retention job needs no new mechanism. One caveat that must be honored: re-arming a retention job is not equivalent to arming one. The Retention policies run their first check immediately — no external window to pause them, caused permanent data loss #1680/Retention gate is arm-only, so a policy whose coverage list GROWS is never re-held — a store upgrading into a new consumer keeps purging its source #1877 coverage gate deliberately leaves held policies scheduled = false, and a self-heal that "revives dead jobs" must not treat a held policy as a dead one — job_stats.next_start = -infinity and a deliberately paused job are different states, and conflating them would drop history the gate exists to protect. Gate the retention arm on MeasureRetentionCoverageAsync returning Covered, exactly as EnsureRetentionPoliciesAsync does, or restrict the self-heal to refresh jobs only and alert-without-healing on retention.
• Alert: the existing EvaluateCompressionJobsAsync shape extends naturally; per-family text so "the archival tier is stalled" and "a rollup has stopped materializing" do not read the same.

Evidence this is the live cause on at least one store

DARLING01 currently holds four retention policies, all in the query_store_* family, with the daily tiers ~25 hours behind their hourly sources and query_store_stats_daygrain_daily completely empty. See #3812 for the measurements. Whether that is a dead refresh job or simply a never-backfilled cold start is not yet established on that box — but nothing in the product would tell an operator which, and that is the point.