Thread: BUG #15646: Inconsistent behavior for current_setting/set_config
The following bug has been logged on the website: Bug reference: 15646 Logged by: Eugen Konkov Email address: kes-kes@yandex.ru PostgreSQL version: 10.4 Operating system: (Debian 10.4-2.pgdg90+1) Description: Hi. [documentation](https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-ADMIN-SET) describes that: >set_config sets the parameter setting_name to new_value. If is_local is true, the new value will only apply to the current transaction. Thus if I rollback transaction original value should be available. But current behavior returns empty string instead of NULL (the initial value) after transaction is rolled back. When I restart session, NULL is returned again as it is expected. ( I expect NULL just because 'my.app_period' is not configuration variable is not defined yet. The documentation (link provided above) does not cover what should be returned) How to reproduce steps: $ make dbshell psql -h databases -p 5433 -U tucha tucha ||: psql (11.1 (Ubuntu 11.1-1.pgdg16.04+1), server 10.4 (Debian 10.4-2.pgdg90+1)) Type "help" for help. We start a new session and check setting value before transaction. It is NULL: tucha=> select current_setting( 'my.app_period', true ) is null; ?column? ---------- t (1 row) We start transaction and change the setting value: tucha=> begin; BEGIN tucha=> select set_config( 'my.app_period', 'some value', true ); set_config ------------ some value (1 row) We can see that value is changed. It is NOT NULL: tucha=> select current_setting( 'my.app_period', true ) is null; ?column? ---------- f (1 row) tucha=> select current_setting( 'my.app_period', true ); current_setting ----------------- some value (1 row) Here I finish transaction (it has no matter how: commit/rollback): tucha=> rollback; ROLLBACK Here we can see that setting value is different from value that was before transaction tucha=> select current_setting( 'my.app_period', true ) is null; ?column? ---------- f (1 row) tucha=> \q When I restart session I get NULL again (as expected): kes@work ~/t $ make dbshell psql -h databases -p 5433 -U tucha tucha ||: psql (11.1 (Ubuntu 11.1-1.pgdg16.04+1), server 10.4 (Debian 10.4-2.pgdg90+1)) Type "help" for help. tucha=> select current_setting( 'my.app_period', true ) is null; ?column? ---------- t (1 row) My database version: tucha=> select version(); version ------------------------------------------------------------------------------------------ PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian (1 row)
On 2/20/19 11:10 AM, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 15646 > Logged by: Eugen Konkov > Email address: kes-kes@yandex.ru > PostgreSQL version: 10.4 > Operating system: (Debian 10.4-2.pgdg90+1) > Description: > > Hi. > > [documentation](https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-ADMIN-SET) > describes that: > >>set_config sets the parameter setting_name to new_value. If is_local is > true, the new value will only apply to the current transaction. > > Thus if I rollback transaction original value should be available. > > But current behavior returns empty string instead of NULL (the initial > value) after transaction is rolled back. When I restart session, NULL is > returned again as it is expected. This has been discussed before and dismissed: https://www.postgresql.org/message-id/flat/56842412.5000005%40joeconway.com Personally I agree it is a bug, but I am not sure you will get much support for that position. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Attachment
Joe Conway <mail@joeconway.com> writes: > On 2/20/19 11:10 AM, PG Bug reporting form wrote: >> But current behavior returns empty string instead of NULL (the initial >> value) after transaction is rolled back. When I restart session, NULL is >> returned again as it is expected. > This has been discussed before and dismissed: > https://www.postgresql.org/message-id/flat/56842412.5000005%40joeconway.com > Personally I agree it is a bug, but I am not sure you will get much > support for that position. The fact that we allow undeclared user-defined GUCs at all is a bug IMO. We need to find a way to replace that behavior with something whereby the name and type of a parameter are declared up-front before you can set it. regards, tom lane
On Wed, Feb 20, 2019 at 10:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Joe Conway <mail@joeconway.com> writes: > > On 2/20/19 11:10 AM, PG Bug reporting form wrote: > >> But current behavior returns empty string instead of NULL (the initial > >> value) after transaction is rolled back. When I restart session, NULL is > >> returned again as it is expected. > > > This has been discussed before and dismissed: > > https://www.postgresql.org/message-id/flat/56842412.5000005%40joeconway.com > > Personally I agree it is a bug, but I am not sure you will get much > > support for that position. > > The fact that we allow undeclared user-defined GUCs at all is a bug IMO. > We need to find a way to replace that behavior with something whereby > the name and type of a parameter are declared up-front before you can > set it. We should at least document the existing working-as-intended behavior then. This, the linked thread, and Bug # 14877 are all caused by insufficient documentation of the current behavior. Users should be informed that as far as the GUC system is concerned NULL and the empty string are equivalent and that resetting uses the empty string while never being set returns NULL. Its immaterial whether its existence is due to a bug that simply became acceptable or was an otherwise retrospectively poor design decision - at this point we have to live with it and should treat it as a proper and supported feature, if only in its current form. At least until someone feels strongly enough to deprecate it and put something else more suitable in its place. David J.
Hi,
On Feb 20, 2019, at 9:32 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:Users should be
informed that as far as the GUC system is concerned NULL and the empty
string are equivalent and that resetting uses the empty string while
never being set returns NULL.
Except that it’s not - the code path in guc.c uses what is passed as the boot value:
It’s up to the extension developer to understand that this can be changed back to something other than the boot value and not actually be the boot value - you can see this assumption being made in plpgsql, pltcl, and plperl. The GUC system makes no notification that the two are equivalent, neither in code nor in documentation.
Its immaterial whether its existence is due to a bug that simply
became acceptable or was an otherwise retrospectively poor design
decision - at this point we have to live with it and should treat it
as a proper and supported feature, if only in its current form. At
least until someone feels strongly enough to deprecate it and put
something else more suitable in its place.
Agreed, but it needs to be documented, the current documentation gives only the boot value, and does not note that string variables are the only variables that behave differently and do not return to their boot value.