schemaproof/examples/stable-vs-volatile-default.sql
caelum0x 5c3b2f55df fix: now()/CURRENT_TIMESTAMP/CURRENT_DATE falsely flagged as forcing a table rewrite
add-column-volatile-default treated every non-literal DEFAULT (any FuncCall
or SQLValueFunction) as "possibly volatile" and returned BLOCK, including
`DEFAULT now()` / `CURRENT_TIMESTAMP` / `CURRENT_DATE` -- one of the most
common real-world migration patterns (created_at/updated_at columns).

Verified live against a real seeded Postgres 14 instance (200k-row table,
not guessed from docs): ADD COLUMN ... DEFAULT now()/CURRENT_TIMESTAMP/
CURRENT_DATE left pg_relation_filenode() unchanged and
pg_attribute.atthasmissing=true (fast-default path, no rewrite) -- same as
a literal constant. By contrast DEFAULT random() (genuinely VOLATILE,
pg_proc.provolatile='v') did change the relfilenode and gave existing rows
distinct per-row values -- a real rewrite, confirming gen_random_uuid(),
nextval(...), clock_timestamp() must keep returning BLOCK.

Fix: treat SQLValueFunction nodes (CURRENT_TIMESTAMP/CURRENT_DATE/
CURRENT_TIME/etc.) as safe, and allow-list the now() FuncCall by name (it's
STABLE, not VOLATILE). Re-ran existing examples/safe-migration.sql and
risky-migration.sql -- no regressions (same CAUTION/BLOCK verdicts as
before). Added examples/stable-vs-volatile-default.sql as a real repro.
`npx tsc --noEmit` clean; `npm run build` + compiled bin/schemaproof.mjs
re-verified the same fix end to end.
2026-10-29 02:36:44 +03:00

17 lines
949 B
SQL

-- Regression repro for the add-column-volatile-default rule.
--
-- The first three statements use STABLE expressions (now(), CURRENT_DATE,
-- CURRENT_TIMESTAMP). Verified live against a seeded Postgres 14 instance
-- (200k-row table): pg_relation_filenode() is unchanged and
-- pg_attribute.atthasmissing is true after each of these -- the same
-- fast-default path (no table rewrite) as a literal constant. These must
-- report INFO / no findings.
ALTER TABLE orders ADD COLUMN created_at timestamptz DEFAULT now();
ALTER TABLE orders ADD COLUMN signup_date date DEFAULT CURRENT_DATE;
ALTER TABLE orders ADD COLUMN updated_at timestamptz DEFAULT CURRENT_TIMESTAMP;
-- gen_random_uuid() is genuinely VOLATILE (pg_proc.provolatile = 'v').
-- Verified live: this one DOES change the relfilenode and give every
-- existing row a distinct value -- a real rewrite. Must report BLOCK.
ALTER TABLE orders ADD COLUMN token uuid DEFAULT gen_random_uuid();