Horton

Re: Schema variables - new implementation for Postgres 15

Joel Jacobson <joel@compiler.org>

View thread
From: "Joel Jacobson" <joel@compiler.org>
To: pgsql-hackers@lists.postgresql.org
Date: 2021-04-16T06:06:48Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow underscores in integer and numeric constants.

  2. Remove special outfuncs/readfuncs handling of RangeVar.catalogname.

  3. Remove extra space from dumped ALTER DEFAULT PRIVILEGES.

  4. Create FKs properly when attaching table as partition

  5. psql: improve tab-complete's handling of variant SQL names.

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.

/Joel