From: holger@jakobs.com
To: pgsql-sql@postgresql.org
Subject: transaction processing after error in statement
Date: Fri, 7 Nov 2003 23:17:51 +0100 (CET)
Message-ID: <20031107221659.55CBC3CA93@smtp1.netcologne.de> (raw)
Dear PostgreSQL Gurus,
I have been using PostgreSQL for quite a while and always relied on its
handling of transaction and concurrency. But recently I discovered a
behaviour that deviates quite a lot from how I understand transactions
and how things are handled by other databases: HP Allbase, Oracle 8 and
.mdb-files (Access).
Here's the description:
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.
Example:
begin;
insert into table1 values (1, 'hello');
--> success!
select no from table1;
ERROR: Attribute 'no' not found
commit;
--> success!
Why should the insert statement fail, just because there was a typo in
the following select statement? I was already carried out successfully,
albeit only visible to the current transaction.
I found this behaviour to be the same across all 7.x versions of
PostgreSQL.
Unfortunately, I haven't been able to find an explanation why PostgreSQL
behaves like this and why all other RDBMS I tried behave differently. In
this case the others make more sense to me.
Additionally, I have discovered that phantom reads occur in PostgreSQL
even if isolation mode serializable is used. Also not so nice!
Sincerely,
Holger
--
Holger Jakobs * D-51469 Bergisch Gladbach
Telefon +49-2202-59991 * Mobilfon +49-177-792-2466
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: holger@jakobs.com
Subject: Re: transaction processing after error in statement
In-Reply-To: <20031107221659.55CBC3CA93@smtp1.netcologne.de>
* 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