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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
