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 foo VALUES(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?