Do I need to VALIDATE CONSTRAINT?

I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.

From the documentation:

But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

It doesn't mention the consequences – not even in the notes –, if there are any at all.

Does it matter if a constraint is not marked as valid forever?

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:

• https://www.dbi-services.com/blog/enabling-disabling-and-validating-foreign-key-constraints-in-postgresql/

StackExchange Database Administrators Q#279960, answer score: 7