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?
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.