Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Schema variables - new implementation for Postgres 15
Date
Msg-id CAFj8pRAzdXiVkuLiVYQLvoTgsQEL28ckEqNLqoq3H2DiN_cSuw@mail.gmail.com
Whole thread Raw
In response to Re: Schema variables - new implementation for Postgres 15  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Schema variables - new implementation for Postgres 15
List pgsql-hackers
Hi

st 22. 5. 2024 v 19:25 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Peter Eisentraut <peter@eisentraut.org> writes:
> On 18.05.24 13:29, Alvaro Herrera wrote:
>> I want to note that when we discussed this patch series at the dev
>> meeting in FOSDEM, a sort-of conclusion was reached that we didn't want
>> schema variables at all because of the fact that creating a variable
>> would potentially change the meaning of queries by shadowing table
>> columns.  But this turns out to be incorrect: it's_variables_  that are
>> shadowed by table columns, not the other way around.

> But that's still bad, because seemingly unrelated schema changes can
> make variables appear and disappear.  For example, if you have
>       SELECT a, b FROM table1
> and then you drop column b, maybe the above query continues to work
> because there is also a variable b.

Yeah, that seems pretty dangerous.  Could we make it safe enough
by requiring some qualification on variable names?  That is, if
you mean b to be a variable, then you must write something like

        SELECT a, pg_variables.b FROM table1

This is still ambiguous if you use "pg_variables" as a table alias in
the query, but the alias would win so the query still means what it
meant before.  Also, table aliases (as opposed to actual table names)
don't change readily, so I don't think there's much risk of the query
suddenly meaning something different than it did yesterday.

we can introduce special safe mode started by

set enable_direct_variable_read to off;

and allowing access to variables only by usage dedicated function (supported by parser) named like variable or pg_variable

so it can looks like

select a, pg_variable(myschema.myvar) from table 

In this mode, the variables never are readable directly, so there is no risk of collision and issue mentioned by Peter. And the argument of the pg_variable pseudo function can be only variable, so risk of possible collision can be reduced too. The pseudo function pg_variable can be used in less restrictive mode too, when the user can explicitly show usage of the variable. 

Tom's proposal is already almost supported now. The user can use a dedicated schema without assigning this schema to search_path. Then a qualified name should be required.

Can this design be the correct answer for mentioned objections?

 Regards

Pavel



                        regards, tom lane

pgsql-hackers by date:

Previous
From: Jelte Fennema-Nio
Date:
Subject: Re: Add new protocol message to change GUCs for usage with future protocol-only GUCs
Next
From: Tom Lane
Date:
Subject: Re: Schema variables - new implementation for Postgres 15