ban-volatile-check-constraint
CHECK constraint calling a volatile function (now(), random()) is only true at write time and rots afterwards.
- operation
- Constraints
- lock taken
- no table lock
- remediation
- Manual rewrite
- category
- Constraints & keys
- Can lose data
What triggers it
A CHECK constraint (CONSTR_CHECK, from AT_AddConstraint in ALTER TABLE or inline/table-level in CREATE TABLE) whose parsed expression tree contains a FuncCall to a known volatile function (now, random, nextval, clock_timestamp, gen_random_uuid, and others in a fixed list) or a bare SQLValueFunction such as CURRENT_TIMESTAMP/CURRENT_DATE/LOCALTIME — found by walking the actual expression tree.
What does not
CHECK constraints whose expression tree contains none of the recognized volatile calls. Because detection walks the parsed FuncCall/SQLValueFunction nodes rather than scanning text, a column merely named random_seed or now_utc doesn't trip it.
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
None — the danger isn't locking, it's that a CHECK is evaluated once at write time and never re-checked.
Why it matters
PostgreSQL does not require CHECK expressions to be IMMUTABLE, so it accepts this without a warning — there is no error to catch in review. A CHECK is evaluated when a row is written and never again, so one built on now() stops describing the rows it admitted. Two things break later, both delayed: the row becomes un-updatable, because any UPDATE re-checks the constraint, and the backup will not restore, because restoring re-adds the constraint against data that now violates it.
Unsafe, and safe
Flagged
ALTER TABLE sessions ADD CONSTRAINT sessions_not_expired CHECK (expires_at > now()); -- Accepted. Rots silently. Blocks UPDATEs and restores later.
Safe alternative
-- Compare stored values against each other — an invariant that stays true. ALTER TABLE sessions ADD CONSTRAINT sessions_expiry_after_creation CHECK (expires_at > created_at) NOT VALID; ALTER TABLE sessions VALIDATE CONSTRAINT sessions_expiry_after_creation;
What it assumes
Assumes any match against the fixed VOLATILE_FUNCTIONS list is a mistake. A volatile function outside that list — a custom or extension-provided one — would be missed entirely, and a check genuinely meant to be true only at insert time is flagged the same as an accidental one.
What the CLI prints
✗ [MP087] CRITICAL (line 1)
CHECK constraint "sessions_not_expired" on "sessions" calls the volatile function now(). PostgreSQL evaluates it only at write time, so stored rows can stop satisfying it. They then become un-updatable, and restoring a dump fails with "is violated by some row".
Safe alternative:
-- Compare against a stored value rather than the current time:
ALTER TABLE sessions ADD CONSTRAINT sessions_not_expired
CHECK (expires_at > created_at) NOT VALID;
ALTER TABLE sessions VALIDATE CONSTRAINT sessions_not_expired;
-- If the rule really is "relative to now", enforce it where it can be
-- re-evaluated: a partial index, a trigger, or the application, not in a
-- CHECK constraint that is frozen at insert time.
Why: PostgreSQL evaluates a CHECK constraint when a row is written and never again, so a predicate built on now() or random() stops describing the rows it admitted. Three things follow, none of them visible at migration time. The table quietly holds rows that violate its own constraint. Those rows become un-updatable: any UPDATE re-checks the constraint, so even writing to an unrelated column fails with "new row violates check constraint". And restoring a dump re-adds the constraint against the stored data, which fails with "is violated by some row", so the backup will not load. The constraint is not an invariant; it is a filter that was applied once.
Docs: https://migrationpilot.dev/rules/mp087Generated 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 MP087 ALTER TABLE sessions ADD CONSTRAINT sessions_not_expired
For the whole project, in .migrationpilotrc.yml — by name or by id:
rules:
MP087: false
# or keep it, and downgrade it
rules:
MP087:
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 MP087 in the playground