Re: Schema variables - new implementation for Postgres 15
| От | Pavel Stehule |
|---|---|
| Тема | Re: Schema variables - new implementation for Postgres 15 |
| Дата | |
| Msg-id | CAFj8pRDdpThkgJhXbB2bWaJGC5ymp7QPgM_sTVwyXxVg660zNA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Schema variables - new implementation for Postgres 15 (Tomas Vondra <tomas.vondra@enterprisedb.com>) |
| Список | pgsql-hackers |
ne 7. 11. 2021 v 22:36 odesílatel Tomas Vondra <tomas.vondra@enterprisedb.com> napsal:
On 11/6/21 04:45, Pavel Stehule wrote:
> Hi
>
> st 3. 11. 2021 v 14:05 odesílatel Tomas Vondra
> <tomas.vondra@enterprisedb.com <mailto:tomas.vondra@enterprisedb.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
В списке pgsql-hackers по дате отправления:
Следующее
От: Pavel StehuleДата:
Сообщение: Re: [PROPOSAL] new diagnostic items for the dynamic sql