DEPLOY.LEGACY.CONSTRAINT_NOT_VALIDATED — A constraint was added NOT VALID and the VALIDATE never followed

• Category: safety
• Severity: medium
• Level: 0
• Downtime class: online — the finding is about the state a constraint is in, not about a DDL operation; an unvalidated constraint locks nothing by existing
• Stability: stable
• Suites: deploy
• Applies to: PostgreSQL

What it reports

Every constraint the server records as never validated — one finding per constraint.

The reading comes from pg_constraint, joined to pg_class for the table and pg_namespace for the schema, filtered on convalidated = false and ordered by schema, table, constraint name. That column carries the whole meaning: it is false exactly when a constraint was added NOT VALID and no ALTER TABLE … VALIDATE CONSTRAINT ever followed. The query adds exactly one more filter — the extension exclusion below — and nothing else.

ADD CONSTRAINT … NOT VALID is the safe way to add a foreign key or a check to a large table. It takes a brief lock, applies to new rows immediately, and skips the full scan. The scan is then done separately by VALIDATE CONSTRAINT, which takes a weaker lock and can run outside the deploy window. That is the whole design, and it has one failure mode: the second half never happens.

Nothing breaks, nothing is slow, and no error is raised. The constraint simply does not hold for the rows that were already there.

The message names the consequence for the constraint type it found, because the three are genuinely different:

contype	What the finding says
f	a foreign key everybody believes is enforced is not, for every row that existed before it was added — so an orphaned reference can already be in the table
c	a check constraint that reads as a guarantee does not hold for the rows that were already there, so code written against it can meet a value it excludes
n	a NOT NULL that does not hold for existing rows, the sharpest of the three: code written against it assumes a value is present
anything else	a constraint that reads as a guarantee does not hold for the rows that were already there

Extension-owned constraints are excluded. A row with a pg_depend entry of deptype = 'e' against pg_constraint is filtered out in the query itself. Such a constraint is the extension's business and a project cannot act on one, so reporting it would ask somebody to fix what they do not own.

The finding sits on the constraint, not on the table

Findings are deduplicated per rule and location. This one is located on schema.constraint with the object type Constraint, so a table with five unvalidated constraints produces five findings. The table is not lost — it stays in the message and in the remedy, which is where you need it.

It is not part of the identity, though. The location is the schema and the constraint name, so two tables in one schema that carry identically named constraints share it; the last section says what that costs.

What it stays quiet about

A constraint that the deploy being gated is about to validate is skipped.

The check reads the pending migrations it was handed and collects the constraint names targeted by pending ALTER TABLE statements — the validate shape, because ADD CONSTRAINT and DROP CONSTRAINT each carry their own statement kind. A constraint whose name is in that set is not reported.

That suppression is not politeness. A gate that names a problem the very run being gated is about to fix is a gate people learn to click past.

Why it matters

The debt is invisible in normal operation and expensive at exactly one moment: a rewrite of that table.

PostgreSQL validates a NOT VALID constraint as part of a rewrite. So a migration that looked like a quick ALTER turns into a full scan nobody planned for, inside the window — on a table large enough that somebody once chose NOT VALID precisely to avoid scanning it.

The correctness half is slower and worse. A foreign key that everybody believes is enforced is not, and the day somebody relies on it is much later than the day it stopped being true.

What to do about it

Decide first, then act. The decision is whether the constraint is still wanted.

It is wanted → finish it, as its own short step outside the deploy:

ALTER TABLE public.orders VALIDATE CONSTRAINT orders_customer_id_fkey;

This takes a SHARE UPDATE EXCLUSIVE lock — the weaker lock, which is why the step can run outside the deploy window at all. The scan is real, though, so run it when a full scan of that table is affordable rather than in the middle of the window.

If it fails, that is the finding under the finding: existing rows really do violate the constraint. Clean those rows, then validate again.

It is not wanted → drop it. A constraint nobody intends to validate is documentation that lies, and it still lengthens the next rewrite.

Either way, the report goes quiet on the next run — convalidated becomes true, or the row is gone.

Why the command will not do it for you

VALIDATE CONSTRAINT takes a lock and scans the whole table. Running one would be a plain violation of the promise this command makes — it never takes its own locks — and would turn a read-only gate into the longest step of the deploy.

Why medium, deliberately not higher

The debt is real and it is old. A finding that shouted would shout on every run of every project that ever did the safe thing and got interrupted, and a gate that always shouts stops being read.

What makes a specific one urgent is a pending rewrite of that table — which this check does not rank on. Severity is the floor here, not the whole judgment: if the table in the finding is one your next migration rewrites, treat it as the blocker it is.

After the deploy, same id

The same reading is taken again after the deploy, under this same rule id, with an empty pending set — so the suppression above cannot fire, and it must not. If the deploy really did validate the constraint, the catalog now says convalidated = true and nothing is reported. If it still says false, the VALIDATE did not take effect, and that is precisely the finding worth having.

What it does not claim

• It validates nothing. The check reads the catalog and takes no lock of its own. It reports that the scan never happened; it does not perform it.
• convalidated = false is not evidence that rows violate the constraint. It says the scan was never done. The rows may all be fine — nobody has looked, and that is the point.
• It does not tell you which one is urgent. It does not correlate an unvalidated constraint with a pending rewrite of its table, so every finding arrives at the same weight.
• Extension-owned constraints are invisible to it by design, so a quiet verdict is not a statement about constraints your extensions installed.
• The pending-work suppression matches on the constraint name and nothing else. The name is compared bare, because that is what pg_constraint stores and because the statement qualifies the table rather than the constraint. The table is never compared, so a pending VALIDATE CONSTRAINT chk_positive on one table also silences an unvalidated chk_positive on a different one.
• Two identically named constraints in one schema are one finding, not two. The finding's identity is schema.constraint, and constraint names are unique within a table, not within a schema — so if two tables in public each carry an unvalidated chk_positive, the deduplication keeps one of them. Names that carry their table, which is what a migration generator produces, are unaffected.
• A catalog it cannot read is undetermined, never a pass. The result then names its reason: the constraint catalog could not be read, so whether this schema carries constraints that were never validated is unknown — a NOT VALID constraint that nobody finished looks exactly like one that was. The driver's own error message is appended to it.
• It never runs on MySQL. MySQL has no NOT VALID: a foreign key is validated when it is added or it is not added at all, so there is no half-finished state to find. That is an absence of the concept, not a gap in coverage.