Commit the transaction after exception is raised

Question

I tried looking up for the solution but failed to find one. I understand that postgres rollbacks all the transactions once an exception is raised. But my requirement is to log a certain exception into a database table.

Following is a sample code:

CREATE OR REPLACE FUNCTION fn_test(
in_var numeric DEFAULT NULL::numeric)
RETURNS numeric AS
$BODY$
DECLARE
BEGIN
IF in_var=0
THEN
    RAISE EXCEPTION using errcode='INELG';
ELSE IF in_var=1
THEN
    RAISE EXCEPTION using errcode='INVAL';
ELSE
    RETURN in_var;
END IF;
begin
EXCEPTION
WHEN sqlstate 'INELG' 
THEN
    INSERT INTO LOG_TBL(in_par,error_reason)
    VALUES(in_var,'VALUE INELGIBLE');
    RAISE EXCEPTION 'Unable to Process: Parameter Not Eligible';
WHEN sqlstate 'INVAL' 
THEN
    INSERT INTO LOG_TBL(in_par,error_reason)
    VALUES(in_var,'VALUE INValid');
    RAISE EXCEPTION 'Unable to Process: Parameter Invalid';
end; 

END;    
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;

The problem I face is as soon as an exception is raised the records in LOG_TBL in the exception section gets rolled back. Is there an alternative solution where I can insert the record into database table as well as raise an application error?

why not issue a COMMIT right after the insert and before raising the last exception? — Ricardo C, Feb 17 '16 at 19:50
@RicardoC - ASOAN can't - the custom function runs inside a transaction not outside it. — Richard Huxton, Feb 17 '16 at 20:12
@RichardHuxton right... i just learned that Postgres has no nested transactions. ummm SAVEPOINT, maybe? I guess we need to establish from ASOAN if he wants to rollback the actions taken before the call of the function or not, because a COMMIT inside the function may still be the answer... i think. — Ricardo C, Feb 18 '16 at 21:10
@RicardoC - yep, savepoint is a nested transaction and that's what's being used above to catch the exception. That's not what (s)he needs here though - it's an independant/detached transaction that's wanted so the write to log-table can be committed even if the "main" transaction gets rolled back. — Richard Huxton, Feb 19 '16 at 09:56

Richard Huxton · Answer 1 · 2016-02-17T20:25:01.980

In a simple way - you can't. Once you are inside a function, that's it.

You either have to do this in the client layer or loop back in on another connection to log the error. Traditionally you would loop back in via dblink, but it might be possible to use Foreign Data Wrappers too - I have to admit I'm not sure.

Edit: Nope, it looks like postgres_fdw syncs the transactions between local and remote. Not helpful in this case. Looks like dblink is the obvious choice.

Commit the transaction after exception is raised

1 Answers1