ban-exclusion-constraint

What triggers it

Fires on the AT_AddConstraint subcommand of ALTER TABLE when the constraint contype is CONSTR_EXCLUSION — that is, ADD CONSTRAINT ... EXCLUDE USING ....

What does not

Skips anything that is not an ADD CONSTRAINT of type CONSTR_EXCLUSION — UNIQUE, CHECK, FOREIGN KEY, and PRIMARY KEY constraints are handled by other rules and never reach this check.

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

The GiST index build and the row scan happen together under one ACCESS EXCLUSIVE lock. There is no NOT VALID escape hatch for exclusion constraints, so the whole table is blocked for reads and writes until both finish.

Why it matters

Adding an exclusion constraint creates a GiST index and validates all existing rows while holding ACCESS EXCLUSIVE. This can take a long time on large tables.

Unsafe, and safe

ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room WITH =, during WITH &&);

-- Consider application-level validation or
-- schedule during maintenance window

What it assumes

Assumes the table is large enough for the scan and index build to take meaningful time; on a small table this is effectively instant, and the rule cannot tell the difference since it fires on syntax alone.

What the CLI prints

✗ [MP031] CRITICAL (line 1)
  EXCLUSION constraint "no_overlap" on "bookings" builds a GiST index under ACCESS EXCLUSIVE lock. This cannot use NOT VALID and blocks all reads and writes for the full scan.

  Safe alternative:
  -- Exclusion constraints cannot be added without locking.
  -- Consider these alternatives:
  -- 1. Add during a maintenance window
  -- 2. Create the table with the exclusion constraint from the start
  -- 3. Use application-level uniqueness checking with advisory locks

  Why: EXCLUSION constraints require a GiST index, which is built inline while holding ACCESS EXCLUSIVE lock. Unlike CHECK or FK constraints, exclusion constraints have no NOT VALID option: the full table scan and index build happen atomically, blocking all reads and writes for the entire duration.
  Docs: https://migrationpilot.dev/rules/mp031

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 MP031
ALTER TABLE bookings ADD CONSTRAINT no_overlap

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

rules:
  MP031: false

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

• MP027disallowed-unique-constraintUNIQUE constraints should use USING INDEX
• MP030require-not-valid-checkCHECK constraints should use NOT VALID
• MP004require-lock-timeoutset lock_timeout before DDL