Re: proposal: schema variables

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;
...

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

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

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

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

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.

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.

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."

== let.sgml ==

7) Invalid example (missing TEMP/TEMPORARY)

CREATE VARIABLE myvar AS integer;

8) Typo in the Synopsis (TEMPORAL should be TEMPORARY):

CREATE { TEMP | TEMPORAL } VARIABLE  [ IF NOT EXISTS ] name [ AS ] data_type

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?

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?


Thanks!

Best, Jim