no-column-type-change

What triggers it

Fires on the AT_AlterColumnType subtype inside AlterTableStmt.cmds — any ALTER TABLE ... ALTER COLUMN ... TYPE ....

What does not

Any AlterTableStmt whose commands don't include AT_AlterColumnType — plain ADD COLUMN or DROP COLUMN alterations, for instance — is skipped, as is any statement that isn't an AlterTableStmt at all.

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

PostgreSQL rewrites the whole table to change a column's on-disk representation, holding ACCESS EXCLUSIVE for the entire rewrite — reads and writes are blocked until it completes, which on a large table can take hours.

Why it matters

Changing a column type requires rewriting every row in the table while holding an ACCESS EXCLUSIVE lock. Use the expand-contract pattern: add a new column, backfill, swap.

Unsafe, and safe

ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- Expand-contract pattern:
ALTER TABLE users ADD COLUMN age_new BIGINT;
UPDATE users SET age_new = age;
-- Deploy code to read from age_new
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN age_new TO age;

Deploy and transaction boundaries

The expand-contract fix adds a new column, backfills it, syncs writes with a trigger, then swaps — the swap step means application code has to be pointed at the new column, so the full sequence spans more than one deploy.

What it assumes

The rule flags every ALTER COLUMN TYPE the same way regardless of table size, so a type change on a small or empty table — where the rewrite is effectively instant — is flagged identically to one on a table with a hundred million rows.

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

✗ [MP007] CRITICAL (line 1)
  ALTER COLUMN TYPE on "users"."age" rewrites the entire table under ACCESS EXCLUSIVE lock, blocking all reads and writes.

  Safe alternative:
  -- Use the expand-contract pattern:
  -- Step 1: Add new column with desired type
  ALTER TABLE users ADD COLUMN age_new <new_type>;
  
  -- Step 2: Backfill in batches
  UPDATE users SET age_new = age::<new_type>
    WHERE id IN (SELECT id FROM users WHERE age_new IS NULL LIMIT 10000);
  
  -- Step 3: Create trigger to sync writes (during backfill)
  -- Step 4: Swap columns (brief lock)
  -- Step 5: Drop old column

  Why: Changing a column type rewrites every row in the table while holding an ACCESS EXCLUSIVE lock that blocks all reads and writes. On large tables this can take hours, causing extended downtime.
  Docs: https://migrationpilot.dev/rules/mp007

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 MP007
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

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

rules:
  MP007: false

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

• MP044no-data-loss-type-narrowingnarrowing types can cause data loss
• MP010no-rename-columnrenaming also uses expand-contract
• MP011unbatched-data-backfillbackfills should be batched