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

[snip]
> > >
> > But the error is postgresql specific. There is no
> > error, only
> > a insert within a transaction failed. Why should that
> > affect
> > the rest of the transaction, if *I* as the user don't
> > want it to?
> >
> >
> How does postgres know which parts you don't care about ?
>

Dave, this argument goes over and over again on the postgres lists, but
basically the answer is simple: if I want to roll back, than I will
issue a roll-back command on catching the error, and if I want to commit
the previously successful part, I issue commit.
So postgres does not need to do any black magic, just commit if I say
commit, and roll back only if I say so. This is how it works in Oracle &
co, and it is very useful where an error will only make the transaction
take a different path instead of failing.
The banking example is simple: if I catch an error, I will roll back
instead of commit. In any case, the choice is mine and the server should
not decide for me.

Now it's a different problem that postgres has no support for this, and
I understand that, I just don't understand the uproar in the developers
corner each time somebody tries to explain that the above feature is not
against the transaction atomicity principle but it gives more choice to
the programmer. It is an interpretation issue about what is a
transaction: all the executed SQLs, or just the set of successful SQLs ?
I don't think the specs have anything clear to say about this, and then
it's a subjective matter, and both parties should accept that the other
version is also valid, instead of bashing the other.

Now that savepoints were implemented, it is possible in postgres too to
design a transaction with different SUCCESS path on an error. It was not
possible before other than retry the whole transaction again. However,
it still needs special code for postgres compared to Oracle ( I can't
compare with others as I only used Oracle).

So I would also vote to make it an optional feature of the postgres JDBC
driver to automatically wrap each statement in a savepoint, even if it
does have a performance hit. The default behavior should be no wrapping,
but it would help a lot with rapidly porting from Oracle.

Cheers,
Csaba