Re: Nested transactions - Mailing list pgsql-patches

The other thing that I have been meaning to say in this thread is that I
don't like using COMMIT to mean subtransaction commit (vs. introducing a
new command for it) because of the following situation.

Lets say that I have a java method that takes a jdbc connection and this
  code starts a transaction and does some work then issues a commit to
ensure the changes have been committed to the database, and then does
some other work outside the database based on the fact that the commit
was sucessfull and it therefore knows the data is saved to disk (i.e.
send out an email notification, or any number of other non-database tasks).

Now lets suppose that someone calls this method with a database
connection that already has a transaction started, so that this method
really is beginning and working with a sub-transaction.  Now when it
commits it doesn't know if the changes will ever get to disk since its
commit could be rolled back later.  So this code gets broken.

I like the functionality of nested transactions, I just think that there
needs to be different commands other than BEGIN/COMMIT to work with
them.  So that there is no possiblity for misunderstanding what COMMIT
really means.

thanks,
--Barry


Alvaro Herrera wrote:

> 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.
>