Subject: Re: proposal: schema variables

From: Dmitry Dolgov <[email-masked]>
Date: Wed, 13 Nov 2024 17:34:59 +0100

> On Sun, Nov 10, 2024 at 06:51:40PM GMT, Pavel Stehule wrote:
> ne 10. 11. 2024 v 17:19 odesílatel Pavel Stehule <[email-masked]>
> 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?

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.

---

From: Pavel Stehule <[email-masked]>
Date: Wed, 13 Nov 2024 19:18:40 +0100

>     -- we've got a variable b at the same time

I am sorry, but I am in very strong opposition  against this idea. Nobody
did reply to my questions, that can change my opinion.

1. This introduces possible inconsistency between LET syntax and SELECT
syntax.
What will be the syntax of LET?

LET var = var FROM var

PLpgSQL does something, and it is really strange, and source of some
unwanted bugs. See https://commitfest.postgresql.org/50/5044/

With current design I can support

LET var = expr with wars

or

LET var = (QUERY with vars)

It is perfectly consistent. The expressions will be expressions.

2. I don't know of any product in the world that introduced the same
requirement. So this syntax will be proprietary (SQL/PSM it really doesn't
require) and shock for any users that know other databases. Proprietary
syntax in this area far from syntaxes of other products is hell. Try to
explain to users the working with OUT variables of Postgres's procedures
and functions. And there is some deeper logic.

3. There is a simple solution - convention. Use your own schema like vars,
and use session variables in this schema, When this schema will not be on
the search path, then there is not a collision.
Variables living in schema. Nobody without CREATE right can create it. So
it is safe. Or use prefix in __ for variables in the search path.

4. this requirement introduces syntax inconsistency between plpgsql
variables and session variables - which breaks one goal of the patch -
introducing some global variables for plpgsql (and for all PL).

5. Using more variables and FROM clauses decrease readability of FROM clause

SELECT v1, v2, a, b, c FROM t1, v1, v2, t2, ...

6. Usually composite variables don't want to unpack. When you should use
FROM clause, then composite variables will be unpacked. Then all fields can
be possibly in collision with all other column name

Example

CREATE TYPE t1 AS (id int, name varchar)
CREATE TABLE tab(id int, name varchar)
CREATE VARIABLE var1 AS t1;

SELECT id, name, foo(var1) FROM tab, var1;

Now I have a collision in columns id, name, and everywhere I need to use
aliases. Without necessity to use var in FROM clause, I can just write

SELECT id, name, foo(var) FROM tab

and there is not any risk of collision

> VARIABLE(b), then all fine.

but same risk you have any time in plpgsql - all time. I don't remember any
bug report related to this issue.

> be great to elaborate on that -- maybe even with a PoC, to make a

There is not any problem with implementation. I see the main problem with
usability, and I really don't want to implement some like LET var = var
FROM var; I am sorry
It fixes one issue, but it increases possible collisions - so the variables
will be unusable.

Regards

Pavel

---

From: Pavel Stehule <[email-masked]>
Date: Thu, 14 Nov 2024 08:41:26 +0100

> VARIABLE(b), then all fine.

In this scenario you will get  a warning related to variable shadowing
(before you drop a column).

I think this issue can be partially similar to creating two equally named
tables in different schemas (both schemas are in search path). When you
drop one table, the query will work, but the result is different. It is the
same issue. The SQL has no concept of shadowing and on the base line it is
not necessary. But when you integrate SQL with some procedural code then
you should solve this issue (or accept). This issue is real, and it is in
every procedural enhancement of SQL that I know with the same syntax.  On
the other hand I doubt this is a real issue. The changes of system
catalogue are tested before production - so probably you will read a
warning about a shadowed variable, and probably you will get different
results, because variable b has the same value for all rows, and probably
will have different value than column b. I can imagine the necessity of
disabling this warning on production systems. Shadowing by self is not an
issue, probably, but it is a signal of code quality problems.

But this scenario is real, and then it is a question if the warning about
shadowed variables should be only optional and if it can be disabled. Maybe
not. Generally the shadowing is a strange concept - it is safeguard against
serious issues, but it should not be used generally and everywhere the
developer should rename the conflict identifiers.

Regards

Pavel

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 15 Nov 2024 05:45:58 +0100

> developer should rename the conflict identifiers.

There can be another example against usage of the FROM clause for
variables. Because it solves just one special case, but others are not
covered.

Theoretically, variables can have the same names as tables. The table
overshadows the variable, so it can work. But when somebody drops the
variable, then the query still can work. So requirement of usage variable
in FROM clause protects us just against drop column, but not against
dropping table. In Postgres the dropping table is possibly risky due
search_path (that introduces shadowing concept) without introduction
variables. There is a possibility of this issue, but how common is this
issue?

Regards

Pavel

---

From: Pavel Stehule <[email-masked]>
Date: Sat, 16 Nov 2024 07:10:31 +0100

> 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