pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedset_config with is_local parameter true escapes transaction boundaries
5+ messages / 4 participants
[nested] [flat]
* set_config with is_local parameter true escapes transaction boundaries
@ 2023-01-10 08:20 Ruslan Talpa <ruslan.talpa@subzero.cloud>
0 siblings, 1 reply; 5+ messages in thread
From: Ruslan Talpa @ 2023-01-10 08:20 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
When executing set_config with the parameter ‘is_local’ set to true within a transaction, it is expected that the config value is not available after the transaction however once the config is set in any transaction, current_setting will return an empty string instead of null
If this is expected behaviour, it is not mentioned anywhere in the documentation.
Steps to reproduce / example
psql (13.9, server 15.1 (Debian 15.1-1.pgdg110+1))
WARNING: psql major version 13, server major version 15.
Some psql features might not work.
Type "help" for help.
app=# select current_setting('custom.a', true), current_setting('custom.a', true) is null;
current_setting | ?column?
-----------------+----------
| t
(1 row)
app=# BEGIN;
START TRANSACTION
app=*# select set_config('custom.a', 'vv', true);
set_config
------------
vv
(1 row)
app=*# COMMIT;
COMMIT
app=# select current_setting('custom.a', true), current_setting('custom.a', true) is null;
current_setting | ?column?
-----------------+----------
| f
(1 row)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: set_config with is_local parameter true escapes transaction boundaries
@ 2023-01-11 00:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ruslan Talpa <ruslan.talpa@subzero.cloud>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-01-11 00:39 UTC (permalink / raw)
To: Ruslan Talpa <ruslan.talpa@subzero.cloud>; +Cc: pgsql-bugs@lists.postgresql.org
Ruslan Talpa <ruslan.talpa@subzero.cloud> writes:
> When executing set_config with the parameter ‘is_local’ set to true within a transaction, it is expected that the config value is not available after the transaction however once the config is set in any transaction, current_setting will return an empty string instead of null
The GUC infrastructure doesn't recognize null as a value, so you
are making a distinction that the system doesn't.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: set_config with is_local parameter true escapes transaction boundaries
@ 2023-01-11 06:54 Ruslan Talpa <ruslan.talpa@subzero.cloud>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Ruslan Talpa @ 2023-01-11 06:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
It’s true that null is not a valid value for GUC however the infrastructure does make a distinction between “the setting has no value, as in empty string” and “there is no such setting, null” and since the documentation for set_config only says “If is_local is true, the new value will only apply during the current transaction.” the expectation is that after the transaction things should return to the previous state (i.e current_setting should return null)
Maybe this is only a documentation issue, for example have a note in 9.27.1 or 20.16 along the lines “Note: Once a GUC value has been set, even with is_local set to true, subsequent calls to current_setting will return an empty string”
In any case, I am only reporting (what I view as) an inconsistency i came across that is not mentioned anywhere and thought I’d bring it up so that PG developers are aware can make a decision either way.
Thank you for your work (and reply to the email)
PS: A few more words about the context where this came up, feel free to skip this.
Per 20.16 it’s clear that this infrastructure was designed for PG extensions to use, however currently it’s being (ab)used by popular tools like PostgREST/Postgraphile/Hasura as a “transaction context”, i.e. store within a transaction things like “who is executing the current query” or “from what ip” so that code from the database context (triggers/views/stored procedures) can act based on that information. Looking at it from this use case point of view, one would want to make a distinction between “that context value is not set” and “that context value is an empty string” or at least be aware this issue exists.
> On 11 Jan 2023, at 02:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ruslan Talpa <ruslan.talpa@subzero.cloud> writes:
>> When executing set_config with the parameter ‘is_local’ set to true within a transaction, it is expected that the config value is not available after the transaction however once the config is set in any transaction, current_setting will return an empty string instead of null
>
> The GUC infrastructure doesn't recognize null as a value, so you
> are making a distinction that the system doesn't.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: set_config with is_local parameter true escapes transaction boundaries
@ 2023-01-12 05:24 Jobin Augustine <jobin.augustine@percona.com>
parent: Ruslan Talpa <ruslan.talpa@subzero.cloud>
1 sibling, 0 replies; 5+ messages in thread
From: Jobin Augustine @ 2023-01-12 05:24 UTC (permalink / raw)
To: Ruslan Talpa <ruslan.talpa@subzero.cloud>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
Yes, The behaviour changes after the parameter are used for the first time.
This could be a problem if the connection is reused (pool)
RESET/DISCARD also not bringing back the original behaviour.
--Please test this on a fresh connection
--Check the status before the first transaction
select current_setting('custom.a',true),current_setting('custom.a',true) is
null, current_setting('custom.a',true)='';
--First transaction where the parameter is set at the transaction level
BEGIN;
SELECT set_config('custom.a', 'vv', true);
COMMIT;
--Reset
RESET ALL;
DISCARD ALL;
--Check the status after the first transaction
select current_setting('custom.a',true),current_setting('custom.a',true) is
null, current_setting('custom.a',true)='';
-Jobin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: set_config with is_local parameter true escapes transaction boundaries
@ 2023-01-12 05:58 David G. Johnston <david.g.johnston@gmail.com>
parent: Ruslan Talpa <ruslan.talpa@subzero.cloud>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-01-12 05:58 UTC (permalink / raw)
To: Ruslan Talpa <ruslan.talpa@subzero.cloud>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
On Wed, Jan 11, 2023 at 1:16 AM Ruslan Talpa <ruslan.talpa@subzero.cloud>
wrote:
> Maybe this is only a documentation issue, for example have a note in
> 9.27.1 or 20.16 along the lines “Note: Once a GUC value has been set, even
> with is_local set to true, subsequent calls to current_setting will return
> an empty string”
>
Yes, we really should get around to documenting this better. I haven't
seen any indication that it is going to change. It hasn't in the 5 years
or so since it last was brought to our attention.
https://www.postgresql.org/message-id/flat/CAKFQuwbQ40dUuNbQb8R9h53WD1LOV2dVEJv%3DLydJLwt%2BdxZQGw%4...
Though maybe with our recent performance enhancements, and a more accepting
mentality toward people creating custom settings, and the stalled nature of
the session variables feature, maybe someone will have a change of heart.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-01-12 05:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-10 08:20 set_config with is_local parameter true escapes transaction boundaries Ruslan Talpa <ruslan.talpa@subzero.cloud>
2023-01-11 00:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-11 06:54 ` Ruslan Talpa <ruslan.talpa@subzero.cloud>
2023-01-12 05:24 ` Jobin Augustine <jobin.augustine@percona.com>
2023-01-12 05:58 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox