volatile-default-table-rewrite

What triggers it

Fires when an AT_AddColumn command's DEFAULT expression contains a call to a function PostgreSQL marks VOLATILE — gen_random_uuid(), random(), clock_timestamp(), timeofday(), nextval(), or a uuid-ossp generator — found by walking the parsed default expression tree rather than matching the rendered SQL text.

What does not

A DEFAULT that's a plain constant with no function call never fires on PostgreSQL 11+, since the missing-value fast path applies and no rewrite happens. It also returns immediately for any statement that isn't AlterTableStmt, has no commands, or has an AT_AddColumn with no DEFAULT clause at all.

Where it applies

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

On PostgreSQL versions before 11 there is no missing-value fast path at all, so the rule's separate pgVersion < 11 branch flags any function-based default — even the normally-stable ones like now() — since neither kind can use attmissingval on that version.

The lock, and what it blocks

PostgreSQL can't store a per-row volatile value in pg_attribute.attmissingval, so it writes a fresh copy of the table and every index on it, holding ACCESS EXCLUSIVE for the whole rewrite — blocking all reads and writes and needing enough free disk for a second copy of the table.

Why it matters

A non-volatile default is evaluated once and stored in pg_attribute.attmissingval, so ADD COLUMN touches no heap pages. A volatile default cannot be stored that way — PostgreSQL evaluates it separately for every existing row, which means writing a fresh copy of the table and all of its indexes while holding ACCESS EXCLUSIVE. now() and CURRENT_TIMESTAMP are stable, not volatile: they do not rewrite, but every pre-existing row is given the one value they evaluated to.

Unsafe, and safe

ALTER TABLE orders ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid();

-- Add the column with no default, so the catalog write is all it costs
ALTER TABLE orders ADD COLUMN public_id uuid;

-- Fill it in batches, then attach the default for rows written from here on
UPDATE orders SET public_id = gen_random_uuid()
WHERE public_id IS NULL
  AND id IN (SELECT id FROM orders WHERE public_id IS NULL LIMIT 10000);

ALTER TABLE orders ALTER COLUMN public_id SET DEFAULT gen_random_uuid();

Deploy and transaction boundaries

The safe fix splits into three separate steps — add the column with no default, batch-backfill the value, then attach the default for future rows — and the doc notes the backfill batches have to run outside the migration's own transaction.

What it assumes

The rule cannot tell whether the table is empty or has a billion rows — a volatile default on a brand-new, still-empty table is effectively instant, but gets flagged identically to one on a huge production table.

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

✗ [MP003] CRITICAL (line 1)
  ADD COLUMN "public_id" DEFAULT gen_random_uuid() on "orders" uses a volatile default, so PostgreSQL rewrites the entire table and every index on it while holding ACCESS EXCLUSIVE. gen_random_uuid() has to be evaluated once per existing row, which rules out the pg_attribute.attmissingval fast path a constant default takes.

  Safe alternative:
  -- Add the column with no default, so the catalog write is all it costs:
  ALTER TABLE orders ADD COLUMN public_id <type>;
  
  -- Fill it in batches, outside any long transaction. Repeat until 0 rows:
  UPDATE orders SET public_id = gen_random_uuid()
  WHERE public_id IS NULL
    AND id IN (SELECT id FROM orders WHERE public_id IS NULL LIMIT 10000);
  
  -- Only then attach the default, for rows written from here on:
  ALTER TABLE orders ALTER COLUMN public_id SET DEFAULT gen_random_uuid();

  Why: A non-volatile default is evaluated once and stored in pg_attribute.attmissingval, so ADD COLUMN touches no heap pages. A volatile default cannot be stored that way. PostgreSQL has to evaluate it separately for every existing row, which means writing a fresh copy of the table and all of its indexes while holding ACCESS EXCLUSIVE. Reads and writes are blocked for the whole rewrite, and the operation needs enough free disk for a second copy of the table before it can finish.
  Docs: https://migrationpilot.dev/rules/mp003

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 MP003
ALTER TABLE orders ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid();

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

rules:
  MP003: false

# or keep it, and downgrade it
rules:
  MP003:
    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

• MP048ban-alter-default-volatile-existingsetting volatile default on existing column
• MP015no-add-column-serialSERIAL creates implicit volatile default
• MP011unbatched-data-backfillbackfills should be batched