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

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