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.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
