Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #18544: Setting local config_parameter to DEFAULT behaves unexpectedly when using period in config name - Mailing list pgsql-bugs

From Hayden Sim
Subject Re: BUG #18544: Setting local config_parameter to DEFAULT behaves unexpectedly when using period in config name
Date
Msg-id CALUjqOtpNk8RJg55erwGhhHgr2t7RSvWg3Xc2hv9LQxY0Dk5MQ@mail.gmail.com
Whole thread Raw
In response to Re: BUG #18544: Setting local config_parameter to DEFAULT behaves unexpectedly when using period in config name  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: BUG #18544: Setting local config_parameter to DEFAULT behaves unexpectedly when using period in config name
List pgsql-bugs
Tree view
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 Audit Logging (relevant docs) 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@sss.pgh.pa.us> wrote:
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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #18544: Setting local config_parameter to DEFAULT behaves unexpectedly when using period in config name
Next
From: Etsuro Fujita
Date:
Subject: Re: BUG #17889: Invalid cursor direction for a foreign scan that reached the fetch_size (MOVE BACKWARD ALL IN cX)