Horton

Re: Schema variables - new implementation for Postgres 15

Dmitry Dolgov <9erthalion6@gmail.com>

View thread
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Pavel Stehule <pavel.stehule@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Sergey Shinderuk <s.shinderuk@postgrespro.ru>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Julien Rouhaud <rjuju123@gmail.com>, dean.a.rasheed@gmail.com, er@xs4all.nl, joel@compiler.org, pgsql-hackers@lists.postgresql.org
Date: 2024-05-22T14:14:26Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow underscores in integer and numeric constants.

  2. Remove special outfuncs/readfuncs handling of RangeVar.catalogname.

  3. Remove extra space from dumped ALTER DEFAULT PRIVILEGES.

  4. Create FKs properly when attaching table as partition

  5. psql: improve tab-complete's handling of variant SQL names.

> On Wed, May 22, 2024 at 02:37:49PM +0200, Peter Eisentraut wrote:
> On 18.05.24 13:29, Alvaro Herrera wrote:
> > I want to note that when we discussed this patch series at the dev
> > meeting in FOSDEM, a sort-of conclusion was reached that we didn't want
> > schema variables at all because of the fact that creating a variable
> > would potentially change the meaning of queries by shadowing table
> > columns.  But this turns out to be incorrect: it's_variables_  that are
> > shadowed by table columns, not the other way around.
>
> But that's still bad, because seemingly unrelated schema changes can make
> variables appear and disappear.  For example, if you have
>
> SELECT a, b FROM table1
>
> and then you drop column b, maybe the above query continues to work because
> there is also a variable b.  Or maybe it now does different things because b
> is of a different type.  This all has the potential to be very confusing.

Yeah, that's a bummer. Interestingly enough, the db2 implementation of
global session variables mechanism is mentioned as similar to what we
have in the patch. But weirdly, the db2 documentation just states
possibility of a resolution conflict for unqualified names, nothing
else.

There was extensive discussion about this problem early in the thread,
and one alternative is to use some sort of special syntax every time
when working with a variable to clear any ambiguity [1]. It's more
verbose, has to be careful to not block some useful syntax for other
stuff, etc. But as Pavel said:

> The different syntax disallows any collision well, it is far to what is
> more usual standard in this area. And if we introduce special syntax, then
> there is no way back. We cannot use :varname - this syntax is used already,
> but we can use, theoretically, @var or $var. But, personally, I don't want
> to use it, if there is possibility to do without it.

It seems to me there is no other possibility to resolve those ambiguity
issues.

[1]: https://www.postgresql.org/message-id/CAFj8pRD03hwZK%2B541KDt4Eo5YuC81CBBX_P0Sa5A7g5TQFsTww%40mail.gmail.com