← back to Homesonspec

O3: add prod apply script for the partition conversion

a15775f6ee70b4b1f377fd7db44f6d7f7ac41c52 · 2026-09-10 11:10:48 -0700 · Steve

One self-aborting script implementing ops/o3-partition-PROVEN-runbook.md.
Preflight hard-aborts on a stale/missing backup, <15G free disk, or row loss.
STEP 1 (VALIDATE CONSTRAINT) is online under SHARE UPDATE EXCLUSIVE; STEP 2 is
a single catalog-only transaction with lock_timeout so it aborts cleanly rather
than queueing behind a long transaction.

Adds two things the runbook did not have, both surfaced while preparing the
real run:

- a DEFAULT partition. Without it, a row whose createdAt falls outside every
  defined range raises 'no partition of relation found' and ALL ingestion
  stops. The verify step asserts it stays empty, which is also the alarm for a
  missing forward range.
- forward monthly partitions through 2027-12, so partition exhaustion cannot
  break ingestion unattended.

Mac2 is classifier-blocked from prod SSH, so this runs from Steve's shell.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP

Files touched

• A ops/o3-apply.sh

Diff

commit a15775f6ee70b4b1f377fd7db44f6d7f7ac41c52
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 10 11:10:48 2026 -0700

    O3: add prod apply script for the partition conversion
    
    One self-aborting script implementing ops/o3-partition-PROVEN-runbook.md.
    Preflight hard-aborts on a stale/missing backup, <15G free disk, or row loss.
    STEP 1 (VALIDATE CONSTRAINT) is online under SHARE UPDATE EXCLUSIVE; STEP 2 is
    a single catalog-only transaction with lock_timeout so it aborts cleanly rather
    than queueing behind a long transaction.
    
    Adds two things the runbook did not have, both surfaced while preparing the
    real run:
    
    - a DEFAULT partition. Without it, a row whose createdAt falls outside every
      defined range raises 'no partition of relation found' and ALL ingestion
      stops. The verify step asserts it stays empty, which is also the alarm for a
      missing forward range.
    - forward monthly partitions through 2027-12, so partition exhaustion cannot
      break ingestion unattended.
    
    Mac2 is classifier-blocked from prod SSH, so this runs from Steve's shell.
    
    Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP
---
 ops/o3-apply.sh | 112 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 112 insertions(+)

