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.



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to