Strange Exception Handling in Postgres

Question

I'm trying to understand the exception handling in PL/pgSQL but ...

Case 1:

CREATE OR REPLACE PROCEDURE ins ()
AS $$ DECLARE
i INT;
BEGIN
insert into scott.emp (empno) values (9000);
commit;
i:=1/0;
EXCEPTION WHEN OTHERS THEN
--rollback;
END $$ LANGUAGE plpgsql;

call ins();

I would guess the line is inserted, but it is not

CASE 2:

CREATE OR REPLACE PROCEDURE ins ()
AS $$ DECLARE
i INT;
BEGIN
insert into scott.emp (empno) values (9000);
commit;
--i:=1/0;
EXCEPTION WHEN OTHERS THEN
--rollback;
END $$ LANGUAGE plpgsql;

call ins();

Now, there is no div by zero , but alos no line in the table

Case 3:

CREATE OR REPLACE PROCEDURE ins ()
    AS $$ DECLARE
    i INT;
    BEGIN
    insert into scott.emp (empno) values (9000);
    commit;
    --i:=1/0;
    --EXCEPTION WHEN OTHERS THEN
    --rollback;
    END $$ LANGUAGE plpgsql;

    call ins();

Now the line is inserted. It seems that the Problen is my Exception Block, but why?

Thanks in advance. Marco

Read full explanation here: [Can a Postgres Commit Exist in Procedure that has an Exception Block?](https://stackoverflow.com/questions/55406735/can-a-postgres-commit-exist-in-procedure-that-has-an-exception-block) — StephaneM, Dec 19 '19 at 15:16

jjanes · Answer 1 · 2019-12-19T20:44:03.913

Silently throwing away your error message certainly does make debugging difficult.

CREATE OR REPLACE PROCEDURE ins ()
AS $$ DECLARE
i INT;
BEGIN
insert into scott.emp (empno) values (9000);
commit;
--i:=1/0;
EXCEPTION WHEN OTHERS THEN
raise notice E'%', SQLERRM;
END $$ LANGUAGE plpgsql;

Shows:

NOTICE:  cannot commit while a subtransaction is active

So this due to the documented limitation:

A transaction cannot be ended inside a block with exception handlers.

Strange Exception Handling in Postgres

1 Answers1