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()?

select current_setting('session.my_username', true); postgresql.org/docs/current/… — Abelisto
– Abelisto, Commented Jan 18, 2021 at 0:56

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-13 17:34:42Z

0

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

answered Dec 13, 2023 at 17:34

Super Kai - Kazuya Ito

42.7k23 gold badges257 silver badges256 bronze badges

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

Collectives™ on Stack Overflow

postgres - unrecognized configuration parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related