Re: Schema variables - new implementation for Postgres 15

čt 15. 4. 2021 v 10:42 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

> Hi,
>
> I am returning back to implementation of schema variables. The schema
> variables can be used as an alternative to package variables (Oracle's
> PL/SQL or ADA). The schema variables can be used as fast and safe storage
> of session information for RLS too.
>
> The previous implementation had not cleanly implemented execution of the
> LET statement. It was something between query and utility, and although it
> was working - it was out of Postgres concept (with different implementation
> of queries and utilities).
>
> I totally rewrote the implementation of the LET statement. I prepared two
> variants:
>
> First variant is based on the introduction of the new command type CMD_LET
> and new very small executor node SetVariable (this is a very very reduced
> analogy of ModifyTable node). The code is consistent and what is important
> - the LET statement can be prepared. The execution is relatively fast from
> PLpgSQL too. Without any special support the execution has the same speed
> like non simple queries. The statement reuses  an execution plan, but
> simple execution is not supported.
>
> Second variant is implemented like a classic utility command. There is not
> any surprise. It is shorter, simple, but the LET statement cannot be
> prepared (this is the limit of all utility statements). Without special
> support in PLpgSQL the execution is about 10x slower than the execution of
> the first variant. But there is a new possibility of using the main parser
> from PLpgSQL (implemented by Tom for new implementation of assign statement
> in pg 14), and then this support in plpgsql requires only a few lines).
> When the statement LET is explicitly supported by PLpgSQL, then execution
> is very fast (the speed is comparable with the speed of the assign
> statement) - it is about 10x faster than the first variant.
>
> I tested code
>
> do $$
> declare x int ;
> begin
>   for i in 1..1000000
>   loop
>     let ooo = i;
>   end loop;
> end;
> $$;
>
> variant 1 .. 1500 ms
> variant 2 with PLpgSQL support .. 140 ms
> variant 2 without PLpgSQL support 9000 ms
>
> The slower speed of the first variant from PLpgSQL can be fixed. But for
> this moment, the speed is good enough. This is the worst case, because in
> the first variant LET statement cannot use optimization for simple query
> evaluation (now).
>
> Now I think so implementation is significantly cleaner, and I hope so it
> will be more acceptable for committers.
>
> I am starting a new thread, because this is a new implementation, and
> because I am sending two alternative implementations of one functionality.
>
> Comments, notes, objections?
>
>
I am sending only one patch and I assign this thread to commitfest
application

Regards

Pavel


> Regards
>
> Pavel
>
>
>