Re: BUG #14025: Unable to validate constraints

On Thursday, March 17, 2016, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> jan(dot)kort(at)genetics(dot)nl <javascript:;> writes:
> > When I do:
> > - disable trigger all
> > - incorrect foreign key reference
> > - enable trigger all
> > - validate
> > Then I get no error, I was expecting an error.
>
> AFAIK, disabling a foreign key trigger is not supported; if you do it,
> we give absolutely zero guarantees about the subsequent behavior of the
> foreign key.
>
> ALTER TABLE VALIDATE CONSTRAINT is not meant to fix that. It is meant
> to finish up a foreign key addition started with
> ALTER TABLE ... ADD FOREIGN KEY ... NOT VALID.
>
> Documented in the relevant sections here.

http://www.postgresql.org/docs/9.5/interactive/sql-altertable.html

We technically support disabling FK (an internal constraint) triggers but
it's intended usage is to bulk load known good data into tables without the
overhead of validation when you already known it is good.

The system as a rule only performs validation on data when newly inserting
it (under MVCC semantics). The few instances of stated re-evaluation
operate on explicitly deferred constructs created using (in this case at
least) NOT VALID. Once an FK has become valid it is never expected to
become invalid again.

While there isn't any particular reason we couldn't validate any named
constraint there is little incentive to make that particular change given
these operating assumptions. And in the rare case you need the ability it
can usually be had in some other way.

David J.