Re: Weird behavior in transaction handling (Possible bug ?)

[Oliver Jowett <oliver(at)opencloud(dot)com>]

j.random.programmer wrote:

> Here is the problem. The commit() will NEVER work and
> no data is ever saved to any table in the database.
> No error message is generated, the commit() SILENTLY
> fails to insert any data.
>
> However, if I comment out the second insert into table
> #2
> (which was causing an error), then the inserts work
> and the transaction is committed().

When postgresql hits an error, the transaction is marked for rollback 
and all subsequent queries in that transaction will fail. A subsequent 
COMMIT will not actually commit; it will roll back. There are arguments 
both ways about whether this is a good idea (mostly correctness vs. 
compatibility with other systems), but that's the way it is and the way 
it has been for ages. Don't ignore errors from your queries!

...

It might be worthwhile having commit() throw an exception if the 
transaction did not actually commit, rather than only reporting 
server-generated errors. What do people think?

Pre-7.4 returns a COMMIT status for any COMMIT even if the transaction 
actually rolled back, and the v2 protocol has no mechanism to detect 
transactions that have failed. So the only way to detect this would be 
to track transaction state internally -- seems a bit ugly and unreliable.

7.4 returns COMMIT for rolled-back COMMITs, but does report transactions 
that have failed via the v3 protocol. 8.0 returns ROLLBACK for 
rolled-back COMMITs and also uses the v3 protocol. So it should be 
possible to detect this case for both 7.4 and 8.0 reasonably easily.

...

Also in 8.0 and later, there is savepoint support that helps with this 
case. The pattern to use is something like this:

  establish savepoint
  INSERT ....;
  if insert caused an error:
    rollback to savepoint
  else:
    release savepoint

See java.sql.Savepoint, and the Postgres docs on SAVEPOINT for more info.

That pattern will cause a subtransaction to be started for the INSERT. 
If the INSERT fails, and we ROLLBACK TO SAVEPOINT, then all the results 
of the INSERT (including the marking-txn-for-rollback) are discarded and 
your original transaction can continue.

There is a performance cost when using savepoints, but I don't know how 
large.

It'd be possible to have optional "automatic savepoint wrapping" in the 
driver, where every user query was transparently wrapped in 
subtransaction. You might prefer to write the code to make the driver do 
this, rather than change your application.

-O