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.
17 lines
949 B
SQL
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();
|