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.
With active shadowing variable warning for described example you will get a warning before dropping.
Session variables are joined with schema (in my proposal). Do anybody can do just
CREATE SCHEMA svars; -- or what (s)he likes
CREATE VARIABLE svars.b AS int;
SELECT a, b FROM table1
and if somebody can be really safe, the can write
SELECT t.a, t.b FROM table1 t
or
SELECT t.a, svars.b FROM table1 t
It can be customized in the way anybody prefers - just creating dedicated schemas and setting search_path. Using its own schema for session variables without enhancing search_path for this schema forces the necessity to set only qualified names for session variables.
Sure the naming of schemas, aliases can be unhappy wrong, and there can be the problem. But this can be a problem today too.
Regards
Pavel
regards, tom lane