Hi st 22. 5. 2024 v 19:25 odesÃlatel Tom Lane <t...@sss.pgh.pa.us> napsal:
> Peter Eisentraut <pe...@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 >
