Re: Schema variables - new implementation for Postgres 15

st 5. 4. 2023 v 19:20 odesílatel Greg Stark <stark@mit.edu> napsal:

On Sun, 26 Mar 2023 at 07:34, Julien Rouhaud <rjuju123@gmail.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.