Re: transaction processing after error in statement

Hi Stephan,

On 9 Nov, Stephan Szabo wrote:
> On Fri, 7 Nov 2003 holger(at)jakobs(dot)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
with savepoints or so. I don't see any problem with an error being
recoverable, because the second insert failed and does not have to be
recovered while the first and the third worked fine and does not have to
be recovered either. When committing a transaction the effects of all
operations that did not fail will be made permanent. This is how
transaction processing is described in the literature.

If a programmer wants the whole transaction to fail because one part
failed, (s)he can always program a rollback in case of at least one
error. But there should always be a difference between a rollback and a
commit, after at least one statement changing data has reported a
success.

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

Sincerely,

Holger
--
Holger(at)Jakobs(dot)com, Bergisch Gladbach, Germany
Telefon (0 22 02) 5 99 91 oder (01 77) 7 92 24 66