diff --git a/ops/o3-apply.sh b/ops/o3-apply.sh
new file mode 100755
index 00000000..fb247e8c
--- /dev/null
+++ b/ops/o3-apply.sh
@@ -0,0 +1,112 @@
+#!/usr/bin/env bash
+# O3 — SourceEvidence partition conversion (ATTACH path, no table rewrite).
+# Ticket TK-11363. Proof: ops/o3-partition-PROVEN-runbook.md
+# Run ON KAMATERA as root.  Aborts on ANY failure. No data is copied or deleted.
+set -euo pipefail
+PSQL="sudo -u postgres psql -d homesonspec -v ON_ERROR_STOP=1"
+Q() { sudo -u postgres psql -d homesonspec -tAc "$1"; }
+say() { printf '\n\033[1;36m=== %s ===\033[0m\n' "$*"; }
+
+say "PREFLIGHT"
+DUMP=$(ls -t /root/backups/db/homesonspec_*.dump 2>/dev/null | head -1 || true)
+[ -n "$DUMP" ] || { echo "ABORT: no homesonspec dump found in /root/backups/db"; exit 1; }
+DUMP_AGE_H=$(( ( $(date +%s) - $(stat -c %Y "$DUMP") ) / 3600 ))
+echo "backup: $DUMP (${DUMP_AGE_H}h old, $(du -h "$DUMP" | cut -f1))"
+[ "$DUMP_AGE_H" -lt 48 ] || { echo "ABORT: newest dump is ${DUMP_AGE_H}h old (>48h)"; exit 1; }
+
+FREE_G=$(df -BG --output=avail / | tail -1 | tr -dc '0-9')
+echo "disk free: ${FREE_G}G"
+[ "$FREE_G" -ge 15 ] || { echo "ABORT: <15G free"; exit 1; }
+
+IIT=$(Q "SELECT count(*) FROM pg_stat_activity WHERE datname='homesonspec' AND state='idle in transaction';")
+echo "idle-in-transaction sessions: $IIT"
+[ "$IIT" -eq 0 ] || echo "WARN: $IIT idle-in-txn session(s) — STEP 2 may hit lock_timeout and abort cleanly; that is safe, just re-run."
+
+BEFORE_ROWS=$(Q "SELECT count(*) FROM \"SourceEvidence\";")
+BEFORE_SIZE=$(Q "SELECT pg_size_pretty(pg_total_relation_size('\"SourceEvidence\"'));")
+MAXTS=$(Q "SELECT max(\"createdAt\") FROM \"SourceEvidence\";")
+echo "SourceEvidence: $BEFORE_ROWS rows, $BEFORE_SIZE, max createdAt = $MAXTS"
+echo "$BEFORE_ROWS" > /root/.o3_before_rows
+
+say "STEP 1 — bound constraint + VALIDATE (online; SHARE UPDATE EXCLUSIVE, does NOT block reads/writes)"
+echo "This is one sequential scan of ~82GB. Expect several minutes. Safe to be interrupted."
+$PSQL <<'SQL'
+ALTER TABLE "SourceEvidence" DROP CONSTRAINT IF EXISTS se_legacy_bound;
+ALTER TABLE "SourceEvidence"
+  ADD CONSTRAINT se_legacy_bound CHECK ("createdAt" < '2026-10-01'::timestamptz) NOT VALID;
+SQL
+time $PSQL -c 'ALTER TABLE "SourceEvidence" VALIDATE CONSTRAINT se_legacy_bound;'
+CONVALID=$(Q "SELECT convalidated FROM pg_constraint WHERE conname='se_legacy_bound';")
+[ "$CONVALID" = "t" ] || { echo "ABORT: constraint not validated"; exit 1; }
+echo "STEP 1 OK — constraint validated. (Rollback at this point: ALTER TABLE \"SourceEvidence\" DROP CONSTRAINT se_legacy_bound;)"
+
+say "STEP 2 — swap in partitioned parent + ATTACH legacy (fast, ACCESS EXCLUSIVE, catalog-only)"
+$PSQL <<'SQL'
+BEGIN;
+SET LOCAL lock_timeout = '10s';
+
+ALTER TABLE "SourceEvidence" RENAME TO "SourceEvidence_p_legacy";
+ALTER INDEX "SourceEvidence_pkey"                    RENAME TO "SourceEvidence_p_legacy_pkey";
+ALTER INDEX "SourceEvidence_entityType_entityId_idx" RENAME TO "SE_p_legacy_entity_idx";
+ALTER INDEX "SourceEvidence_stagedRecordId_idx"      RENAME TO "SE_p_legacy_staged_idx";
+
+-- deliberately NO PRIMARY KEY on the parent: keeps the legacy partition's own
+-- local PK valid and avoids forcing a composite (id, createdAt) data-model change.
+CREATE TABLE "SourceEvidence" (
+  LIKE "SourceEvidence_p_legacy" INCLUDING DEFAULTS INCLUDING STORAGE
+) PARTITION BY RANGE ("createdAt");
+
+ALTER TABLE "SourceEvidence"
+  ADD CONSTRAINT "SourceEvidence_stagedRecordId_fkey"
+  FOREIGN KEY ("stagedRecordId") REFERENCES "StagedRecord"(id);
+CREATE INDEX "SourceEvidence_entityType_entityId_idx" ON "SourceEvidence" ("entityType","entityId");
+CREATE INDEX "SourceEvidence_stagedRecordId_idx"      ON "SourceEvidence" ("stagedRecordId");
+
+ALTER TABLE "SourceEvidence" ATTACH PARTITION "SourceEvidence_p_legacy"
+  FOR VALUES FROM (MINVALUE) TO ('2026-10-01'::timestamptz);
+
+-- SAFETY NET: without a DEFAULT partition, a row whose createdAt falls outside every
+-- defined range raises "no partition of relation found" and ALL INGESTION STOPS.
+CREATE TABLE "SourceEvidence_p_default" PARTITION OF "SourceEvidence" DEFAULT;
+COMMIT;
+SQL
+
+say "STEP 3 — forward monthly partitions through 2027-12"
+for y in 2026 2027; do for m in 01 02 03 04 05 06 07 08 09 10 11 12; do
+  start="$y-$m-01"
+  [ "$start" \< "2026-10-01" ] && continue
+  nxt=$(date -d "$start +1 month" +%Y-%m-01)
+  $PSQL -c "CREATE TABLE IF NOT EXISTS \"SourceEvidence_p_${y}_${m}\" PARTITION OF \"SourceEvidence\" FOR VALUES FROM ('$start') TO ('$nxt');" >/dev/null
+done; done
+echo "forward partitions created through 2027-12"
+
+say "VERIFY"
+AFTER_ROWS=$(Q "SELECT count(*) FROM \"SourceEvidence\";")
+echo "rows before : $BEFORE_ROWS"
+echo "rows after  : $AFTER_ROWS   (after >= before is expected; ingestion is live)"
+[ "$AFTER_ROWS" -ge "$BEFORE_ROWS" ] || { echo "*** ABORT: ROW LOSS DETECTED ***"; exit 1; }
+echo "--- FK enforced? (expect: ERROR ... violates foreign key constraint) ---"
+sudo -u postgres psql -d homesonspec -c "INSERT INTO \"SourceEvidence\" (id,\"stagedRecordId\",field,\"sourceUrl\",\"retrievedAt\",\"extractorVersion\",confidence,\"createdAt\") VALUES ('o3probe','__NO_SUCH_SR__','x','x',now(),'v',0.1,now());" 2>&1 | head -2
+echo "--- partitions ---"
+$PSQL -c "SELECT c.relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_inherits i ON i.inhrelid=c.oid WHERE i.inhparent='\"SourceEvidence\"'::regclass ORDER BY 1;"
+echo "--- default partition must stay EMPTY (non-zero = a partition range is missing) ---"
+$PSQL -c "SELECT count(*) AS rows_in_default FROM \"SourceEvidence_p_default\";"
+echo "--- hot path still indexed ---"
+$PSQL -c "EXPLAIN (COSTS OFF) SELECT * FROM \"SourceEvidence\" WHERE \"stagedRecordId\"='probe';" | head -8
+
+say "DONE — O3 partition conversion complete. No data copied, no data deleted."
+cat <<'ROLL'
+ROLLBACK (catalog-only, no data movement):
+  BEGIN;
+  ALTER TABLE "SourceEvidence" DETACH PARTITION "SourceEvidence_p_legacy";
+  DROP TABLE "SourceEvidence";                       -- drops empty forward + default partitions
+  ALTER TABLE "SourceEvidence_p_legacy" RENAME TO "SourceEvidence";
+  ALTER INDEX "SourceEvidence_p_legacy_pkey" RENAME TO "SourceEvidence_pkey";
+  ALTER INDEX "SE_p_legacy_entity_idx"       RENAME TO "SourceEvidence_entityType_entityId_idx";
+  ALTER INDEX "SE_p_legacy_staged_idx"       RENAME TO "SourceEvidence_stagedRecordId_idx";
+  ALTER TABLE "SourceEvidence" DROP CONSTRAINT se_legacy_bound;
+  COMMIT;
+  -- NOTE: rows written to forward partitions after conversion must be copied back
+  -- into the legacy table BEFORE dropping the parent. Check first:
+  --   SELECT count(*) FROM "SourceEvidence" WHERE "createdAt" >= '2026-10-01';
+ROLL

← d6400d4d O3: prove SourceEvidence partitioning is feasible with no re  ·  back to Homesonspec  ·  O3: fix the two bugs the live run exposed (FK-before-attach cc0a0516 →