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.


Regards

Pavel



>
> --
> greg
>