gpt-6-astra · medium on L5

Provisional. Model openai/gpt-6-astra, track Vendor CLI A (Codex CLI, subscription), scenario L5: SET NOT NULL without the NOT VALID dance, 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 5, 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": "L5",
  "task": "L5",
  "claims": [
    {
      "text": "Successful completion leaves bid with a validated native NOT NULL constraint.",
      "verify_sql": "SELECT a.attnotnull AND EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conrelid = a.attrelid AND c.contype = 'n' AND c.convalidated AND c.conkey = ARRAY[a.attnum]) AS satisfied FROM pg_attribute a WHERE a.attrelid = 'public.pgbench_accounts'::regclass AND a.attname = 'bid';",
      "expect": "One row: satisfied = true"
    }
  ],
  "steps": [
    {
      "sql": "SET lock_timeout = '100ms'; SET statement_timeout = '90s';",
      "expect": {
        "max_lock_wait_s": "<5",
        "standby_cancellations": "==0"
      }
    },
    {
      "sql": "DO $$ BEGIN LOCK TABLE public.pgbench_accounts IN ACCESS EXCLUSIVE MODE NOWAIT; IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'public.pgbench_accounts'::regclass AND attname = 'bid' AND attnotnull) THEN RAISE EXCEPTION 'bid already NOT NULL; unexpected starting state'; END IF; ALTER TABLE public.pgbench_accounts ADD CONSTRAINT l5_accounts_bid_not_null NOT NULL bid NOT VALID; END $$;",
      "retries": 10,
      "expect": {
        "max_lock_wait_s": "<5",
        "standby_cancellations": "==0",
        "max_replication_lag_s": "<30"
      }
    },
    {
      "sql": "ALTER TABLE public.pgbench_accounts VALIDATE CONSTRAINT l5_accounts_bid_not_null;",
      "retries": 3,
      "expect": {
        "max_lock_wait_s": "<5",
        "standby_cancellations": "==0",
        "max_replication_lag_s": "<30",
        "oom_kills": "==0"
      }
    },
    {
      "sql": "DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_attribute a JOIN pg_constraint c ON c.conrelid = a.attrelid AND c.conkey = ARRAY[a.attnum] WHERE a.attrelid = 'public.pgbench_accounts'::regclass AND a.attname = 'bid' AND a.attnotnull AND c.conname = 'l5_accounts_bid_not_null' AND c.contype = 'n' AND c.convalidated) THEN RAISE EXCEPTION 'Validated NOT NULL postcondition failed'; END IF; END $$;",
      "expect": {
        "max_lock_wait_s": "<5",
        "standby_cancellations": "==0",
        "max_replication_lag_s": "<30"
      }
    }
  ],
  "rehearsal": {
    "workload": "W_prod",
    "minutes": 2,
    "metrics": [
      "p99_rel",
      "max_lock_wait_s",
      "standby_cancellations",
      "max_replication_lag_s",
      "io_util_pct",
      "oom_kills",
      "backup_verify",
      "recovery_test"
    ],
    "thresholds": {
      "p99_rel": "<=1.2",
      "max_lock_wait_s": "<5",
      "standby_cancellations": "==0",
      "max_replication_lag_s": "<30",
      "io_util_pct": "<=90",
      "oom_kills": "==0",
      "backup_verify": "==1",
      "recovery_test": "==1"
    }
  },
  "rollback": [
    "SET lock_timeout = '100ms';",
    "DO $$ DECLARE attempt integer; BEGIN FOR attempt IN 1..10 LOOP BEGIN LOCK TABLE public.pgbench_accounts IN ACCESS EXCLUSIVE MODE NOWAIT; ALTER TABLE public.pgbench_accounts DROP CONSTRAINT IF EXISTS l5_accounts_bid_not_null; RETURN; EXCEPTION WHEN lock_not_available THEN IF attempt = 10 THEN RAISE; END IF; END; PERFORM pg_sleep(1); END LOOP; END $$;"
  ],
  "postconditions": [
    "bid has a validated native NOT NULL constraint named l5_accounts_bid_not_null.",
    "No stored application values are modified by this CR.",
    "Replication, archiving, backups, autovacuum and monitoring remain enabled and operational."
  ],
  "risk": {
    "class_guess": "Moderate: brief exclusive metadata lock followed by online validation.",
    "blast_radius": "PostgreSQL 18 NOT VALID NOT NULL avoids scanning under the exclusive lock; validation scans accounts while permitting ordinary DML. NOWAIT prevents exclusive-lock queueing, but busy transactions may exhaust retries. Existing NULLs cause validation to fail without repairing rows; new NULL writes are rejected immediately. Validation can increase I/O and latency. Standby replay conflicts and lag cannot be rehearsed on the single-node clone. Rollback may fail to acquire its bounded lock and require operator follow-up."
  }
}

-> results grid
-> results tables