Thread

Re: proposal: schema variables

Pavel Stehule <pavel.stehule@gmail.com> — 2025-12-08T08:15:50Z

Hi

so 6. 12. 2025 v 12:29 odesílatel Jim Jones <jim.jones@uni-muenster.de>
napsal:

>
>
> On 05/12/2025 07:50, Pavel Stehule wrote:
> > yes, there was a bug, fixed
>
> > Both mentioned issues are related to the declared target of this
> > patchset - maximal reduction of the size.
>
> Nice, the memory is now being freed after a DROP VARIABLE and the tab
> completion for LET and DROP VARIABLE works:
>
> postgres=# CREATE TEMPORARY VARIABLE var AS text;
> CREATE VARIABLE
> postgres=# LET <TAB>
> var  x
> postgres=# LET var = repeat('🐘', 200000000);
> LET
> postgres=# SELECT pg_size_pretty(used_bytes)
> FROM pg_backend_memory_contexts
> WHERE name = 'session variables';
>  pg_size_pretty
> ----------------
>  763 MB
> (1 row)
>
> postgres=# DROP VARIABLE <TAB>
> var  x
> postgres=# DROP VARIABLE var;
> DROP VARIABLE
> postgres=# SELECT pg_size_pretty(used_bytes)
> FROM pg_backend_memory_contexts
> WHERE name = 'session variables';
>  pg_size_pretty
> ----------------
>  240 bytes
> (1 row)
>
>
> -- DROP VARIABLE IF EXISTS also works:
>
> postgres=# DROP VARIABLE IF EXISTS x;
> DROP VARIABLE
>
>
> Some comments and a few minor issues:
>
> == session_variables_ddl.sql ==
>
> 1) duplicate tests
>
> ...
> DROP VARIABLE IF EXISTS x;
> DROP VARIABLE IF EXISTS x;
> ...
>

fixed


>
> 2) Typos in some comments "should to fail" > "should fail"
>

fixed


>
> == Error messages ==
>
> 3) It is not possible to create a VIEW that depends on a session
> variable, which makes perfect sense.
>
> postgres=# CREATE VIEW v AS SELECT variable(var);
> ERROR:  session variable "var" cannot be referenced in a persistent object
>
> The error message is clear, but in case of TEMPORARY VIEWS it gets a bit
> misleading, since a TEMPORARY VIEW is not a persistent object:
>
> postgres=# CREATE TEMPORARY VIEW tv AS SELECT variable(var);
> ERROR:  session variable "var" cannot be referenced in a persistent object
>
> Perhaps something more generic? For instance:
>
> errmsg("session variable \"%s\" cannot be referenced in catalog
> objects", param->paramvarname)
>

changed like you proposed


>
> == ddl.sgml ==
>
> 4) There are invalid examples
>
> -- No schema qualified VARIABLE is supported:
>
> CREATE VARIABLE public.current_user_id AS integer;
>
> -- Only TEMPORARY VARIABLES are supported:
> CREATE VARIABLE var1 AS date;
>

fixed


>
> 5) The term "variable fence" is introduced and emphasised, but not
> described.
>

?? There is already

+++ b/doc/src/sgml/ddl.sgml
@@ -5676,6 +5676,17 @@ EXPLAIN SELECT count(*) FROM measurement WHERE
logdate &gt;= DATE '2008-01-01';
     The session variable holds value in session memory.  This value is
private
     to each session and is released when the session ends.
    </para>
+
+   <para>
+    In an query the session variable can be used only inside
+    <firstterm>variable fence</firstterm>. This is special syntax for
+    session variable identifier, and can be used only for session variable
+    identifier. The special syntax for accessing session variables removes
+    risk of collisions between variable identifiers and column names.
+<programlisting>
+SELECT VARIABLE(current_user_id);
+</programlisting>
+   </para>
   </sect1>



>
> 6) There is a slight repetition regarding the variable's isolation
>
> "This value is private to each session .."
> "The value of a session variable is local to the current session"
>
> I would write something along these lines:
>
> "Session variables are temporary database objects that can hold a value.
> A session variable can be created using the CREATE VARIABLE command and
> can only be accessed by its owner. The value of a session variable is
> stored in session memory and is private to each session. It is
> automatically released when the session ends.
>

done

>
> In a query, a session variable can only be referenced using the special
> <literal>VARIABLE(varname)</literal> syntax. This avoids any risk of
> collision between variable names and column names.
>

done


>
> You set the value of a session variable with the <command>LET</command>
> statement and retrieve it with <command>SELECT</command>:
>
> <programlisting>
> CREATE TEMPORARY VARIABLE var1 AS date;
> LET var1 = current_date;
> SELECT VARIABLE(var1);
>     var1
> ------------
>  2025-12-06
> (1 row)
> </programlisting>
>
> By default, retrieving a session variable returns
> <literal>NULL</literal> unless it has been set in the current session
> using the <command>LET</command> command. Session variables are not
> transactional: changes to their values persist even if the transaction
> is rolled back, similar to variables in procedural languages."
>

done


>
> == let.sgml ==
>
> 7) Invalid example (missing TEMP/TEMPORARY)
>
> CREATE VARIABLE myvar AS integer;
>

fixed


>
> 8) Typo in the Synopsis (TEMPORAL should be TEMPORARY):
>
> CREATE { TEMP | TEMPORAL } VARIABLE  [ IF NOT EXISTS ] name [ AS ]
> data_type
>

fixed


>
> 9) In the description it says "The CREATE VARIABLE command creates a
> temporal session variable.", but isn't the command now CREATE
> TEMP/TEMPORARY VARIABLE? Is it ok to remove the TEMPORARY in the
> description?
>

Although the TEMP clause in CREATE VARIABLE is mandatory now, I prefer to
look on this
like some temporary limit - so I don't libe to rename CREATE VARIABLE to
CREATE TEMP VARIABLE.

I changed this part to

  <para>
   The <command>CREATE VARIABLE</command> command creates a session
   variable. Currently only temporary session variables are supported,
   and then the keyword <literal>TEMPORARY</literal> is required.
  </para>


>
> 10) The description includes also info regarding SELECT and LET. Since
> this page is about CREATE TEMPORARY VARIABLE, I guess it is out of place?
>
>
I am sorry, I don't understand this point. Can you describe it?


For the current patchset I wrote initial support for transactional DDL for
session variables - patch 0010 and 0011.
Now, the DDL is blocked in read only transactions, parallel worker and
inside recovery.

Regards

Pavel




>
> Thanks!
>
> Best, Jim
>