Re: Nested xacts: looking for testers and review

On Thu, 10 Jun 2004, Alvaro Herrera wrote:

> On Wed, Jun 09, 2004 at 11:32:08PM -0700, Stephan Szabo wrote:
>
> > > Unfortunately, I've gotten it to fail, but I haven't looked in depth (I'm
> > > at work, so I'm doing it during compilations and such.)
>
> [...]
>
> > Okay - I think I see what's going on here.
> >
> > It looks like deferredTriggerInvokeEvents is being run (immediate_only),
> > but since deferredTriggers->events_imm is NULL it's using
> > deferredTriggers->events as the start of the list to check, but this value
> > isn't getting reset in DeferredTriggerEndSubXact in the case that the
> > entire list was created in an aborted subtransaction.
>
> Ok, thanks for the test and diagnostics; patch attached.  I'll see if I
> can find other situations like this.

As a question, what was the general assumption about what the following
should do (using a modification of the original test case)?

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;
 
-- move this set constraints down below
--        SET CONSTRAINTS ALL DEFERRED;       BEGIN;               INSERT INTO bar VALUES (5); --(1)       COMMIT;
SET CONSTRAINTS ALL DEFERRED;       BEGIN;               BEGIN;                       INSERT INTO bar VALUES (6);
       ROLLBACK;       COMMIT;       BEGIN;               INSERT INTO bar VALUES (7);       COMMIT;       BEGIN;
      BEGIN;                       INSERT INTO bar VALUES (9);               COMMIT;       COMMIT;       INSERT INTO
fooVALUES(3);       INSERT INTO foo VALUES(5);       INSERT INTO foo VALUES(7);       INSERT INTO foo VALUES(9);
 
COMMIT;


Should the statement at (1) fail because the constraint is not immediately
satisfied, or should it pass because the set constraints all immediate was
part of a subtransaction that didn't commit?