Re: Schema variables - new implementation for Postgres 15

pá 16. 4. 2021 v 8:07 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> On Thu, Apr 15, 2021, at 10:42, Pavel Stehule wrote:
>
> *Attachments:*
>
> - schema-variables-v-execnode-2021-01.patch
> - schema-variables-v-utility-2021-01.patch
>
>
> Applications are currently know to be misusing
> set_config()+current_setting() to pass information in a session or
> transaction.
>
> Such users might be interested in using Schema variables as a better
> replacement.
>
> However, since set_config() is transactional, it can't be used as a
> drop-in replacement:
>
> + <para>
> + The value of a schema variable is local to the current session.
> Retrieving
> + a variable's value returns either a NULL or a default value, unless
> its value
> + is set to something else in the current session with a LET command.
> The content
> + of a variable is not transactional. This is the same as in regular
> variables
> + in PL languages.
> + </para>
>
> I think the "The content of a variable is not transactional." part is
> therefore a bad idea.
>
> Another pattern is to use TEMP TABLEs to pass around information in a
> session or transaction.
> If the LET command would be transactional, it could be used as a drop-in
> replacement for such use-cases as well.
>
> Furthermore, I think a non-transactional LET command would be insidious,
> since it looks like any other SQL command, all of which are transactional.
> (The ones that aren't such as REINDEX CONCURRENTLY will properly throw an
> error if run inside a transaction block.)
>
> A non-transactional LET command IMO would be non-SQL-idiomatic and
> non-intuitive.
>

I am sorry, but in this topic we have different opinions. The variables in
PLpgSQL are not transactional too (same is true in Perl, Python, ...).
Session variables in Oracle, MS SQL, DB2, MySQL are not transactional too.
My primary focus is PLpgSQL - and I would like to use schema variables as
global plpgsql variables (from PLpgSQL perspective) - that means in
Postgres's perspective session variables. But in Postgres, I have to write
features that will work with others PL too - PLPython, PLPerl, ...
Statement SET in ANSI/SQL standard (SQL/PSM) doesn't expect transactional
behaviour for variables too. Unfortunately SET keyword is used in Postgres
for GUC, and isn't possible to reuse without a compatibility break.

The PostgreSQL configuration is transactional, but it is a different
feature designed for different purposes. Using GUC like session variables
is just a workaround. It can be useful for some cases, sure. But it is not
usual behaviour. And for other cases the transactional behaviour is not
practical. Schema variables are not replacement of GUC, schema variables
are not replacement of temporal tables. There is a prepared patch for
global temp tables. I hope so this patch can be committed to Postgres 15.
Global temp tables fixes almost all disadvantages of temporary tables in
Postgres. So the schema variable is not a one row table. It is a different
creature - designed to support the server's side procedural features.

I have prepared a patch that allows non default transactional behaviour
(but this behaviour should not be default - I didn't design schema
variables as temp tables replacement). This patch increases the length of
the current patch about 1/4, and I have enough work with rebasing with the
current patch, so I didn't send it to commitfest now. If schema variables
will be inside core, this day I'll send the patch that allows transactional
behaviour for schema variables - I promise.

Regards

Pavel

> /Joel
>
>
>
>