Re: Schema variables - new implementation for Postgres 15

Hi

pá 17. 11. 2023 v 20:17 odesílatel Dmitry Dolgov <9erthalion6@gmail.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.