To your actual question:
Does it matter if a constraint is not marked as valid forever?
Not for running the database. The only effect is that Postgres sets the flag pg_constraint.convalidated
for the constraint to false
. Then it treats the FK constraint like any other, validating any newly written data. Just, if you later run VALIDATE CONSTRAINT
- that is:
ALTER TABLE tbl VALIDATE CONSTRAINT tbl_col_fkey;
... referential integrity is only actually validated if it was marked NOT VALID
before. And then it's marked valid.
A quick check on the source code revealed no effects, either, as expected.
If you are absolutely positive that referential integrity is intact, you could theoretically exploit that and set the flag in the system catalog manually as superuser:
UPDATE pg_constraint
SET convalidated = true
WHERE conrelid = 'public.tbl'::regclass
AND conname = 'tbl_col_fkey';
But I wouldn't do that if you point a gun to my head.
Firstly, you should never mess with system catalogs to begin with. Stick to well-documented DDL commands to manipulate system catalogs or you may break your database (cluster).
Secondly, you should never make assumptions like that and rather put in the work to be sure.
Thirdly, while my answer may be correct for the current version of Postgres, that may change for the next. We are in undocumented territory here.
And finally, if you really can't get around to properly validate the constraint, just keep operating with the NOT VALID
version. Everything works just fine.
Related blog article: