Richard Huxton wrote:
> Tom Lane wrote:
>
>> The real point here is that omitting the per-command subtransaction
>> ought to be a hidden optimization, not something that intrudes to the
>> point of having unclean semantics when we can't do it.
>
>
> Sorry to be stupid here, but I didn't understand this when it was
> disussed originally either. Why a subtransaction per command rather
> than one per function? If I've got this right, this is so the PL can
> tidy up behind itself and report/log an appropriate error?
I don't understand this either. Why a subtransaction at all?
Don't get me wrong. I fully understand that a subtransaction would make
error recovery possible. What I try to say is that the kind of error
recovery that needs a subtransaction is fairly, or perhaps even very, rare.
We all agree that further calls to SPI must be prohibited if an error
occurs when no subtransaction is active. Such an error can only result
in one thing. The function must terminate and the error must be propagated.
The way most functions that I've seen is written, this is the most
common behavior anyway. It's very uncommon that you want to do further
database accesses after something has gone wrong. I admit that some
special cases indeed do exist but I cannot for my life understand why
those cases must incur a 25% overhead on everything else. Especially if
there is an alternate way of handling them without making any sacrifice
whatsoever on safety.
A function in PL/Java that calls to the backend and encounters an error
can be 1 of 2 types:
1. If no subtransaction is active, the function will be completely and
utterly blocked from doing further calls to the backend. When it
returns, the error will be re-thrown.
2. When a subtransaction is active, the function will be blocked the
same way as for #1 with one exception. A subtransaction rollback will go
through and it will remove the block.
So, in Java I have the choice of writing:
try { // do something } catch(SQLException e) { // Clean up (but no backend calls) and terminate }
or I can write:
Savepoint sp = myConn->setSavepoint("foo"); try { // do something sp.commit(); } catch(SQLException e) {
sp.rollback();
// Handle error and continue execution. }
All cases are covered, there's no subtransaction overhead (unless you
really want it), the semantics are clean, and it's 100% safe. What's
wrong with this approach?
Regards,
Thomas Hallgren
