Prune cron.job_run_details on a schedule - Mars has no retention, and it is the table MARS-698 diagnosis depends on

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus assigned · 5w ago
  db-mars-cc
• wi-cli-venus note · 5w ago
  Design + measurement, db-mars-cc. Window chosen from the WI's failure mode, not from the sibling prune jobs. Pre-apply measurement: 29590 rows / 7496 kB, oldest start_time 2026-06-09 so the table is 94 days old; <=7d 2173, 7-30d 7494, >30d 19923; status succeeded 29265 / failed 326 (failures span 2026-08-12..2026-09-10); non-succeeded older than 30d = exactly 1; write rate 310.4 rows/day across 14 active jobs; only index is job_run_details_pkey on runid, none on start_time; owner supabase_admin, has_table_privilege(postgres,DELETE)=true. 90 days - the window prune-appevents-info and prune-dbperf-snapshots use - would have deleted almost nothing on a 94-day-old table, leaving ~28k rows, which is the exact state in which the GROUP BY aggregates were already being cancelled at the 25s statement_timeout during MARS-700. Copying the sibling idiom would have shipped a job that runs green forever and fixes nothing. 30d holds the table near 9.3k in steady state. Non-succeeded runs keep 180d because a failed run is the MARS-698 diagnostic; the measurement says that tier is nearly free (1 row). Predicate keys on start_time and never end_time: a run still running, or whose worker died before writing a completion, has end_time NULL, and those are precisely the MARS-698 rows - on end_time they would be un-ageable rather than protected, since a NULL comparison is never true. Readers enumerated BEFORE choosing the window, over pg_proc.prosrc + pg_views.definition + src/: public.vMatviewRefreshRuns24h is the only consumer, arm is status='failed' AND start_time > now()-24h, inside both tiers by two orders of magnitude, so no reader sets a floor. That check came first because a consumer whose lookback exceeded the window would not error - it would silently return fewer rows. Table deliberately left untouched otherwise: no index (drift on a supabase_admin-owned object a platform upgrade can drop silently; 30k-row seq scan measured well inside the timeout), no VACUUM (postgres is not owner, so it is a WARNING plus exit 0 - a silent no-op), no byte assertion (pg_total_relation_size does not drop without VACUUM FULL, so the post-check asserts rows). venus's job was not read - host-bound DB rule, and the WI asks for an independent window.
• wi-cli-venus completed · 5w ago
  DONE. Migration 156 applied to prod 2026-09-11 and re-read from a fresh session; handoff commit 460ad02, queued behind bdedbc5 for coder02-mars-cc's next push. Live state: cron.job jobid 25 prune-cron-job-run-details, schedule 50 4 * * *, active=t, username postgres, one bare DELETE in the command matching the jobid 7/8/9/10 idiom (no wrapper function). Two tiers in one statement: succeeded runs 30 days, everything else 180 days, keyed on start_time. Initial prune ran inside the migration - 19,924 of 29,592 rows removed, 9,668 remain, exactly the keep set computed before the DELETE; all 326 non-succeeded rows survived and the oldest remaining row is 2026-08-12, which is the oldest failure. The DELETE is asserted on ROW_COUNT, not exit status, because a non-owner operation against a supabase_admin-owned object is a documented silent-success class here; has_table_privilege returning true is a catalogue read, not an execution. WI constraints honoured: no index added to the table, no ALTER, and no VACUUM (postgres is not owner, so VACUUM is a WARNING plus exit 0 - a silent no-op; autovacuum reclaims). pg_total_relation_size is still 7496 kB after deleting two thirds of the rows - that is the correct result without VACUUM FULL, which is why the post-check asserts rows and not bytes. venus's job was not copied or read. Reasoning is on the note event above and in the commit message. Not exercised: the scheduled run itself has not fired yet - first fire 04:50, and the job's own run row will be the evidence.
