Thread: BUG #14025: Unable to validate constraints

The following bug has been logged on the website:

Bug reference:      14025
Logged by:          Jan
Email address:      jan.kort@genetics.nl
PostgreSQL version: 9.4.1
Operating system:   Windows 2012 Server
Description:

When I do:
- disable trigger all
- incorrect foreign key reference
- enable trigger all
- validate
Then I get no error, I was expecting an error.

For example:

-- This succeeds (expected)
drop table if exists test1;
drop table if exists test2;
create table test2 (id int primary key);
create table test1 (id int primary key, test2_id int references test2(id));
insert into test2 (id) values (1);
insert into test1 (id, test2_id) values (1, 1);

-- This fails (expected)
drop table if exists test1;
drop table if exists test2;
create table test2 (id int primary key);
create table test1 (id int primary key, test2_id int references test2(id));
alter table test1 disable trigger user;
insert into test1 (id, test2_id) values (1, 1);
alter table test1 enable trigger user;

-- This succeeds (unexpected)
drop table if exists test1;
drop table if exists test2;
create table test2 (id int primary key);
create table test1 (id int primary key, test2_id int references test2(id));
alter table test1 disable trigger all;
insert into test1 (id, test2_id) values (1, 1);
alter table test1 enable trigger all;
alter table test1 validate constraint test1_test2_id_fkey;

Regards,

Jan

jan.kort@genetics.nl 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.

            regards, tom lane

On Thursday, March 17, 2016, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> jan.kort@genetics.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.