Thread: BUG #18544: Setting local config_parameter to DEFAULT behaves unexpectedly when using period in config name

The following bug has been logged on the website:

Bug reference:      18544
Logged by:          Hayden Sim
Email address:      haydenwillsim@gmail.com
PostgreSQL version: 16.3
Operating system:   Docker Image (Ubuntu 24.04)
Description:

When a connection is initialised, calling `SELECT
current_setting('hasura.user', 't');`, as expected will return a NULL.
However if you call `SET "hasura.user" TO DEFAULT;`, this will actually
intitialise the value to an empty string. 
Note this only occurs when using a period in the config name.

This can be seen in the following logs:
```
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# \pset null [NULL]
Null display is "[NULL]".
postgres=# SELECT current_setting('server_version_num');
 current_setting
-----------------
 160003
(1 row)

postgres=# SELECT current_setting('foo');
ERROR:  unrecognized configuration parameter "foo"
postgres=# SET "foo" TO DEFAULT;
ERROR:  unrecognized configuration parameter "foo"
postgres=# SELECT current_setting('hasura.user');
ERROR:  unrecognized configuration parameter "hasura.user"
postgres=# SET "hasura.user" TO DEFAULT;
SET
postgres=# SELECT current_setting('hasura.user');
 current_setting
-----------------

(1 row)
```

This presents a huge problem, since even when called inside a transaction
and using `SET LOCAL`. The newly corrupted config value will affect the
session even after ROLLBACK. This can be seen here:
```
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# SELECT current_setting('server_version_num');
 current_setting
-----------------
 160003
(1 row)

postgres=# \pset null [NULL]
Null display is "[NULL]".
postgres=# SELECT current_setting('hasura.user', 't');
 current_setting
-----------------
 [NULL]
(1 row)

postgres=# BEGIN;
BEGIN
postgres=*# SELECT current_setting('hasura.user', 't');
 current_setting
-----------------
 [NULL]
(1 row)

postgres=*# SET LOCAL "hasura.user" TO DEFAULT;
SET
postgres=*# SELECT current_setting('hasura.user', 't');
 current_setting
-----------------

(1 row)

postgres=*# ROLLBACK;
ROLLBACK
postgres=# SELECT current_setting('hasura.user', 't');
 current_setting
-----------------

(1 row)

postgres=# SELECT current_setting('hasura.user');
 current_setting
-----------------

(1 row)
```

Thank you!
Hayden

Hi,

> However if you call `SET "hasura.user" TO DEFAULT;`, this will actually
> intitialise the value to an empty string.

The documentation for SET says [1]:

"""
DEFAULT can be written to specify resetting the parameter to its
default value (that is, whatever value it would have had if no SET had
been executed in the current session).
"""

And for current_setting() [2]:

"""
Returns the current value of the setting setting_name. If there is no
such setting, current_setting throws an error unless missing_ok is
supplied and is true (in which case NULL is returned).
""

Personally I find it confusing. If no SET had been executed,
current_setting() throws an error because there is no such setting.
Reading this literally I would expect that SET ... DEFAULT should
destroy the setting.

It seems to me that the actual behavior is correct. Even if not,
changing it would mean breaking backward compatibility.

I believe an actionable item would be to better document what SET and
current_setting() do in three cases:

- there is no given setting
- there is a setting with default value
- there is a setting with non-default value

Thoughts?

[1]: https://www.postgresql.org/docs/current/sql-set.html
[2]: https://www.postgresql.org/docs/current/functions-admin.html

-- 
Best regards,
Aleksander Alekseev

Hi,

I think the most confusing part is the documentation saying:
"""
DEFAULT can be written to specify resetting the parameter to its
default value (that is, whatever value it would have had if no SET had
been executed in the current session).
"""

It would be expected that subsequent calls to `current_setting()` should throw an error (without the "t" parameter.) As would have been the case if `SET` hadn't ever been executed in the session. This is increasingly confusing when a call to `SET LOCAL "foo.bar" TO 'baz';` runs inside a transaction, as this will affect behaviour outside of the transaction. As can be seen here:

PG Bug reporting form <noreply@postgresql.org> writes:
> When a connection is initialised, calling `SELECT
> current_setting('hasura.user', 't');`, as expected will return a NULL.
> However if you call `SET "hasura.user" TO DEFAULT;`, this will actually
> intitialise the value to an empty string. 

That's a side effect of SET causing such a custom GUC to spring
into existence --- with an empty-string default, because there
is no better value.

> This presents a huge problem,

If you don't like it, don't use custom GUCs.  They're not officially
supported --- the only reason this is allowed at all is to allow
setting of an extension's GUCs before the extension is loaded.
See

https://www.postgresql.org/docs/current/runtime-config-custom.html

            regards, tom lane

I understand it is a side effect of SET causing the custom GUC to be initialised. But the behaviour of `SET LOCAL` affecting the entire session, even outside of the transaction seems bizarre. Should exiting the transaction or calling `SET ... TO DEFAULT` not cause the parameter to be deleted?