Re: Schema variables - new implementation for Postgres 15

 

As far as I can see now, it's a major design flaw that could keep the
patch from being accepted. Fortunately there are few good proposals how
to address this, folks are genuinely trying to help. What do you think
about trying some of them out, as an alternative approach, to compare
functionality and user experience?

It is a design flaw of SQL. The issue we talk about is the generic property of SQL, and then you cannot fix it.

I thought about possibility to introduce dedicated function

svalue(regvariable) returns any - with planner support

and possibility to force usage of this function. Another possibility is using some simple dedicated operator (syntax) for force using of variables

so theoretically this can looks like:

set strict_usage_of_session_variables to on;

SELECT * FROM tab WHERE a = svalue('myvar.var');

or

SELECT * FROM tab WHERE a = @ myvar.var;

This can be really safe. Personally It is not my cup of tea, but I can live it (and this mode can be default).

Theoretically we can limit usage of variables just for PL/pgSQL. It can reduce risks too, but it breaks usage variables for parametrization of DO blocks (what is my primary motivation), but it can be good enough to support migration from PL/SQL.

 

In the meantime I'm afraid I have to withdraw "Ready for committer"
status, sorry. I've clearly underestimated the importance of variables
shadowing, thanks Alvaro and Peter for pointing out some dangerous
cases. I still believe though that the majority of the patch is in a
good shape and the question about variables shadowing is the only thing
that keeps it from moving forward.

I understand.

I'll try to recapitulate my objections against proposed designs

a) using syntax like MS - DECLARE command and '@@' prefix - it is dynamic, so there is not possibility of static check. It is not joined with schema, so there are possible collisions between variables and and the end the variables are named like @@mypackage_myvar - so some custom naming convention is necessary too. There is not possibility to set access rights.

b) using variables like MySQL - first usage define it, and access by '@' prefix. It is simple, but without possibility of static check. There is not possibility to set access rights.

c) using variables with necessity to define it in FROM clause. It is safe, but it can be less readable, when you use more variables, and it is not too readable, and user friendly, because you need to write FROM. And can be messy, because you usually will use variables in queries, and it is introduce not relations into FROM clause. But I can imagine this mode as alternative syntax, but it is very unfriendly and not intuitive (I think). More probably it doesn't fast execution in simple expression execution mode.

d) my proposal - there is possibility of collisions, but consistent with naming of database objects, allows set of access rights, allows static analyze, consistent with PL/pgSQL and similar to PL/pgSQL.

There is not any other possibility. Any time this is war between be user friendly, be readable, be correctly - but there is not perfect solution, because just SQL is not perfect. Almost all mentioned objections against proposed variables are valid just for tables and columns.

Regards

Pavel