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

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?
```
SELECT current_setting('param.value', 't'); -- is NULL
BEGIN;
SET LOCAL "param.value" TO 'some_value';
COMMIT;
SELECT current_setting('param.value', 't'); -- is empty string
```

This is extremely uncommon, but presented a problem when using Hasura
<https://hasura.io> Audit Logging (relevant docs
<https://hasura.io/docs/latest/schema/postgres/postgres-guides/auditing-tables/>)
with PGBouncer, as Hasura relied on this functionality and tried to assign
this value to a JSON parameter inside of a trigger.

The repro steps are as follows:

1. Hasura connects to PGB
2. Hasura sends transaction which looks like
```
BEGIN;
SET LOCAL "hasura.user" = '{"x-hasura-role": "role", ... various session
variables}'
-- Some mutation ...
COMMIT;
```
3. Another SQL Client connects to PGB and is given the same underlying
SQL connection as the previous Hasura connection.
4. SQL Client triggers a Hasura trigger that expects this value to be set
```
UPDATE tableWithTrigger ...;
```
5. SQL Client is faced with a JSON parse exception

Example of a trigger:
```
...
DECLARE
session_variables json;
...
BEGIN
...
session_variables = current_setting('hasura.user', 't');
```

In this case, NULL is an acceptable value and translates to JSON `null`,
however an empty string will cause an invalid JSON exception to be raised
and thus fail the trigger and the whole write.

We've lodged a bug request with Hasura and asked them to expect in their
triggers that the value could potentially be an empty string.

Though I still believe this behaviour is unexpected and if `SET LOCAL`
exists, it would be expected that the GUC is returned to its previous state
of being uninitialised, after the transaction has been finalised.

Thank you,
Hayden

On Thu, Jul 18, 2024 at 11:55 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)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
>