Re: Functions, savepoints, autocommit = I am confused !

Tim Smith wrote on Friday, June 26, 2015 5:38 PM:
> ERROR: cannot begin/end transactions in PL/pgSQL
> HINT: Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT: PL/pgSQL function
> app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint)
> line 16 at SQL statement

> Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"

I believe I've read you can have nested BEGIN ... END blocks, and the transaction control is done implicitly by the PL/pgSQL exception handling, so you probably can write

BEGIN
...
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
...
END
END;

which would (hopefully) only roll back the second ... not the first ... (not sure if you still need to declare the savepoint, at least, as you found out, explicitly rolling back to the savepoint is not allowed in PL/pgSQL). Note that the third ... probably should not raise or re-raise an exception, otherwise you have an exception in the outer BEGIN-END block and everything is rolled back.

Best regards
Holger Friedrich