PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why current_setting() value is undefined within the exception block

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search