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?

JohnH · Accepted Answer · 2024-02-17 20:58:12Z

2

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.

answered Feb 17, 2024 at 20:58

JohnH

3,2932 gold badges6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DonatasM Over a year ago

Thanks for the explanation. I didn't expect session variables to fall under the concept of "persistent database state". Maybe that third parameter in set_config is what caused the confusion. Based on the description, it seems that setting it to false means that the values persist after the transaction is completed/terminated. Does PostgreSQL have any alternative ways to store global variables? For example, Oracle has DBMS_SESSION.SET_CONTEXT or package variables. I need them for custom logging functions.

Laurenz Albe Over a year ago

PostgreSQL doesn't have any variables. What you call "variable" is really a placeholder parameter (part of the state of the database), hence the unexpected behavior.

Laurenz Albe · Accepted Answer · 2024-02-18 06:49:34Z

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

answered Feb 18, 2024 at 6:49

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

3 Comments

DonatasM Over a year ago

Thank you for the comment regarding the use of set_config outside the main execution block. Typically, more complex/sensitive code is encapsulated within internal functions with their own error-handling logic. Therefore, rolling back the parameter value to the one that existed before executing the internal function/block is sufficient for my exception-handler use case (initially, I thought it was completely discarded).

DonatasM Over a year ago

Regarding the potential error masking in the when others then section, I understand that. My example was minimized for the set_config test. Typically, I have separate handlers for known errors, while in the general when others then section, there's usually code for logging error context (such as error_stack, local variables/parameters), followed by error propagation/reraise.

Laurenz Albe Over a year ago

With my comment about good coding style I didn't want to imply that you don't know that. It is good that you simplified the example for the question.

Collectives™ on Stack Overflow

Why current_setting() value is undefined within the exception block

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related