From: Jan Wieck <JanWieck@Yahoo.com>
To: Holger Jakobs <holger@jakobs.com>
Cc: sszabo@megazone.bigpanda.com
Cc: pgsql-sql@postgresql.org
Subject: Re: transaction processing after error in statement
Date: Mon, 10 Nov 2003 13:22:14 -0500
Message-ID: <3FAFD756.2000704@Yahoo.com> (raw)
In-Reply-To: <200311101237.hAACbuJ12784@bg.bib.de>
References: <200311101237.hAACbuJ12784@bg.bib.de>
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:
Why is that "funny behaviour" for you? By putting the statements into a
transaction block you told the data management system "I want this group
of statements to be atomic". Atomic means all or nothing. It might not
be exactly what you intended to say, and you have a point if you
conclude that PostgreSQL is limited because it doesn't let you say
anything in between all or nothing. But after all, thus far it is only
doing what you asked for.
Jan
>
> 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
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: JanWieck@Yahoo.com, holger@jakobs.com, sszabo@megazone.bigpanda.com
Subject: Re: transaction processing after error in statement
In-Reply-To: <3FAFD756.2000704@Yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox