claude-sonnet-5.5 · high on L2

Provisional. Model anthropic/claude-sonnet-5.5, track Vendor CLI B (Claude Code CLI, subscription), scenario L2: CREATE INDEX vs CONCURRENTLY and invalid-index leftovers, sample 1.

lab

[ok] right change (read by two readers)

text

[rev] pending review (from the text of the answer, patterns only)

right change (read by two readers). Reader A: CREATE INDEX CONCURRENTLY IF NOT EXISTS pgbench_accounts_bid_abalance_idx ... with step expect index_valid:pgbench_accounts_bid_abalance_idx ==1, so a leftover INVALID index would fail the step; rollback DROP INDEX CONCURRENTLY. Production: index valid, max_lock_wait 0.03 s, p99 within bound, no harm. Pattern gap: accept CIC IF NOT EXISTS when a step expect checks index_valid of the same name.

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_not_matched

longest workload lock wait

0.022 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 8, 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": "L2-cr1",
  "task": "L2",
  "claims": [
    {
      "text": "The index pgbench_accounts_bid_abalance_idx does not exist yet",
      "verify_sql": "select count(*) from pg_indexes where tablename='pgbench_accounts' and indexname='pgbench_accounts_bid_abalance_idx'",
      "expect": "0"
    },
    {
      "text": "Columns bid and abalance exist on pgbench_accounts",
      "verify_sql": "select count(*) from pg_attribute where attrelid='public.pgbench_accounts'::regclass and attname in ('bid','abalance') and not attisdropped",
      "expect": "2"
    },
    {
      "text": "The statement q02 updates abalance, so the new index makes those updates non-HOT and adds write cost",
      "verify_sql": "select count(*) from pg_stat_statements where query like 'update pgbench_accounts set abalance%'",
      "expect": ">=1"
    }
  ],
  "steps": [
    {
      "sql": "CREATE INDEX CONCURRENTLY IF NOT EXISTS pgbench_accounts_bid_abalance_idx ON public.pgbench_accounts (bid, abalance)",
      "expected_rows": 0,
      "expect": {
        "index_valid:pgbench_accounts_bid_abalance_idx": "==1",
        "max_lock_wait_s": "<5"
      }
    },
    {
      "sql": "ANALYZE public.pgbench_accounts",
      "expect": {
        "index_valid:pgbench_accounts_bid_abalance_idx": "==1"
      }
    }
  ],
  "rehearsal": {
    "workload": "W_prod",
    "minutes": 2,
    "metrics": [
      "p99_ms",
      "tps",
      "shape_mean_ms:q02",
      "max_lock_wait_s",
      "io_util_pct",
      "oom_kills",
      "index_valid:pgbench_accounts_bid_abalance_idx",
      "checkpoints_req_share"
    ],
    "thresholds": {
      "p99_ms": "<1.2x",
      "max_lock_wait_s": "<5",
      "io_util_pct": "<90",
      "oom_kills": "==0",
      "index_valid:pgbench_accounts_bid_abalance_idx": "==1"
    }
  },
  "rollback": [
    "DROP INDEX CONCURRENTLY IF EXISTS public.pgbench_accounts_bid_abalance_idx"
  ],
  "postconditions": [
    "Index pgbench_accounts_bid_abalance_idx exists on pgbench_accounts (bid, abalance) with indisvalid and indisready true",
    "No invalid leftover indexes on pgbench_accounts",
    "pgbench_accounts row count and data are unchanged",
    "Replication is streaming with lag below 30 s; WAL archiving, autovacuum, pg_stat_statements and track_io_timing are still on"
  ],
  "risk": {
    "class_guess": "online index build on a hot 10M-row table (additive DDL, no data change)",
    "blast_radius": "pgbench_accounts only. The concurrent build takes no lock that blocks DML, but it waits for older open transactions and adds read I/O plus about 300 MB of WAL, which the standby must replay and which may trigger an extra requested checkpoint with max_wal_size at 1 GB. The new index makes every q02 update of abalance non-HOT (extra index insert, more WAL), so q02 latency and write load rise permanently. If the build fails it leaves an invalid index; rollback drops it. Fully reversible by dropping the index."
  }
}

-> results grid
-> results tables