Re: Error handling in plperl and pltcl

Tom Lane wrote:
> Wrapping each individual SPI command in a subtransaction IN NO WAY
> prevents us from adding programmer-controllable savepoint features
> to the PL languages later.

Ah good - I was coming to the conclusion savepoints/exception handling
were both separately necessary.

> It simply ensures that we have somewhat
> sane error recovery behavior in the meantime. The only valid argument
> against doing it is the one of added overhead, and I already gave my
> responses to that one.

The bit I still don't get is how the subtrans-per-spi gets us try/catch
functionality.

INSERT 1
INSERT 2
try {
INSERT 3
INSERT 4
}
catch WHATEVER {
INSERT 5
INSERT 6
}

So - here we (well I) would expect to see 1,2,3,4 or 1,2,5,6. That means
if #4 fails we need to rollback to a savepoint before #3. But the
problem is that we don't know whether we are in the try block, otherwise
we'd just start a savepoint there and sidestep the whole issue.

That means the only safe action is to rollback the transaction. We can't
even just write to a log table and raise our own exception, since the
calling function then won't know what to do.

I'm worried that non-intuitive behaviour here is strapping the gun to
our foot. It's going to introduce peculiarities in code-paths that are
likely to go untested until it's too late.

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.

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.

3. We can do something similar with a pgeval() in plperl. Don't know
enough to say about Python.

Basically, if exception handling doesn't work the way it should
intuitively work (IMHO plpgsql's model) then I'd rather wait until 8.1
--
Richard Huxton
Archonet Ltd