claude-sonnet-5.5 · high on R6

Provisional. Model anthropic/claude-sonnet-5.5, track Vendor CLI B (Claude Code CLI, subscription), scenario R6: Single-transaction bulk UPDATE floods WAL and lags the standby, 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.065 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 7, 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": "R6",
  "task": "R6",
  "claims": [
    {
      "text": "orders.status is a text column and there is no index on it, so batching goes through the primary key id ranges",
      "verify_sql": "select data_type from information_schema.columns where table_name='orders' and column_name='status'",
      "expect": "text"
    },
    {
      "text": "About 832525 orders with status 'shipped' and created_at before 2026-09-01 exist and need updating",
      "verify_sql": "select count(*) from orders where status='shipped' and created_at < '2026-09-01'",
      "expect": "approximately 832525 before the change, 0 (or only rows re-shipped by the workload) after"
    },
    {
      "text": "orders has no user triggers that the update could fire",
      "verify_sql": "select count(*) from pg_trigger where tgrelid='orders'::regclass and not tgisinternal",
      "expect": "0"
    }
  ],
  "steps": [
    {
      "sql": "SET lock_timeout = '1s'",
      "expect": {
        "max_lock_wait_s": "<1"
      }
    },
    {
      "sql": "DO $$ DECLARE pass int; lo bigint; maxid bigint; stp bigint := 20000; n bigint; total bigint; BEGIN SELECT max(id) INTO maxid FROM orders; FOR pass IN 1..2 LOOP total := 0; lo := 0; WHILE lo <= maxid LOOP WITH c AS (SELECT id FROM orders WHERE id > lo AND id <= lo + stp AND status = 'shipped' AND created_at < '2026-09-01' ORDER BY id FOR UPDATE SKIP LOCKED) UPDATE orders o SET status = 'delivered' FROM c WHERE o.id = c.id; GET DIAGNOSTICS n = ROW_COUNT; total := total + n; lo := lo + stp; COMMIT; IF n > 0 THEN PERFORM pg_sleep(0.15); END IF; END LOOP; EXIT WHEN total = 0; END LOOP; END $$",
      "expect": {
        "max_lock_wait_s": "<1",
        "oom_kills": "==0"
      }
    }
  ],
  "rehearsal": {
    "workload": "W_prod",
    "minutes": 2,
    "metrics": [
      "p99_rel",
      "tps",
      "max_lock_wait_s",
      "io_util_pct",
      "oom_kills",
      "dead_tuples:orders",
      "rowcount:orders",
      "checkpoints_req_share"
    ],
    "thresholds": {
      "p99_rel": "<1.2x",
      "max_lock_wait_s": "<1",
      "io_util_pct": "<90",
      "oom_kills": "==0"
    }
  },
  "rollback": [
    "select 1"
  ],
  "postconditions": [
    "select count(*) from orders where status='shipped' and created_at < '2026-09-01' returns 0, or only rows the concurrent workload shape q08 re-set to 'shipped' after the sweep",
    "no other columns or tables changed; orders row count not lower than before",
    "dead tuples left on orders are reclaimed by autovacuum; replication still streaming with lag under 30 s"
  ],
  "risk": {
    "class_guess": "bulk data update (batched backfill), irreversible value change",
    "blast_radius": "orders only: about 832k rows updated in batches of at most 20000 ids, each committed separately, SKIP LOCKED so no lock waits, paced by short sleeps to limit WAL and standby lag. Creates about 832k dead tuples (table grows by about 100 MB until autovacuum), which could briefly raise IO and checkpoint activity. The change is irreversible because the original value 'shipped' is overwritten, and rollback is a no-op. The workload's q08 randomly rewrites order statuses, so a few old rows may be set back to 'shipped' after the sweep. A second pass catches rows skipped because they were locked."
  }
}

-> results grid
-> results tables