CODEWITHSUNDEEP
postgresqlplpgsql
ASOAN
ASOAN

Reputation: 41

Commit the transaction after exception is raised

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?

Upvotes: 4

Views: 2696

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22943

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.

Upvotes: 3

Related Questions