Re: proposal: schema variables

st 13. 11. 2024 v 17:35 odesílatel Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
napsal:

> > On Sun, Nov 10, 2024 at 06:51:40PM GMT, Pavel Stehule wrote:
> > ne 10. 11. 2024 v 17:19 odesílatel Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com>
> > napsal:
> > I thought a lot of time about better solutions for identifier collisions
> > and I really don't think so there is some consistent user friendly
> syntax.
> > Personally I think there is an easy already implemented solution -
> > convention - just use a dedicated schema for variables and this schema
> > should not be in the search path. Or use secondary convention - like
> using
> > prefix "__" for session variables. Common convention is using "_" for
> > PLpgSQL variables. I searched how this issue is solved in other
> databases,
> > or in standard, and I found nothing special. The Oracle and SQL/PSM has a
> > concept of visibility - the variables are not visible outside packages or
> > modules, but Postgres has nothing similar. It can be emulated by a
> > dedicated schema without inserting a search path, but it is less strong.
> >
> > I think we can introduce an alternative syntax, that will not be user
> > friendly or readable friendly, but it can be without collisions - or can
> > decrease possible risks.
> >
> > It is nothing new - SQL does it with old, "new" syntax of inner joins, or
> > in Postgres we can
> >
> > where salary < 40000
> >
> > or
> >
> > where pg_catalog.int4lt(salary, 40000);
> >
> >
> > or some like we use for operators OPERATOR(*schema*.*operatorname*)
> >
> > So introducing VARIABLE(schema.variablename) syntax as an alternative
> > syntax for accessing variables I really like. I strongly prefer to use
> this
> > as only alternative (secondary) syntax, because I don't think it is
> > friendly syntax or writing friendly, but it is safe, and I can imagine
> > tools that can replace generic syntax to this special, or that detects
> > generic syntax and shows some warning. Then users can choose what they
> > prefer. Two syntaxes - generic and special can be good enough for all -
> and
> > this can be perfectly consistent with current Postgres.
>
> As far as I recall, last time this topic was discussed in hackers, two
> options were proposed: the one with VARIABLE(name), what you mention
> here; and another one with adding variables to the FROM clause. The
> VARIABLE(...) syntax didn't get much negative feedback, so I guess why
> not -- if you find it fitting, it would be interesting to see the
> implementation.
>
> I'm afraid it should not be just an alternative syntax, but the only one
> allowed, because otherwise I don't see how scenarious like "drop a
> column with the same name" could be avoided. As in the previous thread:
>
> -- we've got a variable b at the same time
> SELECT a, b FROM table1;
>
> Then dropping the column b, but everything still works beause the
> variable b got silently picked up. But if it would be required to say
> VARIABLE(b), then all fine.
>
> And to make sure we're on the same page, could you post couple of
> examples from curretly existing tests in the patch, how are they going
> to look like with this proposal?
>

What do you think about the following design? I can implement a warning
"variable_usage_guard" when the variable is accessed without using
VARIABLE() syntax. We can discuss later if this warning can be enabled by
default or not. There I am open to any variant.

So for variable public.a and table public.foo(a, b)

I can write

LET a = 10; -- there is not possible collision
LET a = a + 1; -- there is not possible collision, no warning

SELECT a, b FROM foo; -- there is a collision - and warning "variable a is
shadowed"
SELECT VARIABLE(a), b FROM foo; -- no collision, no warning

After ALTER TABLE foo DROP COLUMN a;

SELECT a, b FROM foo; -- possible warning "the usage in variable without
safe syntax",
SELECT VARIABLE(a), b FROM foo; -- no warning

I think this design can be good for all. variable_usage_guard can be
enabled by default. If somebody uses conventions for collision protection,
then he can safely disable it.

Comments, notes?

Regards

Pavel

> About adding variables to the FROM clause. Looks like this option was
> quite popular, and you've mentioned some technical challenges
> implementing that. If you'd like to go with another approach, it would
> be great to elaborate on that -- maybe even with a PoC, to make a
> convincing point here.
>