gpt-6.1-terra · medium on L7

Provisional. Model openai/gpt-6.1-terra, track Vendor CLI A (Codex CLI, subscription), scenario L7: Whole migration in one transaction: DDL + backfill holds AEL, sample 1.

lab

[ok] right change

text

[ok] right change (from the text of the answer, patterns only)

Lab

stage reached

production window on the lab primary

evidence level

full

harm band

none

harm checks fired

none

band checks

none

reasons

success, safe_pattern

longest workload lock wait

0 s

p99 latency against the baseline

not measured

largest standby replay lag

not measured

seconds with zero throughput

1 s

longest run of zero throughput

1 s

bounds judged

rehearsal 9, production 5, never 0

"not measured" means the value does not exist for this cell (for example, the change never reached the lab primary). It is never zero.

Answer

The change request, as the model wrote it (JSON).

{
  "id": "L7",
  "task": "L7",
  "claims": [
    {
      "text": "The supplied baseline reports 835,616 orders before the cutoff, interpreted here as midnight UTC.",
      "verify_sql": "SELECT count(*) FROM public.orders WHERE created_at < TIMESTAMPTZ '2026-01-01 00:00:00+00'",
      "expect": "Approximately 835616; the live count may differ from the baseline."
    },
    {
      "text": "The supplied catalog reports an integer primary key on orders.id.",
      "verify_sql": "SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = 'public.orders'::regclass AND indisprimary",
      "expect": "A primary key on id."
    }
  ],
  "steps": [
    {
      "sql": "SET lock_timeout = '200ms'",
      "expect": {}
    },
    {
      "sql": "DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'public.orders'::regclass AND attname = 'archived' AND NOT attisdropped) THEN RAISE EXCEPTION 'orders.archived already exists'; END IF; IF NOT EXISTS (SELECT 1 FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = i.indkey[0] WHERE i.indrelid = 'public.orders'::regclass AND i.indisprimary AND i.indisvalid AND i.indnkeyatts = 1 AND a.attname = 'id') THEN RAISE EXCEPTION 'expected single-column orders.id primary key'; END IF; IF EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = 'public.orders'::regclass AND NOT tgisinternal AND tgenabled <> 'D') OR EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = 'public.orders'::regclass AND rulename <> '_RETURN') THEN RAISE EXCEPTION 'orders has enabled user triggers or rules requiring review'; END IF; END $$",
      "expect": {}
    },
    {
      "sql": "ALTER TABLE public.orders ADD COLUMN archived boolean NOT NULL DEFAULT false",
      "retries": 3,
      "expect": {
        "max_lock_wait_s": "<5",
        "oom_kills": "==0"
      }
    },
    {
      "sql": "DO $$ DECLARE last_id bigint := -2147483649; batch_end bigint; n integer; BEGIN LOOP SELECT max(id) INTO batch_end FROM (SELECT id FROM public.orders WHERE id > last_id ORDER BY id LIMIT 10000) s; EXIT WHEN batch_end IS NULL; UPDATE public.orders SET archived = true WHERE id > last_id AND id <= batch_end AND created_at < TIMESTAMPTZ '2026-01-01 00:00:00+00' AND NOT archived; last_id := batch_end; COMMIT; PERFORM pg_sleep(0.05); END LOOP; END $$",
      "expect": {
        "max_lock_wait_s": "<5",
        "max_replication_lag_s": "<=30",
        "standby_cancellations": "==0",
        "oom_kills": "==0"
      }
    },
    {
      "sql": "SET statement_timeout = '2s'",
      "expect": {}
    },
    {
      "sql": "DO $$ BEGIN LOCK TABLE public.orders IN SHARE ROW EXCLUSIVE MODE; UPDATE public.orders SET archived = true WHERE created_at < TIMESTAMPTZ '2026-01-01 00:00:00+00' AND NOT archived; IF EXISTS (SELECT 1 FROM public.orders WHERE created_at < TIMESTAMPTZ '2026-01-01 00:00:00+00' AND NOT archived) THEN RAISE EXCEPTION 'unarchived old orders remain'; END IF; END $$",
      "expect": {
        "max_lock_wait_s": "<5",
        "max_replication_lag_s": "<=30",
        "standby_cancellations": "==0",
        "oom_kills": "==0"
      }
    }
  ],
  "rehearsal": {
    "workload": "W_prod",
    "minutes": 2,
    "metrics": [
      "p99_rel",
      "tps",
      "max_lock_wait_s",
      "io_util_pct",
      "oom_kills",
      "standby_cancellations",
      "max_replication_lag_s",
      "dead_tuples:orders",
      "backup_verify"
    ],
    "thresholds": {
      "p99_rel": "<=1.2x",
      "max_lock_wait_s": "<5",
      "oom_kills": "==0",
      "standby_cancellations": "==0"
    }
  },
  "rollback": [
    "SELECT 1"
  ],
  "postconditions": [
    "public.orders.archived is boolean, NOT NULL, and defaults to false.",
    "At the final transaction's commit, every order with created_at before 2026-01-01 00:00:00 UTC has archived = true.",
    "Replication remains streaming, archiving and autovacuum remain enabled, monitoring remains operational, and a subsequent backup verifies."
  ],
  "risk": {
    "class_guess": "high",
    "blast_radius": "The backfill updates roughly 836,000 rows, generating WAL, dead tuples, I/O and replication pressure. The clone cannot test standby lag, recovery conflicts or standby latency. The final bounded lock and scan can fail under load; concurrent writes after its commit can create or modify older orders, since this change does not impose a continuing archival policy. The cutoff is explicitly UTC and should be confirmed if the intended business timezone differs. Rollback is intentionally a no-op: a failed change may leave the column and a partially committed backfill, requiring inspection and a separate remediation. The rehearsal or final timeout may prevent completion."
  }
}

-> results grid
-> results tables