On Wed, 3 Nov 2021 at 13:05, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote: > > 2) I find this a bit confusing: > > SELECT non_existent_variable; > test=# select s; > ERROR: column "non_existent_variable" does not exist > LINE 1: select non_existent_variable; > > I wonder if this means using SELECT to read variables is a bad idea, and > we should have a separate command, just like we have LET (instead of > just using UPDATE in some way). >
Hmm. This way of reading variables worries me for a different reason -- I think it makes it all too easy to break existing applications by inadvertently (or deliberately) defining variables that conflict with column names referred to in existing queries.
For example, if I define a variable called "relkind", then psql's \sv meta-command is broken because the query it performs can't distinguish between the column and the variable.
Similarly, there's ambiguity between alias.colname and schema.variablename. So, for example, if I do the following:
CREATE SCHEMA n; CREATE VARIABLE n.nspname AS int;
then lots of things are broken, including pg_dump and a number of psql meta-commands. I don't think it's acceptable to make it so easy for a user to break the system in this way.
Those are examples that a malicious user might use, but even without such examples, I think it would be far too easy to inadvertently break a large application by defining a variable that conflicted with a column name you didn't know about.
This is a valid issue, and it should be solved, or reduce a risk
I see two possibilities
a) easy solution can be implementation of other conflict strategy - variables have lower priority than tables with possibility to raise warnings if some identifiers are ambiguous. This is easy to implement, and with warning I think there should not be some unwanted surprises for developers. This is safe in meaning - no variable can break any query.
b) harder implementation (but I long think about it) can be implementation of schema scope access. It can be used for implementation of schema private objects. It doesn't solve the described issue, but it can reduce the risk of collision just for one schema.
Both possibilities can be implemented together - but the @b solution should be implemented from zero - and it is more generic concept, and then I prefer @a
