require-not-valid-check

What triggers it

Fires on the AT_AddConstraint subcommand of ALTER TABLE when the constraint contype is CONSTR_CHECK and skip_validation (NOT VALID) is not set.

What does not

Skips anything that is not an ADD CONSTRAINT of type CONSTR_CHECK, and skips a CHECK constraint that already has NOT VALID set, since skip_validation being true is exactly the safe form.

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

ADD CONSTRAINT CHECK without NOT VALID holds ACCESS EXCLUSIVE for the full validation scan across every existing row, blocking all reads and writes until it finishes, proportional to table size.

Why it matters

Adding a CHECK constraint validates all existing rows while holding ACCESS EXCLUSIVE. Use NOT VALID to skip validation, then VALIDATE CONSTRAINT separately with a lighter lock.

Unsafe, and safe

ALTER TABLE users ADD CONSTRAINT chk_age
  CHECK (age >= 0);

ALTER TABLE users ADD CONSTRAINT chk_age
  CHECK (age >= 0) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_age;

What it assumes

Assumes the table already holds rows worth scanning; on an empty or newly created table the validation is instant either way, so the NOT VALID split does not save much.

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

✗ [MP030] CRITICAL (line 1)
  CHECK constraint "chk_age" on "users" without NOT VALID scans the entire table under ACCESS EXCLUSIVE lock, blocking all reads and writes.

  Safe alternative:
  -- Step 1: Add CHECK with NOT VALID (instant, no scan)
  ALTER TABLE users ADD CONSTRAINT chk_age CHECK (...) NOT VALID;
  
  -- Step 2: Validate separately (SHARE UPDATE EXCLUSIVE, allows reads + writes)
  ALTER TABLE users VALIDATE CONSTRAINT chk_age;

  Why: ALTER TABLE ADD CONSTRAINT CHECK validates all existing rows while holding an ACCESS EXCLUSIVE lock, blocking all reads and writes. NOT VALID skips the scan during creation (instant), then VALIDATE CONSTRAINT checks rows under a less restrictive lock that allows concurrent reads and writes.
  Docs: https://migrationpilot.dev/rules/mp030

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 MP030
ALTER TABLE users ADD CONSTRAINT chk_age

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

rules:
  MP030: false

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

• MP005require-not-valid-foreign-keyFK constraints should also use NOT VALID
• MP002require-check-not-null-patternNOT NULL via CHECK pattern
• MP031ban-exclusion-constraintexclusion constraints cannot use NOT VALID