Re: Schema variables - new implementation for Postgres 15

ne 7. 11. 2021 v 22:36 odesílatel Tomas Vondra <
tomas(dot)vondra(at)enterprisedb(dot)com> napsal:

> On 11/6/21 04:45, Pavel Stehule wrote:
> > Hi
> >
> > st 3. 11. 2021 v 14:05 odesílatel Tomas Vondra
> > <tomas(dot)vondra(at)enterprisedb(dot)com <mailto:tomas(dot)vondra(at)enterprisedb(dot)com>>
> > napsal:
> >
> > Hi,
> >
> > I took a quick look at the latest patch version. In general the patch
> > looks pretty complete and clean, and for now I have only some basic
> > comments. The attached patch tweaks some of this, along with a couple
> > additional minor changes that I'll not discuss here.
> >
> >
> > 1) Not sure why we need to call this "schema variables". Most objects
> > are placed in a schema, and we don't say "schema tables" for example.
> > And it's CREATE VARIABLE and not CREATE SCHEMA VARIABLE, so it's a
> bit
> > inconsistent.
> >
> >
> > Yes, there is inconsistency, but I think it is necessary. The name
> > "variable" is too generic. Theoretically we can use other adjectives
> > like session variables or global variables and the name will be valid.
> > But it doesn't describe the fundamentals of design. This is similar to
> > the package's variables from PL/SQL. These variables are global,
> > session's variables too. But the usual name is "package variables". So
> > schema variables are assigned to schemes, and I think a good name can be
> > "schema variables". But it is not necessary to repeat keyword schema in
> > the CREATE COMMAND.
> >
> > My opinion is not too strong in this case, and I can accept just
> > "variables" or "session's variables" or "global variables", but I am not
> > sure if these names describe this feature well, because still they are
> > too generic. There are too many different implementations of session
> > global variables (see PL/SQL or T-SQL or DB2).
> >
>
> OK. "Session variable" seems better to me, but I'm not sure how well
> that matches other databases. I'm not sure how much should we feel
> constrained by naming in other databases, though.
>

session variables is generic term - there are big differences already -
T-SQL versus PL/SQL or SQL+ or DB2

> >
> > The docs actually use "Global variables" in one place for some
> reason.
> >
> >
> > 2) I find this a bit confusing:
> >
> > SELECT non_existent_variable;
> > test=# select s;
> > ERROR: column "non_existent_variable" does not exist
> > LINE 1: select non_existent_variable;
> >
> > I wonder if this means using SELECT to read variables is a bad idea,
> and
> > we should have a separate command, just like we have LET (instead of
> > just using UPDATE in some way).
> >
> >
> > I am sure so I want to use variables in SELECTs. One interesting case is
> > using variables in RLS.
> >
>
> How much more complicated would it be without the SELECT?
>

It is not too complicated, just you want to introduce SELECT2. The sense of
session variables is to be used. Has no sense to hold a value on a server
without the possibility to use it.

Session variables can be used as global variables in PL/pgSQL. If you
cannot use it in SQL expressions, then you need to copy it to a local
variable, and then you can use it. That cannot work. This design is a
replacement of a untyped not nullable slow workaround based on GUC, there
is a necessity to use it in SQL.

> > I prefer to fix this error message to "column or variable ... does not
> > exist"
> >
>
> Not sure it's a good idea to make the error message more ambiguous. Most
> people won't use variables at all, and the message will be less clear
> for them.
>

Yes, there is new complexity. But it is an analogy with variables in
PL/pgSQL with all benefits and negatives. You don't want to use dynamic SQL
everywhere you use PL/pgSQL variables.

There are more cases than RLS in SQL

1. hold value in session (for interactive work or for non interactive
scripts). Sometimes you want to reuse value - we can now use CTE or
temporary tables. But in this case you have to store relation, you cannot
store value, that can be used as a query parameter.

2. allow safe and effective parametrization of SQL scripts, and copy value
from client side to server side (there is not risk of SQL injection).

run script with parameter -v xx=10

```
create temp variable xx as int;
set xx = :`xx`;
do $$
.. -- I can work with variable xx on server side

...

$$

This is complement to client side variables - the advantage is possibility
to use outside psql, the are type, and the metadata can be permanent.

3. you can share value by PL environments (and by possible clients). But
this sharing is secure - the rules are the same like holding value in an
table.

Session variables increase complexity a little bit, but increases
possibilities and comfort for developers that use databases directly. The
analogy with PL/pgSQL variables is well, jut you are not limited to
PL/pgSQL scope.

Regards

Pavel

>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>