Denis Sbragion (d.sbragion@infotecna.it) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
Referential integrity problem
Long Description
It looks like performing operation in two steps on tables with multiple deferred referential integrity constraints
betweenthem is not allowed. Not sure this is a bug, may be it is required by the standards, but sure it is rater
counterintuitive(may be, of course, I'm completely wrong). Most of the times this is just annoying, sometimes it
doesn'tlet you do updates, i.e. whenever you cannot perform the update in a single step (I found at least one case).
Testedboth on 7.0.3 and 7.1.2, with same results.
P.S. Great job guys. 7.0.3 was very good, but 7.1.2 is simply wonderful.
Sample Code
Here it is an oversimplyfied example:
create table tablea
(
fielda integer,
fieldb varchar(128),
constraint tablea_pkey primary key (fielda)
);
create table tableb
(
fielda integer,
fieldb integer,
fieldc varchar(128),
fieldd integer,
constraint tableb_pkey primary key (fielda, fieldb),
constraint tablea_to_tableb foreign key (fielda) references
tablea (fielda) on delete restrict on update restrict
deferrable initially deferred,
constraint scndtablea_to_tableb foreign key (fieldd) references
tablea (fielda) on delete restrict on update restrict
deferrable initially deferred
);
insert into tablea (fielda, fieldb) values (1, 'Key 1');
insert into tableb (fielda, fieldb, fieldc, fieldd) values (1, 1, 'Ref to Key 1', 1);
Performing:
begin;
update tableb set fielda = 2, fieldd = 2 where fielda = 1;
update tablea set fielda = 2 where fielda = 1;
commit;
it's ok as it should. If you do the same thing above (now reversed, of course) in two steps, i.e:
begin;
update tableb set fielda = 1 where fielda = 2;
update tableb set fieldd = 1 where fieldd = 2;
update tablea set fielda = 1 where fielda = 2;
commit;
gives:
ERROR: scndtablea_to_tableb referential integrity violation - key referenced from tableb not found in tablea
I hope it's clear.
No file was uploaded with this report
