require-attach-partition-check
ATTACH PARTITION without a matching CHECK constraint scans the whole table under ACCESS EXCLUSIVE to validate the bound.
- operation
- Partitions
- lock taken
- ACCESS EXCLUSIVE
- remediation
- Manual rewrite
- category
- Partitioning
- Blocks reads
- Blocks writes
- Scans every row
What triggers it
The AT_AttachPartition subcommand of ALTER TABLE when the partition being attached has no CHECK constraint added earlier in the same migration — via ALTER TABLE ... ADD CONSTRAINT or inline in its own CREATE TABLE.
What does not
Non-AlterTableStmt statements, or ones without an AT_AttachPartition command. An attach where the incoming table already has a CHECK constraint added earlier in the file — hasCheckConstraintOn only requires a CONSTR_CHECK constraint to exist, not that its expression actually implies the partition bound.
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 on both the incoming table and the parent for the whole validation scan — the entire partitioned table, every partition, is unavailable while PostgreSQL proves the incoming rows fit the bound.
Why it matters
ATTACH PARTITION is meant to be a catalog operation, and mostly it is. The exception is validation: PostgreSQL proves every row in the incoming table satisfies the partition bound by reading all of them, while holding ACCESS EXCLUSIVE on both the incoming table and the parent — so the entire partitioned table, every partition, is unavailable for the duration. An existing CHECK that implies the bound lets PostgreSQL skip the scan, moving the work to VALIDATE CONSTRAINT, which takes a lock that allows reads and writes.
Unsafe, and safe
Flagged
ALTER TABLE events ATTACH PARTITION events_2026_01 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'); -- Full scan of events_2026_01, ACCESS EXCLUSIVE on the whole hierarchy
Safe alternative
-- 1. Add a CHECK matching the bound, without validating it yet ALTER TABLE events_2026_01 ADD CONSTRAINT events_2026_01_bound CHECK (ts >= '2026-01-01' AND ts < '2026-02-01') NOT VALID; -- 2. Validate it under a lock that lets traffic through ALTER TABLE events_2026_01 VALIDATE CONSTRAINT events_2026_01_bound; -- 3. The attach is now catalog-only ALTER TABLE events ATTACH PARTITION events_2026_01 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
What it assumes
Assumes any CHECK constraint on the incoming table means PostgreSQL will skip the scan, but it only checks that a CONSTR_CHECK exists, not that the expression matches the partition bound. A CHECK that doesn't actually imply the bound satisfies this rule while PostgreSQL still performs the full scan at ATTACH time.
What the CLI prints
⚠ [MP094] WARNING (line 1)
ATTACH PARTITION "events_2026_01" onto "events" has no matching CHECK constraint on "events_2026_01", so PostgreSQL scans the whole table to validate the bound while holding ACCESS EXCLUSIVE on both tables.
Safe alternative:
-- Add a CHECK matching the partition bound first. PostgreSQL then trusts
-- it and skips the validation scan during ATTACH.
ALTER TABLE events_2026_01
ADD CONSTRAINT events_2026_01_bound
CHECK (partition_key >= '<lower>' AND partition_key < '<upper>') NOT VALID;
-- VALIDATE takes only SHARE UPDATE EXCLUSIVE, so reads and writes continue:
ALTER TABLE events_2026_01 VALIDATE CONSTRAINT events_2026_01_bound;
-- Now the ATTACH is a catalog-only operation:
ALTER TABLE events ATTACH PARTITION events_2026_01 FOR VALUES ...;
-- The CHECK is redundant once attached and can be dropped afterwards.
Why: Attaching a partition is meant to be a catalog operation, and it is, except for the validation scan. PostgreSQL reads every row of the incoming table to confirm it fits the partition bound, holding ACCESS EXCLUSIVE on both that table and the parent, which means the entire partitioned table is unavailable for the duration. On the multi-million-row table that a new partition usually is, that is minutes of downtime for what should be an instant operation. An equivalent CHECK constraint added beforehand lets PostgreSQL skip the scan on the strength of the constraint, and the work of validating it happens under a lock that does not block traffic.
Docs: https://migrationpilot.dev/rules/mp094Generated 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 MP094 ALTER TABLE events ATTACH PARTITION events_2026_01
For the whole project, in .migrationpilotrc.yml — by name or by id:
rules:
MP094: false
# or keep it, and downgrade it
rules:
MP094:
severity: warningTry 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 MP094 in the playground