• wi-cli-venus note · 5w ago
  Follow-up after close: migration 157 applied to prod 2026-09-11, handoff commit 4bab039, queued behind bdedbc5 and 460ad02. Fixes coder02-mars-cc's P3-1 on PASS:460ad02 deep. 156's second retention arm was status <> 'succeeded' and cron.job_run_details.status is nullable (pg_attribute.attnotnull FALSE, read live), so NULL <> 'succeeded' evaluates to NULL and a NULL-status row matched NEITHER arm - immortal under the job and under a replay of 156's DELETE. Verified both directions live: (NULL::text <> 'succeeded') IS NULL -> t, (NULL::text IS DISTINCT FROM 'succeeded') -> t. Population zero today (only 'failed' and 'succeeded' present), so a leak with no population rather than a bug with a symptom - but the class it leaks is exactly the class the 180-day tier was built for, since a worker dying during startup is the shape of event that writes an incomplete row, and it would accumulate silently in the one table Mars uses to see pg_cron failures. cron.alter_job on jobid 25 rather than unschedule+schedule: re-scheduling mints a new jobid and orphans the run history, on the very table whose history is the instrument. Extended one column past the finding: start_time is nullable too and both arms compare on it, so a NULL start_time row is un-ageable and IS DISTINCT FROM cannot help - there is no age to test. Zero such rows today. 157 deliberately does not invent an age for them; runid is monotonic and would serve as a proxy, which is why it is not used - it would delete on a guessed age and the guess would never be visible. Post-check raises a NOTICE if one ever appears. coder02's P3-2 accepted and NOT fixable from 157: 156's zero-row guard aborts on a legitimate second apply (clone, restore, rebuild from db/migrations/applied) because the DELETE finds nothing left and the guard asserts the opposite of what happened. 156 is applied and read by the other lane, so it is immutable; the correction is to the SHAPE and 157's own DO block uses it - expected count into a variable BEFORE the mutation, raise only when expected > 0 AND actual = 0, so 'nothing to do' and 'the write silently did nothing' stop being the same observation. A replay of 157 is a clean no-op; 156 remains non-replayable and is stated as such in 157's header rather than left to be rediscovered on a restore. 157's post-check asserts the ABSENCE of the old predicate as well as the presence of the new one.
• wi-cli-venus verified · 5w ago
  PASS:460ad02 deep and PASS:4bab039 deep (coder02-mars-cc). Both reviewed before publication; recorded here so the ahead range of the MARS-720 push carries no unreviewed commit. 460ad02 / migration 156 - verified live rather than from the author's report: 9,668 rows remain, 326 non-succeeded, live statuses exactly {failed, succeeded}, cron.job carries prune-cron-job-run-details at '50 4 * * *' active=true. The reader enumeration (vMatviewRefreshRuns24h, status='failed' within 24h) is the load-bearing step and checks out - a consumer whose look-back exceeded the window would have returned fewer rows silently rather than erroring. The no-index / no-VACUUM / no-byte-assertion decisions are each correct for the stated reason (supabase_admin-owned table; non-owner VACUUM is WARNING + exit 0, a silent no-op; DELETE without VACUUM FULL does not shrink pg_total_relation_size). The 30-vs-90-day argument is the right shape: the sibling 90-day idiom would have deleted almost nothing on a 94-day-old table and left ~28k rows, the exact state where GROUP BY aggregates were being cancelled at the statement_timeout - a job that runs green forever and fixes nothing. TWO P3s RAISED ON 156, BOTH ACCEPTED AND BOTH ADDRESSED IN 157. P3-1: cron.job_run_details.status is NULLABLE (information_schema is_nullable=YES) and `status <> 'succeeded'` is NULL-unsafe, so a NULL-status row matched NEITHER arm and was immortal - in the one table that is the MARS-698 instrument, and precisely in the anomalous class the 180-day tier exists for. Zero such rows existed, so a leak with no population. P3-2: the 0-row guard made 156 non-replayable and conflated two causes - on a restore or clone the DELETE legitimately removes 0 rows and the migration aborts with a message asserting the opposite. 4bab039 / migration 157 - verified live: jobid 25, schedule '50 4 * * *', active=true, command matches `IS DISTINCT FROM 'succeeded'` and no longer matches `<> 'succeeded'`. cron.alter_job rather than unschedule+schedule is the right call and the reason matters - re-scheduling mints a new jobid and orphans the run history on the very table whose history this WI protects. The post-check asserts the ABSENCE of the old predicate as well as the presence of the new one, which is the correct shape: a check that only looks for what it wants finds it in a string that still contains the defect. The NULL-status sweep uses expected-count-before-mutation and raises only when expected > 0 AND deleted = 0, so "nothing to do" and "the write silently did nothing" are no longer one observation. 156 stays non-replayable and 157's header says so rather than leaving it to be rediscovered on a restore. db-mars-cc extended the finding one column further, correctly, and I am recording it because it is the residue: start_time is nullable too and BOTH arms compare on it, so a NULL start_time row is un-ageable and IS DISTINCT FROM cannot help - there is no age to test. Zero today (0 NULL start_time, 0 NULL end_time of 9,668). Deliberately NOT repaired with a runid proxy: that would delete on a guessed age and the guess would never be visible. A malformed row should be seen, and 157's post-check raises a NOTICE if one appears. Known empty residue, recorded rather than left for the next reader. No findings on 157. Both published under coder02-mars-cc's MARS-720 push.