postgres - unrecognized configuration parameter

Question

I use the following query in my trigger to store user's ID for further use:

SELECT set_config('session.my_username', 'john', false);

Later, in other trigger I use it this way:

SELECT current_setting('session.my_username');

It works like a charm when parameter is set (first query is executed), which happens when user uses the application.

The point is, that IF developer / supporter tries to modify data that calls second trigger USING some database tool, first trigger does not get executed. In this case, developer / supporter gets unrecognized configuration parameter error message.

My question is, how to avoid such situation? Is there any way to know that configuration parameter is not set before I call SELECT current_setting()?

Answer 1

For set_config(), false in the 3rd argument means the new value applies to the current session and true in the 3rd argument means the new value applies to the current transaction as shown below:

SELECT set_config('my.num', '2', false);
SELECT current_setting('my.num'); -- 2
BEGIN;
SELECT set_config('my.num', '4', true);
SELECT current_setting('my.num'); -- 4
COMMIT;
SELECT current_setting('my.num'); -- 2

In addition, there is the error when you use the custom option which you've never set (even once) in the session and there is not the error when you use the custom option which you've set (at least once) in the session as shown below:

SELECT current_setting('my.num'); -- Error
BEGIN;
SELECT set_config('my.num', '4', true);
SELECT current_setting('my.num'); -- 4
COMMIT;
SELECT current_setting('my.num'); -- Nothing is returned without error