On 12/1/2004 9:23 AM, Jan Wieck wrote:
On 12/1/2004 4:27 AM, Richard Huxton wrote:
Thomas Hallgren wrote:Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing here afaict) in a sub-transaction. An exception can be caught within that function, and all the spi in that function is then rolled back. This is rubbish, but at least it's predictable and allows you to write to a log table and throw another exception.
This will be even worse since it will impose the subtransaction overhead on everything, even functions that never do any database access. Perhaps this approach would be feasible if imposed on volatile functions only, but then again, the volatility of a function cannot be trusted since we have no way of defining a "stable but with side effects" type.
Actually, I was thinking of setting a flag and then on the first SPI call start the subtrans.
2. For pl/tcl introduce a pgtry { } catch { } which just starts a sub-transaction and does standard try/catch. I don't use TCL, but from the little I know this should be straightforward.
If you know how to use special constructs like this, what's wrong with actually using savepoints verbatim? I.e.
INSERT 1 INSERT 2 SAVEPOINT foo try { INSERT 3 INSERT 4 RELEASE foo } catch WHATEVER { ROLLBACK TO foo INSERT 5 INSERT 6 }
IMHO a very clean, sensible, and easily understood approach that doesn't clobber the language.
But is the problem not that forgetting to use SAVEPOINT can get us in trouble with clearing up after an exception? That's the main thrust of Tom's per-statement stuff AFAICT. And again, you're not going to see the problem until an exception is thrown.
I think the following would a) be a drop in replacement without any side effects or performance impact for PL/Tcl functions not using "catch" and b) give "catch" a sensible and correct behaviour.
One can _replace_ the Tcl catch command with his own C function. This can be done during the interpreter initialization when loading the PL/Tcl module. The new catch would
push a status NEED_SUBTRANS onto a stack call Tcl_Eval() for the first command argument if TCL_ERROR { pop status from stack if popped status == HAVE_SUBTRANS { rollback subtransaction } if a second argument exists { store interpreter result in variable } return TCL_ERROR
er ... no ... must return a true boolean with TCL_OK here
} pop status from stack if popped status == HAVE_SUBTRANS { commit subtransaction }
return result code from Tcl_Eval()
and here it must put a false boolean into the Tcl result ... not 100% sure about the result code. Must check if it's possible to return or break from inside a catch block ... if not, then catch allways turns the internal result code into TCL_OK. Anyhow, you get the idea.
Jan
The spi functions check if the top stack entry (if there is one) is NEED_SUBTRANS. If so, they start a subtrans and change the status to HAVE_SUBTRANS.
This all would mean that however deeply nested a function call tree, it would unwind and rollback everything up to the outermost catch. If there is no catch used, no subtransactions are created and the unwinding goes all the way up to the statement. If catch is used but no spi access performed inside, no subtransaction overhead either.
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
