Re: Sketch of extending error handling for subtransactions

> I was just looking around the net to see exactly what Oracle's PL/SQL
> syntax is. It doesn't seem too unreasonable syntax-wise:
>
> BEGIN
> ... controlled statements ...
> EXCEPTION
> WHEN exception_name THEN
> ... error handling statements ...
> WHEN exception_name THEN
> ... error handling statements ...
> ...
> WHEN OTHERS THEN
> ... error handling statements ...
> END;
>
> There's nothing here we couldn't do. However, it seems that Oracle
> thinks you should throw in explicit SAVEPOINT and ROLLBACK statements
> on top of this! That's just weird. It might be that we should
> deliberately *not* adopt the exact syntax they are using, just so we
> don't create compatibility gotchas.

That is because they usually use this to handle the exception of only one
potentially failing statement, which does not rollback any prev statements
(except in pg).
Thus in Oracle you need savepoints in a lot fewer cases. It is only in those seldom
cases, that you add savepoints on top of blocks with exceptions in Oracle.

But yes, I think doing implicit savepoints for plpgsql blocks that contain an
"exception ..." handler is absolutely the way to proceed. For maximum protability
that savepoint should probably travel along with every successful statement after
the BEGIN (that of course is debateable).

BEGIN
--implicit savepoint "x"
update 1 -- succeeds
--implicit release old "x", new savepoint "x"
update 2 -- fails
--position "y"
update 3 -- succeeds
EXCEPTION -- rollback to savepoint "x"
WHEN ....
-- transfer control to position "y"
END; -- implicit RELEASE savepoint "x"

Doing this only for blocks with an exception handler would not impose any overhead
for conventional plpgsql funcs.

Andreas

PS: can someone please help me get through the lists spam blocker, get Marc to contact me,
or I don't know what else I can do