Unexpected behaviour: it was documented to return the same value

On Saturday, July 5, 2025, PG Doc comments form <noreply@postgresql.org>
wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/17/functions-admin.html
Description:

set_config ( setting_name text, new_value text, is_local boolean ) → text
Sets the parameter setting_name to new_value, and returns that value. If
is_local is true, the new value will only apply during the current
transaction. If you want the new value to apply for the rest of the current
session, use false instead. This function corresponds to the SQL command
SET.
set_config('log_statement_stats', 'off', false) → off
```
invoice=> select set_config( 'app.asdf', null, false ) is null;
?column?
----------
f
(1 row)
invoice=> select set_config( 'app.asdf', null, false ) = '';
?column?
----------
t
(1 row)
```
Expected: because I provided NULL value I expect to receive NULL as result
Actual: but function returns the empty string instead.
It would be better to follow documented behaviour, if it will not be
possible to fix it, please at least document that.

There are pending patch(es?) in this area, I see to what extent this
specific wording is covered (not sure we are back-patching the new wording
either…). The main point to remember is that a configuration setting can
never take on the null value. All customized setting placeholders *that
exist* are text typed with a non-null value defaulting to the empty string.

David J.

On Mon, Jul 7, 2025 at 6:51 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Saturday, July 5, 2025, PG Doc comments form <noreply@postgresql.org>
wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/17/functions-admin.html
Description:

Yeah, this clarification just wasn't back-patched.

https://www.postgresql.org/docs/18/functions-admin.html#FUNCTIONS-ADMIN-SET

"set_config accepts the NULL value for new_value, but as settings cannot be
null, it is interpreted as a request to reset the setting to its default
value."

David J.

<div>I would prefer to remove the option from the configuration list instead of having it reset to empty string.</div><div> </div><div>Eg. set_config( 'my.option', NULL, false )</div><div> </div><div>Having it to return the empty string cause differences in the behaviour when parameter is not recognized at all and when it setup to default.</div><div> </div><div>How to make it "unrecognizable" again using set_config/RESET?</div><div> </div><div>The exact problem I am trying to resolve was asked here: https://stackoverflow.com/q/50923911/4632019&lt;/div&gt;&lt;div&gt; &lt;/div&gt;&lt;div&gt;&lt;br /></div><div><br /></div><div>----------------</div>
<div>Кому: kes-kes@yandex.ru (kes-kes@yandex.ru), pgsql-docs@lists.postgresql.org (pgsql-docs@lists.postgresql.org);<br /></div>

<div>Тема: Unexpected behaviour: it was documented to return the same value;<br /></div>
<div>07.07.2025, 17:40, "David G. Johnston" &lt;david.g.johnston@gmail.com&gt;:<br /></div>
<blockquote><div dir="ltr"><div dir="ltr"><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><span style="font-family:'arial' , 'helvetica' , sans-serif">On Mon, Jul 7, 2025 at 6:51 AM David G. Johnston &lt;<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>&gt; wrote:</span></div></div><div class="f13ca48719c8a60033905b23b39675agmail_quote 5ec078e9233601eab4dccacd580e6022gmail_quote_container"><blockquote class="f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex">On Saturday, July 5, 2025, PG Doc comments form &lt;<a href="mailto:noreply@postgresql.org" target="_blank">noreply@postgresql.org</a>&gt; wrote:<br /><blockquote class="f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex">The following documentation comment has been logged on the website:<br />
<br />
Page: <a href="https://www.postgresql.org/docs/17/functions-admin.html&quot; target="_blank">https://www.postgresql.org/docs/17/functions-admin.html&lt;/a&gt;&lt;br />
Description:<br /></blockquote></blockquote><div><br /></div><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif">Yeah, this clarification just wasn't back-patched.</div><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><a href="https://www.postgresql.org/docs/18/functions-admin.html#FUNCTIONS-ADMIN-SET&quot;&gt;https://www.postgresql.org/docs/18/functions-admin.html#FUNCTIONS-ADMIN-SET&lt;/a&gt;&lt;/div&gt;&lt;div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif">"set_config accepts the NULL value for new_value, but as settings cannot be null, it is interpreted as a request to reset the setting to its default value."</div><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif">David J.</div><div class="81bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div></div></div>
</blockquote>

<div>I suppose that DEFAULT syntax should be used to set setting to its default value. Eg.</div><div><div>set_config( 'my.option', DEFAULT, false )</div><div>This is clear what should happen here. Also this syntax will correspond to other places, eg. INSERT INTO xx (name) VALUES (DEFAULT).</div><div> </div><div>Using NULL to set reset the value to the default is very very confusing.</div><div><div>set_config( 'my.option', NULL, false )</div><div>Expected: this call should make option unrecognizable again.</div></div></div><div><br /></div><div><br /></div><div>----------------</div>
<div>Кому: David G. Johnston (david.g.johnston@gmail.com), pgsql-docs@lists.postgresql.org (pgsql-docs@lists.postgresql.org);<br /></div>

<div>Тема: Unexpected behaviour: it was documented to return the same value;<br /></div>
<div>13.07.2025, 16:34, "KES" &lt;kes-kes@yandex.ru&gt;:<br /></div>
<blockquote><div>I would prefer to remove the option from the configuration list instead of having it reset to empty string.</div><div> </div><div>Eg. set_config( 'my.option', NULL, false )</div><div> </div><div>Having it to return the empty string cause differences in the behaviour when parameter is not recognized at all and when it setup to default.</div><div> </div><div>How to make it "unrecognizable" again using set_config/RESET?</div><div> </div><div>The exact problem I am trying to resolve was asked here: <a href="https://stackoverflow.com/q/50923911/4632019&quot;&gt;https://stackoverflow.com/q/50923911/4632019&lt;/a&gt;&lt;/div&gt;&lt;div&gt; &lt;/div&gt;&lt;div&gt;&lt;br /></div><div><br /></div><div>----------------</div>
<div>Кому: <a href="mailto:kes-kes@yandex.ru">kes-kes@yandex.ru</a> (<a href="mailto:kes-kes@yandex.ru">kes-kes@yandex.ru</a>), <a href="mailto:pgsql-docs@lists.postgresql.org">pgsql-docs@lists.postgresql.org</a> (<a href="mailto:pgsql-docs@lists.postgresql.org">pgsql-docs@lists.postgresql.org</a>);<br /></div>

<div>Тема: Unexpected behaviour: it was documented to return the same value;<br /></div>
<div>07.07.2025, 17:40, "David G. Johnston" &lt;<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>&gt;:<br /></div>
<blockquote><div dir="ltr"><div dir="ltr"><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><span style="font-family:'arial' , 'helvetica' , sans-serif">On Mon, Jul 7, 2025 at 6:51 AM David G. Johnston &lt;<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>&gt; wrote:</span></div></div><div class="d96d0fa01d7ccee98219a8e94bccf061f13ca48719c8a60033905b23b39675agmail_quote 425685429bfea96f41dc11498b978baf5ec078e9233601eab4dccacd580e6022gmail_quote_container"><blockquote class="d96d0fa01d7ccee98219a8e94bccf061f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex">On Saturday, July 5, 2025, PG Doc comments form &lt;<a href="mailto:noreply@postgresql.org" target="_blank">noreply@postgresql.org</a>&gt; wrote:<br /><blockquote class="d96d0fa01d7ccee98219a8e94bccf061f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex">The following documentation comment has been logged on the website:<br />
<br />
Page: <a href="https://www.postgresql.org/docs/17/functions-admin.html&quot; target="_blank">https://www.postgresql.org/docs/17/functions-admin.html&lt;/a&gt;&lt;br />
Description:<br /></blockquote></blockquote><div><br /></div><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif">Yeah, this clarification just wasn't back-patched.</div><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><a href="https://www.postgresql.org/docs/18/functions-admin.html#FUNCTIONS-ADMIN-SET&quot;&gt;https://www.postgresql.org/docs/18/functions-admin.html#FUNCTIONS-ADMIN-SET&lt;/a&gt;&lt;/div&gt;&lt;div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif">"set_config accepts the NULL value for new_value, but as settings cannot be null, it is interpreted as a request to reset the setting to its default value."</div><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif">David J.</div><div class="9d2662d4c03e6b9f3bb9a430a42f74e581bf8e361cebb2c6d64fb4d6842d6ebgmail_default" style="font-family:'arial' , 'helvetica' , sans-serif"><br /></div></div></div>
</blockquote></blockquote>

On Sunday, July 13, 2025, KES <kes-kes@yandex.ru> wrote:

I suppose that DEFAULT syntax should be used to set setting to its default
value. Eg.
set_config( 'my.option', DEFAULT, false )
This is clear what should happen here. Also this syntax will correspond to
other places, eg. INSERT INTO xx (name) VALUES (DEFAULT).

Using NULL to set reset the value to the default is very very confusing.
set_config( 'my.option', NULL, false )
Expected: this call should make option unrecognizable again.

I don’t see us changing this even if someone were willing to write a
patch. You will have to make do with the behavior that exists.

David J.

KES <kes-kes@yandex.ru> writes:

I would prefer to remove the option from the configuration list
instead of having it reset to empty string.

That is not the definition, and we'd be very unlikely to change
it to do that, because it would make no sense for built-in or
extension-defined settings.

regards, tom lane

<div>Ok. Yes, changing the existing behaviour could lead to back compatibility issues.</div><div> </div><div>But have you found the functionality to "make an option unrecognizable again" useful? In theory, would it be appropriate to have eg. "UNSET" to perform that?</div><div><br /></div><div><br /></div><div>----------------</div>
<div>Кому: KES (kes-kes@yandex.ru);<br /></div>
<div>Копия: pgsql-docs@lists.postgresql.org;<br /></div>
<div>Тема: Unexpected behaviour: it was documented to return the same value;<br /></div>
<div>13.07.2025, 18:17, "David G. Johnston" &lt;david.g.johnston@gmail.com&gt;:<br /></div>
<blockquote>On Sunday, July 13, 2025, KES &lt;<a href="mailto:kes-kes@yandex.ru">kes-kes@yandex.ru</a>&gt; wrote:<br /><blockquote class="f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:#ccc;border-left-style:solid;border-left-width:1px;margin:0 0 0 0.8ex;padding-left:1ex"><div>I suppose that DEFAULT syntax should be used to set setting to its default value. Eg.</div><div><div>set_config( 'my.option', DEFAULT, false )</div><div>This is clear what should happen here. Also this syntax will correspond to other places, eg. INSERT INTO xx (name) VALUES (DEFAULT).</div><div> </div><div>Using NULL to set reset the value to the default is very very confusing.</div><div><div>set_config( 'my.option', NULL, false )</div><div>Expected: this call should make option unrecognizable again.</div></div></div></blockquote><div><br /></div><div>I don’t see us changing this even if someone were willing to write a patch.  You will have to make do with the behavior that exists.</div><div><br /></div><div>David J.</div>
</blockquote>