Re: set/reset issues in create function and set_config

At Tue, 24 Apr 2018 16:32:08 +0100, Alexey Bashtanov <bashtanov(at)imap(dot)cc> wrote in <a7c1f0cb-1031-abf8-e4cc-13cf781d6563(at)imap(dot)cc>
> Hello,
>
> It looks like set_config does set-to-default if it's called with
> second argument being null, but this is undocumented.
> Please see the docs patch to fix it.

Agreed.

> Also, SET to DEFAULT or RESET in CREATE FUNCTION is accepted but does
> nothing.
> It wouldn't be a problem, but documentation to SET command says:
>
> > If SET LOCAL is used within a function that has a SET option for the
> > same variable (see CREATE FUNCTION
> > <https://www.postgresql.org/docs/9.4/static/sql-createfunction.html>),
> > the effects of the SET LOCAL command disappear at function exit
>
> This doesn't work with CREATE FUNCTION ... SET ... DEFAULT ....
> What do you think of just disallowing it? Patch attached as well.

SET .. DEFAULT and RESET are defined as follows.

https://www.postgresql.org/docs/10/static/sql-reset.html

> The default value is defined as the value that the parameter
> would have had, if no SET had ever been issued for it in the
> current session.

Precisely following this statement, SET ... TO DEFAULT changes
the variable to the session default temporarily. For example, the
attached patch (of course, just a PoC) does that.

=# create or replace function f() returns record as $$ select name, setting, reset_val from pg_settings where name = 'log_min_messages'; $$ set log_min_messages to default language sql;
=# set log_min_messages = debug5;
=# show log_min_messages;
log_min_messages
------------------
debug5
(1 row)
=# set log_min_messages = debug5;
SET
postgres=# select f();
f
------------------------------------
(log_min_messages,warning,warning)
(1 row)
=# show log_min_messages;
log_min_messages
------------------
debug5
(1 row)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center