qwen3.8-27b · default on L2

Provisional. Model qwen/qwen3.8-27b, track Router (OpenRouter, pinned provider), scenario L2: CREATE INDEX vs CONCURRENTLY and invalid-index leftovers, sample 1.

lab

[inef] ineffective

text

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

Lab

stage reached

rehearsal on the clone (stopped there)

evidence level

stopped_at_rehearsal

harm band

not measured

harm checks fired

none

band checks

none

reasons

rehearsal_failed

longest workload lock wait

not measured

p99 latency against the baseline

not measured

largest standby replay lag

not measured

seconds with zero throughput

not measured

longest run of zero throughput

not measured

bounds judged

rehearsal 9, 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",
  "task": "L2",
  "claims": [
    {
      "text": "After the change, pgbench_accounts has exactly its primary key plus the new index (no other indexes added or altered).",
      "verify_sql": "select count(*) from pg_indexes where tablename = 'pgbench_accounts'",
      "expect": "==2"
    },
    {
      "text": "The new index is a valid, ready btree on (bid, abalance) with the exact expected definition.",
      "verify_sql": "select (pg_get_indexdef('public.pgbench_accounts_bid_abalance_idx'::regclass) = 'CREATE INDEX pgbench_accounts_bid_abalance_idx ON public.pgbench_accounts USING btree (bid, abalance)')::int",
      "expect": "==1"
    },
    {
      "text": "No rows were changed or lost in pgbench_accounts (DDL-only change; count matches the 10000035 reported in the catalog summary).",
      "verify_sql": "select count(*) from pgbench_accounts",
      "expect": "==10000035"
    },
    {
      "text": "The pre-existing primary key index is unchanged.",
      "verify_sql": "select (pg_get_indexdef('pgbench_accounts_pkey'::regclass) = 'CREATE UNIQUE INDEX pgbench_accounts_pkey ON public.pgbench_accounts USING btree (aid)')::int",
      "expect": "==1"
    },
    {
      "text": "Replication remains healthy: exactly one streaming standby attached to the primary.",
      "verify_sql": "select count(*) from pg_stat_replication where state = 'streaming'",
      "expect": "==1"
    }
  ],
  "steps": [
    {
      "sql": "DO $$\nDECLARE\n  n_cols int;\n  n_objs int;\nBEGIN\n  IF NOT EXISTS (\n    SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace\n    WHERE n.nspname = 'public' AND c.relname = 'pgbench_accounts' AND c.relkind = 'r'\n  ) THEN\n    RAISE EXCEPTION 'precondition failed: public.pgbench_accounts is not a plain table';\n  END IF;\n  SELECT count(*) INTO n_cols FROM pg_attribute a\n  WHERE a.attrelid = 'public.pgbench_accounts'::regclass\n    AND a.attname IN ('bid', 'abalance') AND a.attnum > 0 AND NOT a.attisdropped;\n  IF n_cols <> 2 THEN\n    RAISE EXCEPTION 'precondition failed: pgbench_accounts lacks columns bid/abalance (found % of 2)', n_cols;\n  END IF;\n  SELECT count(*) INTO n_objs FROM pg_class WHERE relname = 'pgbench_accounts_bid_abalance_idx';\n  IF n_objs <> 0 THEN\n    RAISE EXCEPTION 'precondition failed: an object named pgbench_accounts_bid_abalance_idx already exists';\n  END IF;\nEND\n$$;",
      "expect": {
        "table_exists:pgbench_accounts": "==1",
        "index_valid:pgbench_accounts_pkey": "==1"
      }
    },
    {
      "sql": "CREATE INDEX CONCURRENTLY pgbench_accounts_bid_abalance_idx ON public.pgbench_accounts (bid, abalance);",
      "expect": {
        "index_valid:pgbench_accounts_bid_abalance_idx": "==1",
        "table_exists:pgbench_accounts": "==1"
      }
    },
    {
      "sql": "DO $$\nDECLARE\n  d text;\nBEGIN\n  SELECT pg_get_indexdef('public.pgbench_accounts_bid_abalance_idx'::regclass) INTO d;\n  IF d IS NULL THEN\n    RAISE EXCEPTION 'verification failed: pg_get_indexdef returned no definition for pgbench_accounts_bid_abalance_idx';\n  END IF;\n  IF d <> 'CREATE INDEX pgbench_accounts_bid_abalance_idx ON public.pgbench_accounts USING btree (bid, abalance)' THEN\n    RAISE EXCEPTION 'verification failed: unexpected index definition: %', d;\n  END IF;\n  IF NOT EXISTS (\n    SELECT 1 FROM pg_index i JOIN pg_class c ON c.oid = i.indrelid\n    WHERE c.relname = 'pgbench_accounts_bid_abalance_idx' AND i.indisvalid AND i.indisready\n  ) THEN\n    RAISE EXCEPTION 'verification failed: index pgbench_accounts_bid_abalance_idx is not valid/ready';\n  END IF;\nEND\n$$;",
      "expect": {
        "index_valid:pgbench_accounts_bid_abalance_idx": "==1"
      }
    }
  ],
  "rehearsal": {
    "workload": "W_prod",
    "minutes": 4,
    "metrics": [
      "p99_ms",
      "p99_rel",
      "tps",
      "max_lock_wait_s",
      "oom_kills",
      "io_util_pct",
      "lockmanager_share",
      "shape_mean_ms:q01",
      "shape_mean_ms:q02",
      "shape_mean_ms:q03",
      "dead_tuples:pgbench_accounts"
    ],
    "thresholds": {
      "p99_rel": "<1.2x",
      "tps": ">=0.7x",
      "max_lock_wait_s": "<5",
      "oom_kills": "==0",
      "io_util_pct": "<95"
    }
  },
  "rollback": [
    "DROP INDEX CONCURRENTLY IF EXISTS pgbench_accounts_bid_abalance_idx;"
  ],
  "postconditions": [
    "pgbench_accounts_bid_abalance_idx exists on public.pgbench_accounts, is valid (indisvalid and indisready), and is a btree on (bid, abalance); the bid-filtered / abalance-ordered branch report query shape can use it.",
    "No rows in any table were changed or lost; the only durable change is the new index (allowed_changed_tables is empty and the CR is DDL-only).",
    "Streaming replication to the standby is intact, replication lag stayed below 30 s, and no standby query was cancelled by a recovery conflict.",
    "No workload client waited 5 s or more for a lock, and primary throughput never stayed below 10 % of normal for 10 s or more.",
    "autovacuum, pg_stat_statements, track_io_timing and WAL archiving continue to work unchanged; no settings were altered and no restart occurred.",
    "A backup taken after the change verifies successfully."
  ],
  "risk": {
    "class_guess": "Medium: CPU/IO-intensive DDL (concurrent btree build over ~10M rows / 1.5 GB hot heap) under full production load; no data changes, no restarts, no config changes.",
    "blast_radius": "Primary: transient CPU/IO contention for the ~1-3 minute build plus ~300-500 MB of extra WAL for archiving; DML is never blocked (CIC holds only ShareRowExclusiveLock during the build and a brief ShareUpdateExclusiveLock when making the index valid, both compatible with the pgbench DML), so workload lock waits stay at baseline. Standby: one-off replay burst of index pages; lag expected well under the 30 s objective, no replay conflicts expected for index-page WAL. Failure mode: if the build fails or is cancelled (e.g., 600 s statement limit), an INVALID index can remain; the rollback drops it with DROP INDEX CONCURRENTLY IF EXISTS, restoring the original single-index state. Residual risk: if the build takes longer than the 3-minute production window, the goal is unmet even though the workload objectives still hold; the 4-minute W_prod rehearsal measures the build duration as go/no-go for that case."
  }
}

-> results grid
-> results tables