Re: Schema variables - new implementation for Postgres 15

Hi

pá 17. 11. 2023 v 20:17 odesílatel Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
napsal:

> > On Wed, Aug 23, 2023 at 04:02:44PM +0200, Pavel Stehule wrote:
> > NameListToString is already buildin function. Do you think NamesFromList?
> >
> > This is my oversight - there is just `+extern List *NamesFromList(List
> > *names); ` line, but sure - it should be in 0002 patch
> >
> > fixed now
>
> Right, thanks for fixing.
>
> I think there is a wrinkle with pg_session_variables function. It
> returns nothing if sessionvars hash table is empty, which has two
> consequences:
>
> * One might get confused about whether a variable is created,
> based on the information from the function. An expected behaviour, but
> could be considered a bad UX.
>
> =# CREATE VARIABLE var1 AS varchar;
>
> -- empty, is expected
> =# SELECT name, typname, can_select, can_update FROM
> pg_session_variables();
> name | typname | can_select | can_update
> ------+---------+------------+------------
> (0 rows)
>
> -- but one can't create a variable
> =# CREATE VARIABLE var1 AS varchar;
> ERROR: 42710: session variable "var1" already exists
> LOCATION: create_variable, pg_variable.c:102
>
> -- yet, suddenly after a select...
> =# SELECT var2;
> var2
> ------
> NULL
> (1 row)
>
> -- ... it's not empty
> =# SELECT name, typname, can_select, can_update FROM pg_sessio
> n_variables();
> name | typname | can_select | can_update
> ------+-------------------+------------+------------
> var2 | character varying | t | t
> (1 row)
>
> * Running a parallel query will end up returning an empty result even
> after accessing the variable.
>
> -- debug_parallel_query = 1 all the time
> =# CREATE VARIABLE var2 AS varchar;
>
> -- empty, is expected
> =# SELECT name, typname, can_select, can_update FROM
> pg_session_variables();
> name | typname | can_select | can_update
> ------+---------+------------+------------
> (0 rows)
>
> -- but this time an access...
> SELECT var2;
> var2
> ------
> NULL
> (1 row)
>
> -- or set...
> =# LET var2 = 'test';
>
> -- doesn't change the result, it's still empty
> =# SELECT name, typname, can_select, can_update FROM
> pg_session_variables();
> name | typname | can_select | can_update
> ------+---------+------------+------------
> (0 rows)
>
> Would it be a problem to make pg_session_variables inspect the catalog
> or something similar if needed?
>

It can be very easy to build pg_session_variables based on iteration over
the system catalog. But I am not sure if we want it. pg_session_variables()
is designed to show the variables from session memory, and it is used for
testing. Originally it was named pg_debug_session_variables. If we iterate
over catalog, it means using locks, and it can have an impact on isolation
tests.

So maybe we can introduce a parameter for this function to show all session
variables (based on catalog) or only used based on iteration over memory.
Default can be "all". What do you think about it?

The difference between debug_parallel_query = 1 and debug_parallel_query =
0 is strange - and I'll check it.