Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers

> On Tue, May 28, 2024 at 05:18:02PM GMT, Pavel Stehule wrote:
>
> I propose another variants. First we can introduce pseudo function VAR( ).
> The argument should be session variables. The name of this function can be
> pgvar, globvar, ... We can talk about good name, it should not be too long,
> but it is not important now. The VAR() function will be pseudo function
> like COALESCE, so we can easily to set correct result type.

So, the purpose of the function would be only to verify that the argument is a
session variable? That seems to be a very light payload, which looks a bit
awkward.

Out of those options you propose I think the first one is the
most straightforward one, but...

> Alvaro Herrera:
> > Perhaps the solution to all this is to avoid having the variables be
> > implicitly present in the range table of all queries.  Instead, if you
> > need a variable's value, then you need to add the variable to the FROM
> > clause;

The more I think about this, the more I like this solution. Marking
which variables are available to the query this way, and using established
patterns for resolving ambiguity actually looks intuitive to me. Now I know,
you've got strong objections:

> I don't like this. Sure, this fixes the problem with collisions, but then
> we cannot talk about variables. When some is used like a table, then it
> should be a table. I can imagine memory tables, but it is a different type
> of object. Table is relation, variable is just value. Variables should not
> have columns, so using the same patterns for tables and variables has no
> sense. Using the same catalog for variables and tables. Variables just hold
> a value, and then you can use it inside a query without necessity to write
> JOIN. Variables are not tables, and then it is not too confusing so they
> are not transactional and don't support more rows, more columns.

A FROM clause could contain a function returning a single value, nobody
finds it confusing. And at least to me it's not much different from having a
session variable as well, what do you think?

> 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).

The proposal from Wolfgang to have a short-cut and not add FROM in case there
is no danger of ambiguity seems to resolve that.

> More probably it doesn't fast execution in simple expression execution mode.

Could you elaborate more, what do you mean by that? If the performance
overhead is not prohibitive (which I would expect is the case), having better
UX for a new feature usually beats having better performance.

> It looks odd - It is not intuitive, it introduces new inconsistency inside
> Postgres, or with solutions in other databases. No other database has a
> similar rule, so users coming from Oracle, Db2, or MSSQL, Firebird will be
> confused. Users that use PL/pgSQL will be confused.

Session variables are not part of the SQL standard, and maintaining
consistency with other databases is a questionable goal. Since it's a new
feature, I'm not sure what you mean by inconsistency inside Postgres itself.

I see that the main driving case behind this patch is to help with
migrating from other databases that do have session variables. Going with
variables in FROM clause, will not make a migration much harder -- some of the
queries would have to modify the FROM part, and that's it, right? I could
imagine it would be even easier than adding VAR() everywhere.