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? — StephaneM
– StephaneM, Commented Dec 19, 2019 at 15:16

jjanes · Accepted Answer · 2019-12-19 20:44:03Z

4

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.

edited Dec 19, 2019 at 20:44

answered Dec 19, 2019 at 15:29

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Strange Exception Handling in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related