BUG #14560: FK not valid are ignored during transactions

From: amos_operation(at)swiss-as(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14560: FK not valid are ignored during transactions
Date: 2017-02-21 12:35:42
Message-ID: 20170221123542.1268.85944@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14560
Logged by: AMOS Operation
Email address: amos_operation(at)swiss-as(dot)com
PostgreSQL version: 9.5.5
Operating system: Linux
Description:

Hello,

With a "not valid" FK, I can run single updates without error. But running
the same updates inside a transaction does not work.

Example:
create table source (id int, fk int, name character varying(10));
create table target (id int, name character varying(10));
alter table source add constraint pk_source primary key (id);
alter table target add constraint pk_target primary key (id);

insert into source values (1,99,'test');

alter table source add constraint fk_test foreign key (fk) references
target(id) not valid;

update source set name = 'twice' where id = 1; => works
update source set name = 'other' where id = 1; => works

begin;
update source set name = 'unknow2' where id = 4;
update source set name = 'unknow3' where id = 4;
commit; => does not work:
ERROR: insert or update on table "source" violates foreign key constraint
"fk_test"
DETAIL: Key (fk)=(99) is not present in table "target".

Thanks !
Olivier

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message mjsbeaton 2017-02-21 13:20:20 BUG #14561: Unexpected disk buffer in FETCH handling
Previous Message sujitm 2017-02-21 06:50:07 BUG #14559: Error message of too many clients.