Skip to main content

PG.L4.CONSTRAINT_VALIDATION_PENDING — A constraint was added NOT VALID and never validated

  • Category: safety
  • Level: 4
  • Confidence: heuristic
  • Downtime class: online
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

This rule is not telling you that you did something wrong. ADD CONSTRAINT … NOT VALID is the safe move, and it is exactly what PG.L2.CONSTRAINT_NOT_VALIDATED asks you to do. That rule stays deliberately silent about it.

It is also only half of the pattern, and this rule is the account of the other half.

What NOT VALID actually leaves behind​

Two things are true from the moment the statement returns:

  • The constraint is enforced for every row inserted or updated afterwards.
  • The constraint is not known to hold for the rows that were already there — PostgreSQL records convalidated = false, and the planner will not rely on it.

Nothing breaks, nothing is slow, and no error is raised. That is precisely why the second step is the one that gets forgotten: there is no symptom to notice.

The deferred cost does not disappear, it moves. PostgreSQL validates a NOT VALID constraint as part of a table rewrite — so a much later migration that looked like a quick ALTER turns into a full scan nobody planned for, inside a deploy window nobody sized for it.

The second half​

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

It takes a SHARE UPDATE EXCLUSIVE lock and scans the table. That lock does not block reads or writes — which is the whole reason the pattern is split in two, and the reason this step belongs in its own migration, outside the deploy window, rather than tacked onto the one that added the constraint.

Flagged​

DB::statement('ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID');

Preferred​

// The same first half, with the second half scheduled as its own migration:
DB::statement('ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID');
// …and in a later migration, outside the deploy window:
DB::statement('ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk');

NOT VALID is the right first step and this is not a complaint about it. Until VALIDATE CONSTRAINT runs, the constraint does not hold for the rows that were already there and the planner will not rely on it — silently, which is why the second step is the one that gets forgotten.

When the rule stays silent​

  • A VALIDATE CONSTRAINT for that constraint appears anywhere in the run — including in a later migration. That separation is the pattern, so a correctly split pair draws no finding.
  • The statement adds a constraint that is not NOT VALID at all. That case belongs to the level-2 rule, which asks a different question (does adding it lock the table?).

Why it is heuristic​

The question is asked of the whole run. On the single-file fast path (sqlens:lint --file=…) the run is one migration, so a VALIDATE living in another file is out of view and the rule reports an open end that is in fact already closed. That is the same limit the fast path carries everywhere: it sees what it was given. A full run does not have it.

Why it is level 4 and carries no security severity​

Level 4 is the backward-compatibility band — the axis on which a constraint that does not hold for existing rows genuinely is a gap. There is no security question here at all, so the rule declares no security severity and cannot breach that gate.

What makes one open end more urgent than another is its age, and age is not a property of a migration. It is what the debt account adds later, from the ledger.

Sources​

  • PostgreSQL 18 — ALTER TABLE — a constraint added NOT VALID is enforced for rows inserted or updated afterwards, existing rows are not checked, and the constraint is not assumed to hold; VALIDATE CONSTRAINT performs the scan under a SHARE UPDATE EXCLUSIVE lock that does not block reads or writes

The fix material this rule carries​

A finding from this rule carries machine-readable fix material. Which sequence depends on the statement:

  • concurrently — Build the index without taking the write lock the ordinary form takes.
  • not_valid_then_validate — Add the constraint unvalidated, then validate it in a second migration.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.