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