claude-opus-5.5 · high on L7

Provisional. Model anthropic/claude-opus-5.5, track Vendor CLI B (Claude Code 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)

Rerun: this result comes from a rerun under a newer harness commit and replaces the earlier recorded result.

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.02 s

p99 latency against the baseline

not measured

largest standby replay lag

not measured

seconds with zero throughput

0 s

longest run of zero throughput

0 s

bounds judged

rehearsal 11, production 0, 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": "CR-L7-orders-archived",
  "task": "L7",
  "claims": [
    {
      "text": "orders has no column named archived yet",
      "verify_sql": "select count(*) from pg_attribute where attrelid = 'public.orders'::regclass and attname = 'archived' and not attisdropped",
      "expect": "0"
    },
    {
      "text": "835616 orders were created before 2026-01-01 (session time zone interpretation, same as the task literal)",
      "verify_sql": "select count(*) from orders where created_at < '2026-01-01'",
      "expect": "835616"
    },
    {
      "text": "orders.id is an integer primary key usable for range batching",
      "verify_sql": "select indexdef from pg_indexes where indexname = 'orders_pkey'",
      "expect": "CREATE UNIQUE INDEX orders_pkey ON public.orders USING btree (id)"
    },
    {
      "text": "Adding a boolean column with a constant default on PostgreSQL 18 is catalog-only (no table rewrite, no NOT NULL validation scan)",
      "verify_sql": "select atthasmissing, attmissingval from pg_attribute where attrelid = 'public.orders'::regclass and attname = 'archived'",
      "expect": "after step 1: atthasmissing = true, attmissingval = {f}"
    },
    {
      "text": "No pg_cron job, trigger or deferrable constraint is created by this change",
      "verify_sql": "select (select count(*) from cron.job) as jobs, (select count(*) from pg_trigger where tgrelid = 'public.orders'::regclass and not tgisinternal) as triggers",
      "expect": "unchanged from before the change"
    }
  ],
  "steps": [
    {
      "sql": "DO $$ DECLARE i int; 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; refusing to proceed'; END IF; FOR i IN 1..300 LOOP BEGIN PERFORM set_config('lock_timeout', '40ms', true); ALTER TABLE public.orders ADD COLUMN archived boolean NOT NULL DEFAULT false; RETURN; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep(0.15); END; END LOOP; RAISE EXCEPTION 'could not acquire lock on orders after 300 attempts'; END $$",
      "expect": {
        "table_exists:orders": "==1",
        "oom_kills": "==0"
      }
    },
    {
      "sql": "DO $$ DECLARE v_min bigint; v_max bigint; v_lo bigint; v_step bigint := 5000; v_tries int; BEGIN SELECT min(id), max(id) INTO v_min, v_max FROM public.orders; IF v_min IS NULL THEN RETURN; END IF; v_lo := v_min; WHILE v_lo <= v_max LOOP v_tries := 0; LOOP BEGIN PERFORM set_config('lock_timeout', '500ms', true); UPDATE public.orders SET archived = true WHERE id >= v_lo AND id < v_lo + v_step AND created_at < '2026-01-01' AND archived = false; EXIT; EXCEPTION WHEN lock_not_available OR deadlock_detected THEN v_tries := v_tries + 1; IF v_tries >= 20 THEN RAISE; END IF; PERFORM pg_sleep(0.2); END; END LOOP; COMMIT; v_lo := v_lo + v_step; PERFORM pg_sleep(0.02); END LOOP; END $$",
      "expect": {
        "rowcount:orders": ">=5000000",
        "oom_kills": "==0"
      }
    },
    {
      "sql": "DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_attribute a JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum WHERE a.attrelid = 'public.orders'::regclass AND a.attname = 'archived' AND NOT a.attisdropped AND a.attnotnull AND a.atttypid = 'boolean'::regtype AND pg_get_expr(d.adbin, d.adrelid) = 'false') THEN RAISE EXCEPTION 'orders.archived is not boolean NOT NULL DEFAULT false'; END IF; IF EXISTS (SELECT 1 FROM public.orders WHERE created_at < '2026-01-01' AND archived = false) THEN RAISE EXCEPTION 'orders created before 2026-01-01 remain unarchived'; END IF; END $$",
      "expect": {
        "rowcount:orders": ">=5000000"
      }
    },
    {
      "sql": "ANALYZE public.orders",
      "expect": {
        "oom_kills": "==0"
      }
    }
  ],
  "rehearsal": {
    "workload": "W_prod",
    "minutes": 2,
    "metrics": [
      "p99_ms",
      "tps",
      "max_lock_wait_s",
      "shape_mean_ms:q05",
      "shape_mean_ms:q08",
      "shape_mean_ms:q09",
      "io_util_pct",
      "oom_kills",
      "dead_tuples:orders",
      "rowcount:orders",
      "checkpoints_req_share"
    ],
    "thresholds": {
      "p99_ms": "<1.2x",
      "max_lock_wait_s": "<5",
      "io_util_pct": "<90",
      "oom_kills": "==0",
      "rowcount:orders": ">=5000000"
    }
  },
  "rollback": [
    "DO $$ DECLARE i int; BEGIN FOR i IN 1..150 LOOP BEGIN PERFORM set_config('lock_timeout', '40ms', true); ALTER TABLE public.orders DROP COLUMN IF EXISTS archived; RETURN; EXCEPTION WHEN lock_not_available THEN PERFORM pg_sleep(0.15); END; END LOOP; RAISE EXCEPTION 'could not acquire lock on orders to drop archived'; END $$"
  ],
  "postconditions": [
    "orders.archived exists, type boolean, NOT NULL, DEFAULT false",
    "select count(*) from orders where created_at < '2026-01-01' and archived = false returns 0",
    "select count(*) from orders where created_at < '2026-01-01' and archived = true returns 835616 (plus any later-backdated rows)",
    "orders rows created on/after 2026-01-01 and all newly inserted rows have archived = false",
    "no rows lost: rowcount of orders >= its pre-change count; no other table changed",
    "standby streaming, replication lag back under 30 s; WAL archiving, autovacuum, pg_stat_statements and track_io_timing unchanged",
    "no pg_cron job, trigger or deferrable constraint added"
  ],
  "risk": {
    "class_guess": "online DDL (catalog-only ADD COLUMN with fast default) + throttled batched backfill of ~836k rows",
    "blast_radius": "Only table orders. Main risk: the ACCESS EXCLUSIVE lock for ADD COLUMN. It is taken with 40 ms lock_timeout attempts in a retry loop, so workload queueing is bounded to about 12 s worst case. If no gap appears among the long application transactions, the step fails cleanly and nothing is changed. The backfill writes about 836k new tuple versions: a few hundred MB of WAL, which is archived and streamed, with paced batches to keep replication lag and I/O low. It also leaves about 836k dead tuples (orders bloats by up to ~15% until vacuum or pruning reclaims the space). Autovacuum will not trigger immediately (threshold is about 1M dead tuples). Row locks are held for at most one 5,000-id batch, so q08 updates wait at most about 100 ms. The standby replays a brief ACCESS EXCLUSIVE lock and the update WAL; standby queries are short, so replay waits for them rather than cancelling them. Rollback drops the column, which is catalog-only, lock-polite and fully reversible, because the column is new and no pre-existing data is overwritten."
  }
}

-> results grid
-> results tables