Re: Schema variables - new implementation for Postgres 15

Hi

st 22. 5. 2024 v 19:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Peter Eisentraut <peter(at)eisentraut(dot)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
>