Re: proposal: schema variables

Hi

út 19. 11. 2024 v 20:14 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
> I wrote POC of VARIABLE(varname) syntax support
>
> here is a copy from regress test
>
> SET session_variables_ambiguity_warning TO on;
> SET session_variables_use_fence_warning_guard TO on;
> SET search_path TO 'public';
> CREATE VARIABLE a AS int;
> LET a = 10;
> CREATE TABLE test_table(a int, b int);
> INSERT INTO test_table VALUES(20, 20);
> -- no warning
> SELECT a;
> a..
> ----
> 10
> (1 row)
>
> -- warning variable is shadowed
> SELECT a, b FROM test_table;
> WARNING: session variable "a" is shadowed
> LINE 1: SELECT a, b FROM test_table;
> ^
> DETAIL: Session variables can be shadowed by columns, routine's variables
> and routine's arguments with the same name.
> a | b..
> ----+----
> 20 | 20
> (1 row)
>
> -- no warning
> SELECT variable(a) FROM test_table;
> a..
> ----
> 10
> (1 row)
>
> ALTER TABLE test_table DROP COLUMN a;
> -- warning - variable fence is not used
> SELECT a, b FROM test_table;
> WARNING: session variable "a" is not used inside variable fence
> LINE 1: SELECT a, b FROM test_table;
> ^
> DETAIL: The collision of session variable' names and column names is
> possible.
> a | b..
> ----+----
> 10 | 20
> (1 row)
>
> -- no warning
> SELECT variable(a), b FROM test_table;
> a | b..
> ----+----
> 10 | 20
> (1 row)
>
> DROP VARIABLE a;
> DROP TABLE test_table;
> SET session_variables_ambiguity_warning TO DEFAULT;
> SET session_variables_use_fence_warning_guard TO DEFAULT;
> SET search_path TO DEFAULT;
>
>
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.

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

DO $$
BEGIN
RAISE NOTICE '% %', VARIABLE(foo), VARIABLE(public.foo);
END;
$$;

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.

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

This proposal doesn't reduce lines of code, but significantly reduces
possible impacts of introducing session variables to other parts of SQL.
Moreover, it allows us to separate some
work and related discussion into separate blocks - any block can be
implemented in different major pg releases.

I think a lot of users will be very happy just with step 1 and step 2, and
anything else can be discussed in future.

Is this plan acceptable?

Regards

Pavel

> Regards
>
> Pavel
>