public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jim Jones <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Dmitry Dolgov <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: DUVAL REMI <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: jian he <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: PegoraroF10 <[email protected]>
Subject: Re: proposal: schema variables
Date: Sat, 6 Dec 2025 12:29:48 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFj8pRAfQt13pacZgL_tQw8amczvLYEVDqKgjUNHDcQkwJT2_g@mail.gmail.com>
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>
<[email protected]>
<CAFj8pRAfQt13pacZgL_tQw8amczvLYEVDqKgjUNHDcQkwJT2_g@mail.gmail.com>
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
view thread (439+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: proposal: schema variables
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox