CODEWITHSUNDEEP
postgresqlpostgresql-9.6postgresql-10
Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

How to remove configuration parameter

In Postgres it is possible to create your own configuration parameters, something like a "cookie" that persists for duration of either session or transaction.

This is done like that:

SELECT set_config(setting_name, new_value, is_local)
or
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }

Local is supposed to persist only for duration of transaction, but it does affect configuration parameter even after transaction - instead of said parameter being unrecognized, it will be now set to empty string.

Question

How to make said parameter unrecognized again, without reconnecting?

This does not work:

SELECT set_config('settings.myapp.value', null, true);
RESET settings.myapp.value;

This will not return NULL, instead it gives empty string:

SELECT current_setting('settings.myapp.value', true);

I can of course work around this, but I would like to know if I can somehow revert state of configuration parameter back to what it was before "transaction only" change.

SELECT nullif(current_setting('settings.myapp.value', true), '');

Upvotes: 7

Views: 3265

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

You cannot do that.

If you create a new parameter, it is created as a “placeholder” parameter. If you later load a module that defines that parameter, it will be converted to a “real” parameter on the fly.

But there is no way to delete a parameter during the lifetime of a database session.

Upvotes: 11

Related Questions