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
