Date: Fri, 7 Nov 2003 23:17:51 +0100 (CET)
From: holger@jakobs.com
Subject: transaction processing after error in statement
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: TEXT/plain; charset=us-ascii
Message-Id: <20031107221659.55CBC3CA93@smtp1.netcologne.de>

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