Date: Wed, 21 May 2025 17:22:15 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>,
	Laurenz Albe <laurenz.albe@cybertec.at>,
	Erik Rijkers <er@xs4all.nl>, Michael Paquier <michael@paquier.xyz>,
	Amit Kapila <amit.kapila16@gmail.com>,
	DUVAL REMI <REMI.DUVAL@cheops.fr>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	jian he <jian.universality@gmail.com>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: proposal: schema variables
Message-ID: <aC5EB8Q0p0KF_uvg@momjian.us>
References: 
 <CAFj8pRDJ9cq00VYSHxs6LsoHNWjhYXyWWBtV6UgeWwhs0AHa9A@mail.gmail.com>
 <CAFj8pRBPXTcw_3fpKtgVthV2+9rZGhxitZ40DnAwCrK601TZZg@mail.gmail.com>
 <ndtfl4tsnpkb7m7hwvnmlpsascpgd3a7xvjmjhtxffsbrgygtm@4du6zsmnnwq5>
 <CAFj8pRAu4XvNCGu1751t=2YEqLqTjDA3FavMExm2S0KYQq=DdQ@mail.gmail.com>
 <CAFj8pRAsEoeZv0HEnA8CKgFKDSQ-wYw18Os1vdksWCV7ez2bVw@mail.gmail.com>
 <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65>
 <CAFj8pRBoWPDTOwn5FmMzc+1qiopw+N04U26nviOdF61fs8A2wQ@mail.gmail.com>
 <stckyvkl4yyzvgjsaawojs3xikke7mmds5bhv7l7qerclywywk@h4v4n43xm6u2>
 <CAFj8pRB_E1GM_YGT-ti4bXka6mhLdAAFeTe+BHgHFYC+qb-76g@mail.gmail.com>
 <CAFj8pRCE=zkECNS9E-eLv9tbyUqCR-txx7eZp+GHF1_LKFUAOg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAFj8pRCE=zkECNS9E-eLv9tbyUqCR-txx7eZp+GHF1_LKFUAOg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aC5EB8Q0p0KF_uvg%40momjian.us>
Precedence: bulk

On Wed, May 21, 2025 at 09:12:54AM +0200, Pavel Stehule wrote:
> Last discussion is related to reducing the size of the session variable patch
> set.
> 
> I have an idea to use variable's fencing more aggressively from the start, and
> then we can reduce it in future. This should not break issues with
> compatibility and doesn't need some like version flags.
> 
> The real problem of proposed session variables is possible collisions between
> session variables identifiers and table or columns identifiers. I designed some
> tools to minimize the risk of unwanted collisions, but these tools increase the
> size of code and don't reduce the complexity of the patch and tests. The
> proposed change probably doesn't reduce a lot of code, but can reduce some
> tests, and mainly possible risk of some unwanted impact - at the end it can be
> less work for reviewers and less stress for committers - and the implementation
> can be divided to allone workable following steps.

Yes, I remember the discussions about how the creation of server
variables could break existing queries.  Our scoping rules are already
complex, so adding another scope would add a lot of complexity.

> Step 1
> =====
> 
> So the main change is the hard requirement for usage variable's fence
> everywhere where collisions are possible - and then in the first step, the
> collisions will not be possible, and then we don't need it to solve, and we
> don't need to test it.
> 
> CREATE VARIABLE public.foo AS int;
> LET foo = 10;
> SELECT VARIABLE(foo);

Yes, I can see how adding fencing like VARIABLE() would simplify things.

> Step 2
> =====
> Necessity of usage variable fencing in PL/pgSQL can be a problem for migration
> from PL/SQL. But this can be solved separately by using SPI params hooks -
> similar to how PL/pgSQL works with PL/pgSQL variables. In this step we can push
> optimization for fast execution of the LET statement or optimization of usage
> variables in queries.

Yes, there is already going to be migration requirements in moving from
PL/SQL to PL/pgSQL, so the requirement to add VARIABLE() seems minimal.

> After this step will be possible:
> 
> DO $$
> BEGIN
>   RAISE NOTICE '% %', foo, VARIABLE(public.foo);
> END;
> $$;
> 
> SELECT VARIABLE(foo);
> 
> No other visible change in this step. WIth this step the people who do
> migration form Oracle and PL/pgSQL developers will be very happy. They don't
> need more. There can be collisions, but the collisions can be limited just to
> PL/pgSQL scope, and we can use already implemented mechanisms.
> 
> Step 3
> =====
> We can talk in future about less requirement of usage variable fencing in
> queries. This needs to introduce some form of detection collisions and how they
> should be solved (outside PL/pgSQL).
> We can talk about other features like temporal, default values, transactional,
> etc ...

I feel that if we haven't found a good solution to this in 13 years, we
should assume it is unsolvable and just accept an imperfect solution.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.