On 2024-May-22, Dmitry Dolgov wrote:
> Yeah, that's a bummer. Interestingly enough, the db2 implementation of
> global session variables mechanism is mentioned as similar to what we
> have in the patch. But weirdly, the db2 documentation just states
> possibility of a resolution conflict for unqualified names, nothing
> else.
Perhaps the solution to all this is to avoid having the variables be
implicitly present in the range table of all queries. Instead, if you
need a variable's value, then you need to add the variable to the FROM
clause; and if you try to read from the variable and the name conflicts
with that of a column in one of the tables in the FROM clause, then you
get an error that the name is ambiguous and invites to qualify it.
Like, for instance,
create table lefttab (a int, b int);
create table righttab (c int, d int, b int);
=# select b from lefttab, righttab;
ERROR: column reference "b" is ambiguous
LÍNEA 1: select b from lefttab, righttab;
^
but this works fine because there's no longer an ambiguity:
select lefttab.b from lefttab, righttab;
b
───
(0 filas)
Nothing breaks if you create new variables, because your queries won't
see them until you explicitly request them. And if you add add columns
to either tables or variables, it's possible that some queries would
start having ambiguous references, in which case they'll just stop
working until you disambiguate by editing the query.
Now, Pavel has been saying that variables are simple and cannot break
queries (because they're always shadowed), which is why they're always
implicitly visible to all queries[1]; but maybe that's a mistake.
[1] https://postgr.es/m/CAFj8pRA2P7uaFGpFJxVHrHFtizBCN41J00BrEotspdD+urGBLQ@mail.gmail.com
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)