Message-ID: <8f7e3edc-ca20-46fc-8fa1-84aed33e1e46@uni-muenster.de>
Date: Sat, 6 Dec 2025 12:29:48 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: proposal: schema variables
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>, Dmitry Dolgov <9erthalion6@gmail.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>, Erik Rijkers <er@xs4all.nl>,
 Michael Paquier <michael@paquier.xyz>, Amit Kapila
 <amit.kapila16@gmail.com>, DUVAL REMI <REMI.DUVAL@cheops.fr>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 jian he <jian.universality@gmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>, PegoraroF10 <marcos@f10.com.br>
References: 
 <CAFj8pRDJ9cq00VYSHxs6LsoHNWjhYXyWWBtV6UgeWwhs0AHa9A@mail.gmail.com>
 <CAFj8pRAe1BN1iLoVaD4CLtMY4pg2XROVqmU8HngqBaFRhF4zCg@mail.gmail.com>
 <CAFj8pRBryCY4LqtrPu9E_mtZYkNfZb64-MetWE0232whdOLgjA@mail.gmail.com>
 <CAFj8pRCPjHT8xfnMHRroc_wx4wBT4ocqzjH--jqo0xHqec4qhA@mail.gmail.com>
 <CAFj8pRBTmSPUPET-Jo=REsLLx15kmHqYXmXwL2BKLeZjZqUUEg@mail.gmail.com>
 <CAFj8pRAOTQ5BkKvCFHkUE=HWzirZhVzxE5iugbcOsz=z5R0ogA@mail.gmail.com>
 <CAFj8pRDmCF05tVDbzkwL45WHLhdZnbkQLn9HA3RA-5LGu=wEXg@mail.gmail.com>
 <CAFj8pRCACo9_fwfe3ixgrRfThon+Nu26b1phd-ge7v5XCx4=9A@mail.gmail.com>
 <CAFj8pRDNBHEf2nrU3Xx3S-Vmtj+YUiGxc0CV78u7uwbxXP_acw@mail.gmail.com>
 <CAFj8pRAnWR5TV=kRu9L-xUUkxoeoWUXDKS_Qixfqj4YptjGUsA@mail.gmail.com>
 <CAFj8pRBHjSEE3A500MLyOPQogqaYGu=XVKvq4LnSnuHsFSxeJg@mail.gmail.com>
 <CAFj8pRDisL7v_t9-QYqxYOLvdQJCbZBY8hjzXNHMWxqEoVun4g@mail.gmail.com>
 <62426ced-0795-4191-876a-4fa16d277757@uni-muenster.de>
 <CAFj8pRAfQt13pacZgL_tQw8amczvLYEVDqKgjUNHDcQkwJT2_g@mail.gmail.com>
Content-Language: de-DE, en-GB
From: Jim Jones <jim.jones@uni-muenster.de>
In-Reply-To: 
 <CAFj8pRAfQt13pacZgL_tQw8amczvLYEVDqKgjUNHDcQkwJT2_g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/8f7e3edc-ca20-46fc-8fa1-84aed33e1e46%40uni-muenster.de>
Precedence: bulk


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