Horton

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Dmitry Dolgov <9erthalion6@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-22T18:33:46Z
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.

st 22. 5. 2024 v 14:37 odesílatel Peter Eisentraut <peter@eisentraut.org>
napsal:

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

In the described case, the variable's shadowing warning will be raised.

There are more cases where not well designed changes (just with tables) can
break queries or change results. Adding columns can be a potential risk,
creating tables or dropping tables (when the search path contains more
schemas) too.

Good practice is using well designed names and almost all use aliases or
labels, and it is one way to minimize real risks. Personally I prefer a
very strict mode that disallows shadowing, conflicts, ... but on second
hand, for some usual work this strict mode can be boring, so we should find
some good compromise.

Regards

Pavel