ban-alter-default-volatile-existing

What triggers it

An AlterTableStmt command with subtype AT_ColumnDefault whose def contains a real call to one of now, random, gen_random_uuid, uuid_generate_v4, clock_timestamp, statement_timestamp, timeofday, txid_current, nextval, or the keyword spellings CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, LOCALTIMESTAMP, LOCALTIME. Detection walks the parsed expression for a FuncCall — matching the last element of funcname, so a schema-qualified pg_catalog.gen_random_uuid() counts — or a SQLValueFunction, which is how the keyword forms arrive. The walk reaches nested calls, so now() + interval '7 days' is caught. It shares its tree walk with MP003.

What does not

ALTER COLUMN ... DROP DEFAULT, which is also AT_ColumnDefault but arrives with no def at all. Constant defaults, including string literals that merely contain one of those names: SET DEFAULT 'nowhere' is an A_Const and is not flagged, and neither is a bare column reference named now. CURRENT_USER and the other non-temporal SQLValueFunction ops. ADD COLUMN ... DEFAULT now(), which is AT_AddColumn and belongs to MP003 and MP015. Any non-AlterTableStmt node.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

This is not a lock rule. ALTER COLUMN ... SET DEFAULT takes ACCESS EXCLUSIVE, but only momentarily: it writes one catalog row, touches no heap page, and rewrites nothing. What the rule is really about is a semantic gap — the default applies to future INSERTs only, so the existing rows the author assumed would be filled stay exactly as they were.

Why it matters

ALTER TABLE ALTER COLUMN SET DEFAULT only affects future INSERTs. Existing rows are NOT updated. Using a volatile function like now() may give the false impression that existing NULLs will be filled.

The operation, and the mitigation

ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();
-- Existing rows with NULL remain NULL!

ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();
-- Backfill existing rows explicitly:
UPDATE users SET created_at = now() WHERE created_at IS NULL;

This operation is irreversible, so there is no syntax that makes it safe. The second block is what care looks like — and MigrationPilot still flags it. The mitigation is process: confirm nothing reads the object, keep a way back, and do it in a window where you can watch.

What it assumes

The list of names is fixed, so a user-defined volatile function with an unrelated name passes silently — there is no catalog lookup to ask PostgreSQL what a function's volatility actually is. The rule also cannot tell whether a backfill is wanted: setting a default purely for rows written from here on is a legitimate thing to do, and gets flagged anyway. Earlier releases decided this by searching the serialised parse node for substrings, which reported SET DEFAULT 'nowhere' as a volatile default because the literal contains now; walking the tree for actual call nodes is what removed that.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

⚠ [MP048] WARNING (line 1)
  Setting volatile default on "users"."created_at" only affects future INSERTs. Existing rows will NOT be updated. You may need a backfill UPDATE.

  Safe alternative:
  -- SET DEFAULT only affects new rows. To backfill existing rows:
  -- 1. ALTER TABLE users ALTER COLUMN created_at SET DEFAULT <volatile_fn>;
  -- 2. UPDATE users SET created_at = DEFAULT WHERE created_at IS NULL;
  -- (Run the UPDATE in batches for large tables)

  Why: ALTER TABLE ALTER COLUMN SET DEFAULT only affects future INSERTs. Existing rows are NOT updated. Setting a volatile function like now() or gen_random_uuid() as default may give the false impression that existing NULLs will be filled. You likely need a backfill UPDATE as well.
  Docs: https://migrationpilot.dev/rules/mp048

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP048
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP048: false

# or keep it, and downgrade it
rules:
  MP048:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP003volatile-default-table-rewriteADD COLUMN with volatile default
• MP011unbatched-data-backfillbackfills should be batched
• MP015no-add-column-serialSERIAL creates implicit volatile default