On Sun, May 30, 2004 at 04:07:27AM -0700, Stephan Szabo wrote:
> On Sat, 29 May 2004, Alvaro Herrera wrote:
> > Ah, this seems to work. I'll implement it and I'll let you know how it
> > goes.
>
> Ugh... There's one further wrinkle I hadn't thought about, imagine the
> following:
Ok Stephan, thank you very much for your help. I implemented this and
it seems to work, at least on my (limited?) test scenario. If you have
some spare time I'd like you to test it and see if you can break it (I
posted it to -patches yesterday).
Or please see my test case below. Is it missing something? Note that
if I take out any of the four inserts just before the end of the
transaction, the whole thing is rejected.
DROP TABLE foo CASCADE;
DROP TABLE bar CASCADE;
CREATE TABLE foo (A INT UNIQUE);
CREATE TABLE bar (A INT REFERENCES foo(A) DEFERRABLE);
DELETE FROM bar;
DELETE FROM foo;
INSERT INTO foo VALUES (1);
INSERT INTO foo VALUES (2);
BEGIN;SET CONSTRAINTS ALL DEFERRED;INSERT INTO bar VALUES (1);BEGIN; INSERT INTO bar VALUES (3);COMMIT;BEGIN;
BEGIN; INSERT INTO bar VALUES (4); COMMIT; INSERT INTO foo VALUES (3); SET CONSTRAINTS ALL
IMMEDIATE;ROLLBACK;SETCONSTRAINTS ALL DEFERRED;BEGIN; INSERT INTO bar VALUES (5);COMMIT;BEGIN; BEGIN;
INSERTINTO bar VALUES (6); ROLLBACK;COMMIT;BEGIN; INSERT INTO bar VALUES (7);COMMIT;BEGIN; BEGIN;
INSERTINTO bar VALUES (9); COMMIT;COMMIT;INSERT INTO foo VALUES(3);INSERT INTO foo VALUES(5);INSERT INTO foo
VALUES(7);INSERTINTO foo VALUES(9);
COMMIT;
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Acepta los honores y aplausos y perderás tu libertad"