Thread: Problem (bug?) with deferred foreign key checks?
Yo!
Why is it not possible to temporarily have illegal values in a table,
and update them during the transaction to point to real entries in the
'super' table?
=====
avbidder=> create table super (id integer unique);
NOTICE: [...]
CREATE
avbidder=> create table sub (sid integer references super(id) deferrable
initially deferred);
NOTICE: [...]
CREATE
avbidder=> begin;
BEGIN
avbidder=> insert into sub values (2);
INSERT 53513 1
avbidder=> insert into super values (1);
INSERT 53514 1
avbidder=> update sub set sid = 1 where sid = 2;
UPDATE 1
avbidder=> commit;
ERROR: <unnamed> referential integrity violation - key referenced from
sub not found in super
=====
(This is Debian package 7.1.3-7, but I'm told it's there with other
versions, too)
cheers
-- vbi
--
secure email with gpg avbidder@fortytwo.ch: key id 0x92082481
avbidder@acter.ch: key id 0x5E4B731F
Attachment
I seem to recall that there is something in 7.1.x where you cannot update the same row more than once per transaction. Consider altering your code to not require that or upgrading to 7.2 where that is supposed to be allowed. Joshua b. Jore ; http://www.greentechnologist.org ; 1121 1233 1311 200 1201 1302 1211 200 1201 1303 200 1300 1233 1313 1211 1302 1212 1311 1230 200 1201 1303 200 1321 1233 1311 1302 200 1211 1232 1211 1231 1321 200 1310 1220 1221 1232 1223 1303 200 1321 1233 1311 200 1201 1302 1211 232 200 1112 1233 1310 1211 200 1013 1302 1211 1211 1232 201 22 On 10 Jun 2002, Adrian 'Dagurashibanipal' von Bidder wrote: > Yo! > > Why is it not possible to temporarily have illegal values in a table, > and update them during the transaction to point to real entries in the > 'super' table? > > ===== > avbidder=> create table super (id integer unique); > NOTICE: [...] > CREATE > avbidder=> create table sub (sid integer references super(id) deferrable > initially deferred); > NOTICE: [...] > CREATE > avbidder=> begin; > BEGIN > avbidder=> insert into sub values (2); > INSERT 53513 1 > avbidder=> insert into super values (1); > INSERT 53514 1 > avbidder=> update sub set sid = 1 where sid = 2; > UPDATE 1 > avbidder=> commit; > ERROR: <unnamed> referential integrity violation - key referenced from > sub not found in super > ===== > > (This is Debian package 7.1.3-7, but I'm told it's there with other > versions, too) > > cheers > -- vbi > > > -- > secure email with gpg avbidder@fortytwo.ch: key id 0x92082481 > avbidder@acter.ch: key id 0x5E4B731F > >
On Mon, 2002-06-10 at 14:52, Joshua b. Jore wrote: > I seem to recall that there is something in 7.1.x where you cannot update > the same row more than once per transaction. Consider altering your code > to not require that or upgrading to 7.2 where that is supposed to be > allowed. 1. It also doesn't work with only 1 upgrade. 2. It doesn't work on 7.2 as well. Diana Senn
On 10 Jun 2002, Adrian 'Dagurashibanipal' von Bidder wrote: > Why is it not possible to temporarily have illegal values in a table, > and update them during the transaction to point to real entries in the > 'super' table? This was a bug due to not noticing that a row was invalidated. It's partially fixed in current sources (the below works for me). Someone asked about this bug recently and got the patch (I missed a few things when doing it so you have to be careful). If you go through the archives you should find it. The related patch (for cases if you were to have updated super away and made a new super row for the value) isn't completed yet, but there's a temporary one.