Re: proposal: schema variables

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.