3

I am using set_config to set some context on the session (i.e user context).

Once I set the context for a parameter - I can't seem to get rid of it. RESET/SET param TO DEFAULT will empty the value but not remove it altogether.

select current_setting('my.test1'); -- SQL Error [42704]: ERROR: unrecognized configuration parameter "my.test1"

select set_config('my.test1','123',false);

select current_setting('my.test1'); -- returns 123

set my.test1 to default;  --same as reset my.test1

select current_setting('my.test1'); --returns an empty string rather than an exception

How to remove it (raise exception again) ? I am catching 42704 but it won't be thrown if I just "reset" it.

p.s I assume pg_reload_conf might help - but it seems too aggressive for this simple task.

Thanks.

PrakashG
  • 1,642
  • 5
  • 20
  • 30
Pyrocks
  • 401
  • 3
  • 14
  • why downvote? if you think this question is not legitimate please state why and point me in the right direction – Pyrocks Feb 13 '19 at 13:32
  • The only thing I can think of is to set it to `NULL` and wherever you would catch the error also test for a null value –  Feb 13 '19 at 14:07
  • Just tested - doesn't work. it still returns an empty string (`''`) – Pyrocks Feb 13 '19 at 14:09
  • Does your code ever accept `''` as a valid value for such a parameter? If not, you could test for `''` instead. –  Feb 13 '19 at 14:11
  • If I don't have a choice - that's what I would do eventually. But I was hoping to avoid it just in case I need to support a parameter which can be null/empty. – Pyrocks Feb 13 '19 at 14:12

1 Answers1

4

The answer is that you cannot (in postgres 10), if you are in the same session.

Those empty parameters ONLY go away if you exit the session and open a new session. pg_reload_conf() has no effect on the custom variables that have been set in a session, or local to a transaction, and doesn't work to remove the parameter. They just stay as '' ... empty string.

For me this is a very legitimate question and issue also ...

i have been finding the same behaviour with custom ( ie name_one.name_two ) configuration parameters, whilst i've been developing a configuration setting wrapper to overlay into individual schemas.

Once the parameter has been set locally with eg set_config ( _name_ , _value_ , TRUE ) OR at session level with set_config ( _name_ , _value_ , FALSE) ... it is not removed if set to NULL or UNSET or to DEFAULT ... there is no way around this i have found, after testing and testing and questioning my own perception of my slightly nested functions and scoping ... and so my only answer has been to alter one of my pure SQL language functions to PLPGSQL and make a test for the particlular parameter that i was relying on as being not existing, because my call that allows missing_ok : current_setting ( '_pre._global_int_' , TRUE )does not return NULL if at some point earlier in any transaction in the session it has been set locally or not locally !!!

It had been frustrating me also, and i was very happy to find that this question had already been asked, and so here i give the answer :

it cannot be done in the same session in PG 10

( i have not tried it yet in 11 or 12 or 13 )

UPDATE : i just found this answer, https://stackoverflow.com/a/50929568/14653862, of Laurenz Albe, which also says that in the same session you cannot

sol
  • 310
  • 2
  • 7