Re: Nested xacts: looking for testers and review

Alvaro Herrera wrote:
> On Fri, May 28, 2004 at 04:51:07PM +0900, Tatsuo Ishii wrote:
>
> > 2) certain behavior was different from what I expected (please correct me
> > if my expectation is wrong).
>
> Yes, the expected behavior is different: if an aborted subtransaction is
> closed with a COMMIT or END command, the parent transaction is aborted
> too. This is to inhibit an application which blindly expects the
> subtransaction to succeed to reach an invalid state. If you want to
> return to non-aborted state, end the subtransaction with ROLLBACK
> instead.

I am interested to know if people agree with this behavior, reproduced below:

test=# begin;
BEGIN
test=# insert into t1 values(1);
INSERT 17216 1
test=# begin;
BEGIN
test=# aaa;
ERROR: syntax error at or near "aaa" at character 1
ERROR: syntax error at or near "aaa" at character 1
LINE 1: aaa;
^
test=# end;
COMMIT
test=# select * from t1; <-- I thought this should work since
subtransaction was closed
ERROR: current transaction is aborted, commands ignored until end of
transaction block
ERROR: current transaction is aborted, commands ignored until end of
transaction block
test=# end;
COMMIT

The problem I see with the behavior shown is that there is no way to use
subtransactions in scripts, where the queries can't be changed.
Consider this:

BEGIN;
DO SOME WORK...
BEGIN;
INSERT ...
COMMIT;
BEGIN;
INSERT ...
COMMIT;
BEGIN;
INSERT ...
COMMIT;
BEGIN;
INSERT ...
COMMIT;
DO MORE WORK...
COMMIT;

In this case, I want to try all of the inserts, but any of them can
fail, then I want the bottom part done.

I guess my big question is that if they issue a COMMIT for a
subtransaction that failed, do we assume they made a mistake and fail
the outer transaction, or do we just accept it and not affect the outer
transaction.

In my logic, the subtransaction COMMIT is part of the subtransaction and
should not affect the outer transaction's state.

Unfortunately, we don't have any similar behavior in our 7.4 code
because whether you issue COMMIT or ABORT, it does not affect the outer
session.

Do any other databases have nested transactions, and how to they handle
it?

I think we should issue a warning but not affect the outer transaction.

> But Bob Henkel and you are right: there needs to be documentation.
> Initially I figured I could do that later because there is no new
> syntax, but it is obviously needed to explain all sorts of assumptions
> and behavior like this.

Right now I think just posting examples will work fine. I think the
above case shows we are not ready for documentation yet. What I would
like is for folks to focus on testing so we can find any open issues
like this one and address them.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073