The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/sql-set.html
Description:
Hello,
I'm reading through and playing with the SET and RESET docs and I'm somewhat
unclear about the behavior of arbitrary user settings that *don't* have a
session default.
For example a user defined setting is not available:
> psql -h localhost -U postgres
psql (14.17)
Type "help" for help.
postgres=# select current_setting('foo.bar');
ERROR: unrecognized configuration parameter "foo.bar"
postgres=# select current_setting('foo.bar', true);
current_setting
-----------------
(1 row)
postgres=# select current_setting('foo.bar', true) is null;
?column?
----------
t
(1 row)
And then I set it:
postgres=# set foo.bar = 'foo!bar';
SET
postgres=# select current_setting('foo.bar');
current_setting
-----------------
foo!bar
(1 row)
And then I reset it to… what?
postgres=# reset foo.bar;
RESET
postgres=# select current_setting('foo.bar');
current_setting
-----------------
(1 row)
postgres=# select current_setting('foo.bar') = '';
?column?
----------
t
(1 row)
It seems that the assumed/implied default value for settings that did not
exist for a session is the empty string? It would be useful to document that
behavior explicitly.
We can take this further:
> psql -h localhost -U postgres
psql (14.17)
Type "help" for help.
postgres=# select current_setting('foo.bar', true) is null;
?column?
----------
t
(1 row)
postgres=# select current_setting('foo.bar', true) = '';
?column?
----------
(1 row)
postgres=# reset foo.bar; -- Same as: SET foo.bar TO DEFAULT
RESET
postgres=# select current_setting('foo.bar', true) is null;
?column?
----------
f
(1 row)
postgres=# select current_setting('foo.bar', true) = '';
?column?
----------
t
(1 row)
Note how the value & type of the user setting changes, somewhat unexpectedly
(to me).
Furthermore, it seems impossible to actually *remove* such a user defined
settings completely, correct? That too, I miss stated explicitly in the
documentation.
Much thanks!
Jens
