MP089warning

warn-collation-change-rewrite

Changing a column COLLATE reorders the column, forcing a table rewrite and a rebuild of every index on it.

operation: Types and domains
lock taken: ACCESS EXCLUSIVE
remediation: Manual rewrite
category: Lock safety

Blocks reads
Blocks writes
Rewrites the table

What triggers it

The AT_AlterColumnType subcommand of ALTER TABLE ... ALTER COLUMN ... TYPE (or SET DATA TYPE, which parses to the same subtype) when the new column definition carries a collClause — an explicit COLLATE.

What does not

Non-AlterTableStmt statements, and any ALTER COLUMN TYPE with no collClause on the new definition — a type change that doesn't touch collation is left to MP007.

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

ACCESS EXCLUSIVE — the same lock the type-change rewrite already holds, so the collation-driven index rebuilds happen inside it rather than as separate work you could schedule. Both reads and writes are blocked for the whole rewrite plus every index rebuild.

Why it matters

A collation is the definition of sort order, so changing it changes where every value in the column belongs. Every btree index on the column is rebuilt inside the same ACCESS EXCLUSIVE lock as the table rewrite, not as separate work you can schedule or run concurrently. Comparisons also answer differently afterwards: ORDER BY returns a different sequence, and a unique index under a collation that treats more strings as equal can start rejecting inserts that used to succeed.

Unsafe, and safe

Flagged

ALTER TABLE users ALTER COLUMN name TYPE TEXT COLLATE "en_US";
-- Table rewritten, every index on name rebuilt, all under ACCESS EXCLUSIVE

Safe alternative

-- Expand-contract keeps the index builds online.
ALTER TABLE users ADD COLUMN name_new TEXT COLLATE "en_US";
-- backfill in batches...
CREATE INDEX CONCURRENTLY idx_users_name_new ON users (name_new);
-- swap the columns in a short transaction once the data is in place

What it assumes

Assumes any explicit COLLATE on an ALTER COLUMN TYPE is a meaningful sort-order change worth flagging. It only checks for the clause's presence, not whether the new collation actually differs from the column's current one, so a redundant no-op COLLATE gets flagged the same as a real change.

What the CLI prints

migrationpilot analyze migration.sql

⚠ [MP089] WARNING (line 1)
  Changing "users"."name" to COLLATE "en_US" reorders the column. The table is rewritten and every index on the column is rebuilt, all under ACCESS EXCLUSIVE, and comparison, ORDER BY and LIKE results change afterwards.

  Safe alternative:
  -- Move the collation change off the critical path with expand-contract:
  -- 1. Add the replacement column with the new collation
  ALTER TABLE users ADD COLUMN name_new TEXT COLLATE "en_US";
  -- 2. Backfill in batches, then build its indexes without blocking
  CREATE INDEX CONCURRENTLY idx_users_name_new ON users (name_new);
  -- 3. Swap the columns in a short transaction once the data is in place
  
  -- If you must change it in place, bound the lock wait:
  SET lock_timeout = '5s';

  Why: A collation defines the sort order, so changing it changes where every value belongs. Any btree index on the column is now ordered wrongly and has to be rebuilt, and that happens inside the same ACCESS EXCLUSIVE lock as the table rewrite rather than as separate work you can schedule. The behavioural change outlasts the migration: comparisons, ORDER BY, LIKE prefix matching and range queries on the column all answer differently afterwards, so a unique index can start rejecting values it used to accept and queries can silently return rows in a different order.
  Docs: https://migrationpilot.dev/rules/mp089

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 MP089
ALTER TABLE users ALTER COLUMN name TYPE TEXT COLLATE "en_US";

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

.migrationpilotrc.yml

rules:
  MP089: false

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

Run MP089 in the playground

Related rules

← All rules This rule's doc Rule source