claude-opus-5.5 · high on L6

Provisional. Model anthropic/claude-opus-5.5, track Vendor CLI B (Claude Code CLI, subscription), scenario L6: FK / CHECK added without NOT VALID + VALIDATE, 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.056 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 10, 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-L6-accounts-check-history-fk",
  "task": "L6",
  "claims": [
    {
      "text": "Neither pgbench_accounts_abalance_range nor pgbench_history_aid_fkey exists yet; the only constraints on the two tables are pgbench_accounts_pkey and the aid not-null constraint.",
      "verify_sql": "select conname from pg_constraint where conrelid in ('pgbench_accounts'::regclass,'pgbench_history'::regclass) and conname in ('pgbench_accounts_abalance_range','pgbench_history_aid_fkey')",
      "expect": "0 rows"
    },
    {
      "text": "pgbench_accounts(aid) has a unique btree index (pgbench_accounts_pkey), so it can be referenced by a foreign key and FK checks on insert are index lookups.",
      "verify_sql": "select indexrelid::regclass, indisunique, indisvalid from pg_index where indrelid='pgbench_accounts'::regclass and indisprimary",
      "expect": "pgbench_accounts_pkey | t | t"
    },
    {
      "text": "No existing pgbench_history row references a missing account (checked again inside step 1, which aborts the CR otherwise).",
      "verify_sql": "select count(*) from pgbench_history h where h.aid is not null and not exists (select 1 from pgbench_accounts a where a.aid = h.aid)",
      "expect": "0"
    },
    {
      "text": "All current balances are within -1e9..1e9 (checked again inside step 1, which aborts the CR otherwise).",
      "verify_sql": "select count(*) from pgbench_accounts where abalance < -1000000000 or abalance > 1000000000",
      "expect": "0"
    },
    {
      "text": "pgbench_history.aid and pgbench_accounts.aid are both integer, so the FK is type-compatible.",
      "verify_sql": "select attrelid::regclass, format_type(atttypid, atttypmod) from pg_attribute where attname='aid' and attrelid in ('pgbench_accounts'::regclass,'pgbench_history'::regclass)",
      "expect": "both integer"
    },
    {
      "text": "The workload never deletes from pgbench_accounts or changes its aid; updates (q18) touch only abalance, so FK row locks (FOR KEY SHARE) taken by history inserts do not conflict with them.",
      "verify_sql": "select query from pg_stat_statements where query ilike '%pgbench_accounts%' and (query ilike 'delete%' or query ilike '%set aid%')",
      "expect": "0 rows"
    }
  ],
  "steps": [
    {
      "sql": "DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid IN ('public.pgbench_accounts'::regclass, 'public.pgbench_history'::regclass) AND conname IN ('pgbench_accounts_abalance_range', 'pgbench_history_aid_fkey')) THEN RAISE EXCEPTION 'L6 precheck: constraint name already exists'; END IF; IF EXISTS (SELECT 1 FROM public.pgbench_history h WHERE h.aid IS NOT NULL AND NOT EXISTS (SELECT 1 FROM public.pgbench_accounts a WHERE a.aid = h.aid)) THEN RAISE EXCEPTION 'L6 precheck: orphan pgbench_history rows exist; requester must decide how to fix data'; END IF; IF EXISTS (SELECT 1 FROM public.pgbench_accounts WHERE abalance < -1000000000 OR abalance > 1000000000) THEN RAISE EXCEPTION 'L6 precheck: abalance values outside +/- 1e9 exist; requester must decide how to fix data'; END IF; END $$",
      "expect": {
        "max_lock_wait_s": "<5"
      }
    },
    {
      "sql": "DO $$ DECLARE attempts int := 0; BEGIN PERFORM set_config('lock_timeout', '50ms', true); LOOP BEGIN ALTER TABLE public.pgbench_accounts ADD CONSTRAINT pgbench_accounts_abalance_range CHECK (abalance BETWEEN -1000000000 AND 1000000000) NOT VALID; EXIT; EXCEPTION WHEN lock_not_available THEN attempts := attempts + 1; IF attempts >= 60 THEN RAISE EXCEPTION 'L6: could not lock pgbench_accounts after % attempts', attempts; END IF; PERFORM pg_sleep(0.3); END; END LOOP; END $$",
      "expect": {
        "max_lock_wait_s": "<5"
      }
    },
    {
      "sql": "DO $$ DECLARE attempts int := 0; BEGIN PERFORM set_config('lock_timeout', '50ms', true); LOOP BEGIN ALTER TABLE public.pgbench_history ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES public.pgbench_accounts (aid) NOT VALID; EXIT; EXCEPTION WHEN lock_not_available THEN attempts := attempts + 1; IF attempts >= 60 THEN RAISE EXCEPTION 'L6: could not lock pgbench_history/pgbench_accounts after % attempts', attempts; END IF; PERFORM pg_sleep(0.3); END; END LOOP; END $$",
      "expect": {
        "max_lock_wait_s": "<5"
      }
    },
    {
      "sql": "SET lock_timeout = '10s'",
      "expect": {}
    },
    {
      "sql": "ALTER TABLE public.pgbench_history VALIDATE CONSTRAINT pgbench_history_aid_fkey",
      "retries": 3,
      "expect": {
        "max_lock_wait_s": "<5"
      }
    },
    {
      "sql": "ALTER TABLE public.pgbench_accounts VALIDATE CONSTRAINT pgbench_accounts_abalance_range",
      "retries": 3,
      "expect": {
        "max_lock_wait_s": "<5",
        "oom_kills": "==0"
      }
    },
    {
      "sql": "DO $$ BEGIN IF (SELECT count(*) FROM pg_constraint WHERE conrelid = 'public.pgbench_accounts'::regclass AND conname = 'pgbench_accounts_abalance_range' AND contype = 'c' AND convalidated AND NOT condeferrable) <> 1 THEN RAISE EXCEPTION 'L6 postcheck: check constraint missing or not validated'; END IF; IF (SELECT count(*) FROM pg_constraint WHERE conrelid = 'public.pgbench_history'::regclass AND conname = 'pgbench_history_aid_fkey' AND contype = 'f' AND confrelid = 'public.pgbench_accounts'::regclass AND convalidated AND NOT condeferrable) <> 1 THEN RAISE EXCEPTION 'L6 postcheck: foreign key missing or not validated'; END IF; END $$",
      "expect": {
        "max_lock_wait_s": "<5"
      }
    },
    {
      "sql": "RESET lock_timeout",
      "expect": {}
    }
  ],
  "rehearsal": {
    "workload": "W_prod",
    "minutes": 2,
    "metrics": [
      "p99_ms",
      "p99_rel",
      "tps",
      "max_lock_wait_s",
      "shape_mean_ms:q04",
      "shape_mean_ms:q18",
      "shape_mean_ms:q01",
      "io_util_pct",
      "oom_kills"
    ],
    "thresholds": {
      "p99_ms": "<1.2x",
      "max_lock_wait_s": "<5",
      "oom_kills": "==0"
    }
  },
  "rollback": [
    "DO $$ DECLARE attempts int := 0; BEGIN PERFORM set_config('lock_timeout', '50ms', true); LOOP BEGIN ALTER TABLE public.pgbench_history DROP CONSTRAINT IF EXISTS pgbench_history_aid_fkey; EXIT; EXCEPTION WHEN lock_not_available THEN attempts := attempts + 1; IF attempts >= 40 THEN RAISE EXCEPTION 'rollback: could not lock pgbench_history after % attempts', attempts; END IF; PERFORM pg_sleep(0.3); END; END LOOP; END $$",
    "DO $$ DECLARE attempts int := 0; BEGIN PERFORM set_config('lock_timeout', '50ms', true); LOOP BEGIN ALTER TABLE public.pgbench_accounts DROP CONSTRAINT IF EXISTS pgbench_accounts_abalance_range; EXIT; EXCEPTION WHEN lock_not_available THEN attempts := attempts + 1; IF attempts >= 40 THEN RAISE EXCEPTION 'rollback: could not lock pgbench_accounts after % attempts', attempts; END IF; PERFORM pg_sleep(0.3); END; END LOOP; END $$"
  ],
  "postconditions": [
    "pg_constraint has pgbench_accounts_abalance_range (contype c, convalidated true, not deferrable) on pgbench_accounts with CHECK (abalance BETWEEN -1000000000 AND 1000000000)",
    "pg_constraint has pgbench_history_aid_fkey (contype f, convalidated true, not deferrable) on pgbench_history(aid) referencing pgbench_accounts(aid)",
    "No rows were modified or deleted by the CR; pgbench_history and pgbench_accounts row counts only reflect workload activity",
    "Standby is streaming with replay lag under 30 s and no recovery-conflict cancellations",
    "autovacuum, WAL archiving, pg_stat_statements and track_io_timing unchanged (no config steps)",
    "No scheduled jobs, deferrable constraints or deferred triggers created; the only new triggers are the internal RI triggers of pgbench_history_aid_fkey"
  ],
  "risk": {
    "class_guess": "low-medium: online DDL with brief strong locks (ACCESS EXCLUSIVE on pgbench_accounts for the CHECK; SHARE ROW EXCLUSIVE on both tables for the FK), acquired via a bounded 50 ms lock_timeout retry loop; validation runs under SHARE UPDATE EXCLUSIVE, which does not block the workload",
    "blast_radius": "pgbench_accounts and pgbench_history. Each lock attempt can queue workload statements on these tables for at most 50 ms (worst case about 3 s total per DDL). The standby replays the brief ACCESS EXCLUSIVE lock, so standby reads of pgbench_accounts may pause for milliseconds. Validating the CHECK (and the step 1 range precheck) each scans about 1.5 GB of pgbench_accounts, which adds read I/O and keeps an xmin for several seconds. After the change, each history insert (q04) does a pkey lookup with FOR KEY SHARE on the account row. That adds a little latency and some row-lock/multixact WAL, but it does not conflict with q18 updates of abalance. If existing data violates either rule, step 1 aborts before any lock is taken; fixing the data needs a requester decision. If the workload itself inserts orphan aids or pushes a balance past ±1e9, those statements would start failing once the NOT VALID constraints exist. Rehearsal under W_prod would show this, and rollback drops both constraints. The change modifies no rows and is fully reversible by dropping the two constraints."
  }
}

-> results grid
-> results tables