Re: Two features left - Mailing list pgsql-general

Ok, so it looks like your nested transactions and savepoints are really the
same thing.  The question is, are you going to change the way SQL exceptions
are handled so that simply abort that SQL statement don't require a rollback?
With your enhancement, it sounds like calling BEGIN before each SQL statement
could acheive what I am asking for, but the issue is existing applications
will not expect to have to do so.

On Wednesday 27 November 2002 01:16 pm, Bruce Momjian wrote:
> Jon Swinth wrote:
> > Maybe what you are talking about will not help.  The question is are you
> > trying to make nested transactions or savepoints?
> >
> > Nested transactions would be useful for trying to interrupt a transaction
> > and have another action happen or not happen on it's own.  An example
> > would be when you want a credit card transaction to generate a log
> > reguardless of whether the out transaction is commited or rolled back.
> > The problem with
>
> Not with my implementation:
> > >     BEGIN;
> > >     SELECT ...
> > >     BEGIN;
> > >     UPDATE ...
> > >     ABORT;
> > >     DELETE ...
> > >     COMMIT;
>
> In the above case, the ABORT cancels the UPDATE.  If the outer
> transaction ABORTS, everything aborts.  Even if you commit a
> subtransaction, _all_ transactions above it must commit for the
> subtransaction to actually commit.
>
> If you want a log entry regardless of the transaction, put it in a
> separate transaction.
>
> > nested transactions is that it is easy to generate deadlocks, especially
> > with the write locks currently on foreign keys.
>
> Again, it isn't really any different from a transaction without
> subtransactions except certain parts of the entire transaction can be
> aborted.
>
> > What may help is the concept of savepoint (if implemented internally).
> > Savepoints are usually named and allow rollback to a specific point in
> > the transaction.  There is no issue with deadlock since everything is
> > still in the same transaction.  You then don't have to have something
> > call ABORT, you simple need to say ROLLBACK TO <savepoint_name>.
> >
> > BEGIN;
> > SELECT...
> > INSERT...
> > SAVEPOINT a ;
> > UPDATE...
> > ROLLBACK TO a ;
> > DELETE...
> > COMMIT;
>
> Right.  It is no change in functionality to add savepoints because we
> can just do a named BEGIN internally as the SAVEPOINT, then do ABORT
> back until we match the nesting level of the savepoint.