Re: transaction processing after error in statement - Mailing list pgsql-sql

On Mon, 10 Nov 2003, Holger Jakobs wrote:

> Hi Stephan,
>
> On  9 Nov, Stephan Szabo wrote:
> > On Fri, 7 Nov 2003 holger@jakobs.com wrote:
> >
> >> Whenever an error occurs within the transaction, PostgreSQL puts the
> >> whole transaction in an *ABORT* state, so that there is no difference
> >> at all between COMMITing or ROLLBACKing it. Even commands
> >> successfully carried out before the error ocurred are rolled back,
> >> even if I COMMIT the transaction, where no error message whatsoever
> >> is shown.
> >
> > In PostgreSQL all errors are currently considered unrecoverable, and
> > all statements in a transaction must commit or rollback together as a
> > single unit. In the future an implementation of nested transactions or
> > savepoints would presumably relax this limitation to only the
> > successfully committed subtransactions or statements that were not
> > separately rolled back to a previous savepoint.
>
> What I meant was not subtransactions or savepoints, but the funny
> behaviour that operations already successfully carried out never will be
> committed, just because some other operation later within the same
> transaction fails. This is far different from the behaviour of all other
> DMBS I know. Why not:
>
>    begin work;
>    insert into x values (1, 'hi');
>    --> success
>    insert into x values (1, 'there');
>    --> failure due to primary key violation
>    insert into x values (2, 'foo');
>    --> success
>    commit work;
>
> and have two new tuples in the table? Why do _all_ of these operations
> have to be rolled back? I just don't get it that this has anything to do

Right now there's a technical reason (the system won't handle partially
completed statements reasonably - but nested transactions will give a way
for that to work presumably hence their mention) and a logical reason
(many of the developers seem believe that partial commit isn't a good
behavior and that transactions should be all commit or all fail).
Personally, I think it'd be nice to have some way to deal with errors
other than retrying the whole sequence, but at the mean time we have a
system which basically meets the words if not the intent of the spec.

> Hopefully this can be cleared and perhaps improved within PostgreSQL.
> Otherwise, PostgreSQL always claims to be close to standards.

This is actually fairly standard complient. Note my message about errors
being unrecoverable. The SQL spec allows an entire transaction to be
rolled back upon unrecoverable errors. Our failed state behavior may not
be (if we sent an error on commit, I believe it would be), but forcing the
entire transaction to roll back is.
That may not be what the spec intended, but it does seem to be allowed.