> On Mar 22, 2025, at 21:37, Kevin Stephenson <kjs...@hotmail.com> wrote:
>
> Christophe and Tom, thank you for your responses, but I'm still a bit
> confused. In my original email, the Test 2 case is allowing a ROLLBACK in the
> EXCEPTION clause without throwing an error. Is it a NOP ROLLBACK being
> applied to an aborted subTX, a real full ROLLBACK, or something else? Please
> advise.
That's an interesting question. It appears to be a no-op, although a quick
scan of the code doesn't reveal why. Here's an illustrative test case:
xof=# CREATE OR REPLACE PROCEDURE outer() AS $$
BEGIN
INSERT INTO t VALUES(3);
BEGIN
CALL inner();
PERFORM 1/0;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'in outer exception handler';
END;
END;
$$ language plpgsql;
CREATE PROCEDURE
xof=# create or replace procedure inner() as $$
BEGIN
BEGIN
INSERT INTO t VALUES(1);
PERFORM 1/0;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
INSERT INTO t VALUES(2);
END;
END;
$$
language plpgsql;
CREATE PROCEDURE
xof=# call outer();
NOTICE: in outer exception handler
CALL
xof=# table t;
i
---
3
(1 row)
xof=# truncate t;
TRUNCATE TABLE
xof=# call inner();
CALL
xof=# table t;
i
---
2
(1 row)
It clearly doesn't roll back the outer transaction. The savepoint that BEGIN
... EXCEPTION creates is released upon entry into the EXCEPTION block, so
there's no savepoint in that context to roll back to.
Pragmatically, the answer is: don't put top-level transaction control
statements in procedures where they might be invoked within an EXCEPTION block,
either directly or indirectly.
