Re: Nested transactions

On Wed, Jun 16, 2004 at 11:45:36PM +0100, Simon Riggs wrote:

> The patch looks impressively technical, but overall I'm not exactly sure
> what it does...I guess I'm just not clear why I would want it, except as
> the main technical pre-work to later syntax changes. I'm sure some short
> explanations would clear that up for me very quickly... :)

Right. I have never intended to be implementing a known SQL standard
feature. What I'm doing is allowing the whole backend to go back to a
know state after an error is encountered.

With this in place, implementing SAVEPOINTs the way SQL expects them to
work appears to be a very trivial exercise.

> Perhaps what I've just asked about is trivial icing on the cake you've
> just baked,

I think this phrase very precisely describes it. At least, that's what
I expect.

You may not see it, but a savepoint is just the start of a nested
transaction in disguise. Consider:

begin;
insert into foo values (1);
savepoint dammit;
insert into foo values (2);
select foo; -- fails
rollback to dammit;
insert into foo values (3);
commit;

You expect the transaction to finish with tuples 1 and 3 in table foo,
right? Well, this is exactly the same as

begin;
insert into foo values (1);
begin; -- dammit
insert into foo values (2);
select foo; -- fails, goes to aborted state
rollback;
insert into foo values (3);
commit;

So all that's needed for the former to work is to be able to define a
"name" for a transaction (using a cute syntax) and being able to
rollback to it. Definitely trivial, after all the work I have put into
making the latter work.

In extant releases you can only do this:
begin;
insert into foo values (1);
insert into foo values (2);
select foo; -- oops, can't go back!
rollback;
begin;
insert into foo values (1);
insert into foo values (3);
commit;

You are forced to send all the commands before the aborting one to the
server again. And there's no way to "undo" a command in the
transaction, short of aborting it completely.

I don't know what Oracle or other DBMSs expect in this area. Anyone
care to give me a few pointers? If I'm missing something, I want to
know as soon as possible.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers