Re: Schema variables - new implementation for Postgres 15

On Wed, Apr 5, 2023 at 1:58 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> st 5. 4. 2023 v 19:20 odesílatel Greg Stark <stark(at)mit(dot)edu> napsal:
>
>> On Sun, 26 Mar 2023 at 07:34, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>> >
>> > This feature can significantly increase log size, so it's disabled by
>> default.
>> > For testing or development environments it's recommended to enable it
>> if you
>> > use session variables.
>>
>> I think it's generally not practical to have warnings for valid DML.
>> Effectively warnings in DML are errors since they make the syntax just
>> unusable. I suppose it's feasible to have it as a debugging option
>> that defaults to off but I'm not sure it's really useful.
>>
>
> It is a tool that should help with collision detection. Without it, it
> can be pretty hard to detect it. It is similar to plpgsql's extra warnings.
>
>
>> I suppose it raises the question of whether session variables should
>> be in pg_class and be in the same namespace as tables so that
>> collisions are impossible. I haven't looked at the code to see if
>> that's feasible or reasonable. But this feels a bit like what happened
>> with sequences where they used to be a wholly special thing and later
>> we realized everything was simpler if they were just a kind of
>> relation.
>>
>
> The first patch did it. But at the end, it doesn't reduce conflicts,
> because usually the conflicts are between variables and table's attributes
> (columns).
>
> example
>
> create variable a as int;
> create table foo(a int);
>
> select a from foo; -- the "a" is ambiguous, variable "a" is shadowed
>
> This is a basic case, and the unique names don't help. The variables are
> more aggressive in namespace than tables, because they don't require be in
> FROM clause. This is the reason why we specify so variables are always
> shadowed. Only this behaviour is safe and robust. I cannot break any query
> (that doesn't use variables) by creating any variable. On second hand, an
> experience from Oracle's PL/SQL or from old PLpgSQL is, so unwanted
> shadowing can be hard to investigate (without some tools).
>
> PL/pgSQL doesn't allow conflict between PL/pgSQL variables, and SQL (now),
> and I think so it is best. But the scope of PLpgSQL variables is relatively
> small, so very strict behaviour is acceptable.
>
> The session variables are some between tables and attributes. The catalog
> pg_class can be enhanced about columns for variables, but it does a lot
> now, so I think it is not practical.
>
>>
>> I agree about shadowing schema variables. But is there no way to fix
that so that you can dereference the variable?
[Does an Alias work inside a procedure against a schema var?]
Does adding a schema prefix resolve it properly, so your example, I could
do:
SELECT schema_var.a AS var_a, a as COL_A from t;

Again, I like the default that it is hidden, but I can envision needing
both?

Regards, Kirk