Why current_setting() value is undefined within the exception block

Question

After setting the configuration parameter with set_config() I can read it with current_setting(). But if exception ocured and I try to read value within the exception block it is undefined.

do $$declare
    l_ctx_prm text := 'some_value'; 
    l_dummy numeric;
begin
    raise notice 'test before set_config: [utl_log.test_ctx=%]', current_setting('utl_log.test_ctx', true);  
    perform set_config('utl_log.test_ctx', l_ctx_prm, false);
    raise notice 'test after set_config: [utl_log.test_ctx=%]', current_setting('utl_log.test_ctx', true);  
    l_dummy := 1/0; -- raise exception 
exception when others then
    raise notice 'test in exception block: [utl_log.test_ctx=%]', current_setting('utl_log.test_ctx', true);  
end$$;

The result is

test before set_config: [utl_log.test_ctx=<NULL>]
test after set_config: [utl_log.test_ctx=some_value]
test in exception block: [utl_log.test_ctx=]

Can someone explain this behavior?

Answer 1

From the manual:

When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back.

The call to set_config is an attempt to change persistent state, so the effects are rolled back prior to entering the exception block.

Answer 2

You'll have to put the block with the exception handler right where the potential error can happen, so that your parameter setting isn't rolled back:

BEGIN
    PERFORM set_config('utl_log.test_ctx', l_ctx_prm, false);
    BEGIN
        l_dummy := 1/0; -- raise exception 
    EXCEPTION WHEN division_by_zero THEN
        /* handle the exception */
    END;  
END;

Basically, you should follow two rules for good programming

• don't handle errors with a lazy catch-it-all exception handler around your whole code

• catch specific exceptions that you expect to avoid masking unexpected errors