warn-partition-default-scan

What triggers it

The AT_AttachPartition subtype of ALTER TABLE — any ALTER TABLE <parent> ATTACH PARTITION <name> FOR VALUES ... — matched unconditionally whenever that subtype appears among the statement's cmds.

What does not

Any AlterTableCmd subtype other than AT_AttachPartition, and any AlterTableStmt with no cmds array. The rule has no check for whether a DEFAULT partition actually exists on the parent — it fires on every ATTACH PARTITION regardless.

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, held for as long as the DEFAULT partition scan takes. PostgreSQL must verify no default-partition row belongs in the new partition's range before allowing the attach, and that scan runs under the same lock blocking every other read and write.

Why it matters

When attaching a new partition, PostgreSQL scans the entire DEFAULT partition while holding an ACCESS EXCLUSIVE lock on it. If the default partition is large, this blocks all reads and writes.

The operation, and the mitigation

ALTER TABLE events ATTACH PARTITION events_2024
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Move rows from default partition first, then attach:
SET lock_timeout = '5s';
ALTER TABLE events ATTACH PARTITION events_2024
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
RESET lock_timeout;

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 rule fires on every ATTACH PARTITION even when the parent has no DEFAULT partition at all — in that case there's no scan and no risk, but the source never checks for a DEFAULT partition's existence, so it can't distinguish the two cases.

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

⚠ [MP072] WARNING (line 1)
  ATTACH PARTITION "events_2024" to "events" scans the DEFAULT partition under ACCESS EXCLUSIVE lock. Ensure the default partition is small or empty.

  Safe alternative:
  -- Before attaching, move rows from default partition to avoid a long scan:
  -- 1. Create the new partition detached first
  -- 2. Move matching rows from DEFAULT to new partition
  -- 3. Then attach:
  SET lock_timeout = '5s';
  ALTER TABLE events ATTACH PARTITION events_2024 FOR VALUES ...;
  RESET lock_timeout;

  Why: When attaching a new partition, PostgreSQL must verify that no rows in the DEFAULT partition belong to the new partition range. This requires scanning the entire DEFAULT partition while holding an ACCESS EXCLUSIVE lock on it. If the default partition is large, this scan blocks all reads and writes for its entire duration.
  Docs: https://migrationpilot.dev/rules/mp072

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 MP072
ALTER TABLE events ATTACH PARTITION events_2024

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

rules:
  MP072: false

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