public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Stehule <[email protected]>
To: Haritabh Gupta <[email protected]>
Cc: [email protected]
Subject: Re: proposal: schema variables
Date: Fri, 3 Apr 2026 21:10:13 +0200
Message-ID: <CAFj8pRBH629MNqris2U4wyocNd0na5wv6czeUHRoTKYEH90-yA@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRDuhKBC2n6GsukvR24jSD7WOuMcFiNznW8XdjOgKbkFQA@mail.gmail.com>
References: <CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com>
<CAFj8pRDLL-srue=m+je26vFrMzwETWwOfsEVNwr7=_KhuWA1bQ@mail.gmail.com>
<177261853144.1094601.11795093672679754617.pgcf@coridan.postgresql.org>
<CAFj8pRBozszOrD+5c21TucP-VLG_ra+erXh3OgBy0c0dYsR6KQ@mail.gmail.com>
<CAFj8pRDsL38udq5vQv_uZEj3XgqW7D04g00OkHi6JHfFJvh0tA@mail.gmail.com>
<CAFj8pRDo4o2pyO6SpihKbT0gTLVmDdOJGLCBmZW9GTc0++MzOw@mail.gmail.com>
<CAFj8pRA7Nzzd9dEeTB=aSeN4YehjHudQOs1i6RcOV8P9pZigrg@mail.gmail.com>
<CAFj8pRDqm_EC39zhJdZ_zCvyaLacJaQWkGKnO5HQqWadni0ZfA@mail.gmail.com>
<CAFj8pRDMW554UDMwf5_BOh6JPDEVWL=UH1vGc7BAatcok0ihQg@mail.gmail.com>
<CAFj8pRDyMrtjoMYup+MMoUPQcC+sqiOU_tn1rVkwMs61R=iRBw@mail.gmail.com>
<CAFj8pRDuhKBC2n6GsukvR24jSD7WOuMcFiNznW8XdjOgKbkFQA@mail.gmail.com>
Hi
fix warning reported by cfbot
Regards
Pavel
Attachments:
[text/x-patch] v20260403-2-0001-CREATE-VARIABLE-DROP-VARIABLE.patch (33.8K, 3-v20260403-2-0001-CREATE-VARIABLE-DROP-VARIABLE.patch)
download | inline diff:
From 76ce9fa65876e3b859364a9bab74e3aa505801ae Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Wed, 19 Nov 2025 19:36:07 +0100
Subject: [PATCH 01/11] CREATE VARIABLE, DROP VARIABLE
Introduce simple (non transactional) implementation of basic DDL statements.
Because only temporary variables are allowed, we don't need a dedicated catalog.
This is the most reduced version of declared session variables implementation.
Main goal is introduction of basic functionality and reducing the size of the patch
to maximum. This patch is designed to be possible to move to catalog based
implementation without compatibility break.
Some details about different implementations of session variables
https://wiki.postgresql.org/wiki/Implementation_of_declarative_catalog_session_variables
---
doc/src/sgml/ddl.sgml | 20 ++
doc/src/sgml/glossary.sgml | 15 ++
doc/src/sgml/ref/allfiles.sgml | 2 +
doc/src/sgml/ref/create_variable.sgml | 133 +++++++++++
doc/src/sgml/ref/drop_variable.sgml | 84 +++++++
doc/src/sgml/reference.sgml | 2 +
src/backend/commands/Makefile | 1 +
src/backend/commands/dropcmds.c | 1 +
src/backend/commands/meson.build | 1 +
src/backend/commands/session_variable.c | 216 ++++++++++++++++++
src/backend/parser/gram.y | 55 ++++-
src/backend/tcop/utility.c | 26 +++
src/bin/psql/tab-complete.in.c | 10 +-
src/include/commands/session_variable.h | 25 ++
src/include/nodes/parsenodes.h | 23 ++
src/include/parser/kwlist.h | 1 +
src/include/tcop/cmdtaglist.h | 2 +
.../expected/session_variables_ddl.out | 43 ++++
src/test/regress/parallel_schedule | 2 +-
.../regress/sql/session_variables_ddl.sql | 56 +++++
src/tools/pgindent/typedefs.list | 4 +
21 files changed, 715 insertions(+), 7 deletions(-)
create mode 100644 doc/src/sgml/ref/create_variable.sgml
create mode 100644 doc/src/sgml/ref/drop_variable.sgml
create mode 100644 src/backend/commands/session_variable.c
create mode 100644 src/include/commands/session_variable.h
create mode 100644 src/test/regress/expected/session_variables_ddl.out
create mode 100644 src/test/regress/sql/session_variables_ddl.sql
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 747f929aee3..17618813582 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5936,6 +5936,26 @@ SELECT ... FROM GRAPH_TABLE (myshop MATCH (IS person WHERE name = '...')-[]->...
</para>
</sect1>
+ <sect1 id="ddl-session-variables">
+ <title>Session Variables</title>
+
+ <indexterm zone="ddl-session-variables">
+ <primary>Session variables</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ </indexterm>
+
+ <para>
+ Session variables are temporary database objects that can hold a value.
+ A session variable can be created by the <command>CREATE VARIABLE</command>
+ 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.
+ </para>
+ </sect1>
+
<sect1 id="ddl-others">
<title>Other Database Objects</title>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index 113d7640626..b6b80d446eb 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1743,6 +1743,21 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-session-variable">
+ <glossterm>Session variable</glossterm>
+ <glossdef>
+ <para>
+ A temporal database object that holds a value in session memory. This
+ value is private to each session and is released when the session ends.
+ The default value of the session variable is null. Read or write access
+ to session variables is allowed only to owner (creator).
+ </para>
+ <para>
+ For more information, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-shared-memory">
<glossterm>Shared memory</glossterm>
<glossdef>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index e1a56c36221..35d485f5bc4 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -101,6 +101,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY createType SYSTEM "create_type.sgml">
<!ENTITY createUser SYSTEM "create_user.sgml">
<!ENTITY createUserMapping SYSTEM "create_user_mapping.sgml">
+<!ENTITY createVariable SYSTEM "create_variable.sgml">
<!ENTITY createView SYSTEM "create_view.sgml">
<!ENTITY deallocate SYSTEM "deallocate.sgml">
<!ENTITY declare SYSTEM "declare.sgml">
@@ -150,6 +151,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropType SYSTEM "drop_type.sgml">
<!ENTITY dropUser SYSTEM "drop_user.sgml">
<!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml">
+<!ENTITY dropVariable SYSTEM "drop_variable.sgml">
<!ENTITY dropView SYSTEM "drop_view.sgml">
<!ENTITY end SYSTEM "end.sgml">
<!ENTITY execute SYSTEM "execute.sgml">
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
new file mode 100644
index 00000000000..4e8c1940252
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,133 @@
+<!--
+doc/src/sgml/ref/create_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createvariable">
+ <indexterm zone="sql-createvariable">
+ <primary>CREATE VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>defining</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>CREATE VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE VARIABLE</refname>
+ <refpurpose>define a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Description</title>
+
+ <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>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a session variable's value returns NULL, unless its value is set to
+ something else in the current session with a <command>LET</command> command.
+ The content of a session variable is not transactional. This is the same as
+ regular variables in procedural languages.
+ </para>
+
+ <para>
+ Session variables are retrieved by the <command>SELECT</command>
+ command. Their value is set with the <command>LET</command> command.
+ </para>
+
+ <para>
+ Session variables cannot be used in views or in SQL functions using
+ SQL-conforming style syntax.
+ </para>
+
+ <note>
+ <para>
+ Session variables can be <quote>shadowed</quote> by other identifiers.
+ For details, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </note>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry id="sql-createvariable-name">
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createvariable-data_type">
+ <term><replaceable class="parameter">data_type</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the data type of the session
+ variable. Only buildin scalar data types are allowed. Arrays or composite
+ types are not allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Use the <command>DROP VARIABLE</command> command to remove a session
+ variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Create an date session variable <literal>var1</literal>:
+<programlisting>
+CREATE TEMPORARY VARIABLE var1 AS date;
+</programlisting>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 00000000000..e8517a78200
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,84 @@
+<!--
+doc/src/sgml/ref/drop_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-dropvariable">
+ <indexterm zone="sql-dropvariable">
+ <primary>DROP VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>removing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP VARIABLE</refname>
+ <refpurpose>remove a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP VARIABLE <replaceable class="parameter">name</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DROP VARIABLE</command> removes a session variable.
+ A session variable can only be removed by its owner or a superuser.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To remove the session variable <literal>var1</literal>:
+
+<programlisting>
+DROP VARIABLE var1;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>DROP VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 674ac17e82c..342a7afb517 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -129,6 +129,7 @@
&createType;
&createUser;
&createUserMapping;
+ &createVariable;
&createView;
&deallocate;
&declare;
@@ -178,6 +179,7 @@
&dropType;
&dropUser;
&dropUserMapping;
+ &dropVariable;
&dropView;
&end;
&execute;
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index c10fdba2bbb..7739f3897ed 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -55,6 +55,7 @@ OBJS = \
seclabel.o \
sequence.o \
sequence_xlog.o \
+ session_variable.o \
statscmds.o \
subscriptioncmds.o \
tablecmds.o \
diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c
index 88a2df65c69..d3d3a4ad52b 100644
--- a/src/backend/commands/dropcmds.c
+++ b/src/backend/commands/dropcmds.c
@@ -22,6 +22,7 @@
#include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "commands/defrem.h"
+#include "commands/session_variable.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
#include "utils/acl.h"
diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build
index 90c7e37a429..e8a018bdd2a 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -43,6 +43,7 @@ backend_sources += files(
'seclabel.c',
'sequence.c',
'sequence_xlog.c',
+ 'session_variable.c',
'statscmds.c',
'subscriptioncmds.c',
'tablecmds.c',
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
new file mode 100644
index 00000000000..a865a4c10c4
--- /dev/null
+++ b/src/backend/commands/session_variable.c
@@ -0,0 +1,216 @@
+/*-------------------------------------------------------------------------
+ *
+ * session_variable.c
+ * session variable creation/manipulation commands
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/commands/session_variable.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "catalog/pg_language.h"
+#include "catalog/pg_type.h"
+#include "commands/session_variable.h"
+#include "miscadmin.h"
+#include "parser/parse_type.h"
+#include "storage/proc.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+
+/*
+ * The session variables are stored in the backend's private memory (data,
+ * metadata) in the dedicated memory context SVariableMemoryContext in binary
+ * format. They are stored in the "sessionvars" hash table, whose key is the
+ * name of the variable.
+ *
+ * Only owner (creator) can access the session variables. Because there is
+ * not catalog support, there is not possibility to track dependecies, and
+ * then only buildin types.
+ */
+typedef struct SVariableData
+{
+ NameData varname;
+
+ Oid varowner;
+ Oid vartype;
+ int32 vartypmod;
+ Oid varcollation;
+
+ bool isnull;
+ Datum value;
+
+ int16 typlen;
+ bool typbyval;
+} SVariableData;
+
+typedef SVariableData *SVariable;
+
+static HTAB *sessionvars = NULL; /* hash table for session variables */
+
+static MemoryContext SVariableMemoryContext = NULL;
+
+/*
+ * Create the hash table for storing session variables.
+ */
+static void
+create_sessionvars_hashtables(void)
+{
+ HASHCTL vars_ctl;
+
+ Assert(!sessionvars);
+
+ if (!SVariableMemoryContext)
+ {
+ /* we need our own long-lived memory context */
+ SVariableMemoryContext =
+ AllocSetContextCreate(TopMemoryContext,
+ "session variables",
+ ALLOCSET_START_SMALL_SIZES);
+ }
+
+ vars_ctl.keysize = NAMEDATALEN;
+ vars_ctl.entrysize = sizeof(SVariableData);
+ vars_ctl.hcxt = SVariableMemoryContext;
+
+ sessionvars = hash_create("Session variables", 64, &vars_ctl,
+ HASH_ELEM | HASH_STRINGS | HASH_CONTEXT);
+}
+
+/*
+ * Returns entry of session variable specified by name
+ */
+static SVariable
+search_variable(char *varname)
+{
+ SVariable svar;
+
+ if (!sessionvars)
+ create_sessionvars_hashtables();
+
+ svar = (SVariable) hash_search(sessionvars, varname,
+ HASH_FIND, NULL);
+
+ if (!svar)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("session variable \"%s\" doesn't exist",
+ varname)));
+
+ return svar;
+}
+
+/*
+ * Creates a new variable - does new entry in sessionvars
+ *
+ * Used by CREATE VARIABLE command
+ */
+void
+CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
+{
+ Oid typeid;
+ int32 typmod;
+ Oid typcollation;
+ Oid varowner = GetUserId();
+ SVariable svar;
+ bool found;
+ int16 typlen;
+ bool typbyval;
+
+ /*
+ * Current implementation is not catalog based, but we expect catalog
+ * based implementation for future, so we force same limits.
+ */
+ PreventCommandIfReadOnly("CREATE VARIABLE");
+ PreventCommandIfParallelMode("CREATE VARIABLE");
+ PreventCommandDuringRecovery("CREATE VARIABLE");
+
+ typenameTypeIdAndMod(pstate, stmt->typeName, &typeid, &typmod);
+
+ if (get_typtype(typeid) != TYPTYPE_BASE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s is not a base type",
+ format_type_be(typeid))));
+
+ if (OidIsValid(get_element_type(typeid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s type is an array",
+ format_type_be(typeid))));
+
+ /* allow only buildin types */
+ if (typeid >= FirstUnpinnedObjectId)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("session variable cannot have a user-defined type"),
+ errdetail("Session variables that make use of user-defined types are not yet supported."));
+
+ get_typlenbyval(typeid, &typlen, &typbyval);
+ typcollation = get_typcollation(typeid);
+
+ if (!sessionvars)
+ create_sessionvars_hashtables();
+
+ svar = hash_search(sessionvars, stmt->name,
+ HASH_ENTER, &found);
+
+ if (found)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
+
+ namestrcpy(&svar->varname, stmt->name);
+ svar->vartype = typeid;
+ svar->vartypmod = typmod;
+ svar->varcollation = typcollation;
+ svar->varowner = varowner;
+ svar->typlen = typlen;
+ svar->typbyval = typbyval;
+
+ svar->value = (Datum) 0;
+ svar->isnull = true;
+}
+
+/*
+ * Drop variable by name
+ */
+void
+DropVariableByName(char *varname)
+{
+ SVariable svar;
+
+ /*
+ * Current implementation is not catalog based, but we expect catalog
+ * based implementation for future, so we force same limits.
+ */
+ PreventCommandIfReadOnly("DROP VARIABLE");
+ PreventCommandIfParallelMode("DROP VARIABLE");
+ PreventCommandDuringRecovery("DROP VARIABLE");
+
+ svar = search_variable(varname);
+
+ /* only owner can get content of variable */
+ if (svar->varowner != GetUserId() && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be owner of session variable %s",
+ varname)));
+
+ if (!svar->typbyval && !svar->isnull)
+ pfree(DatumGetPointer(svar->value));
+
+ if (hash_search(sessionvars,
+ varname,
+ HASH_REMOVE,
+ NULL) == NULL)
+ elog(ERROR, "hash table corrupted");
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f4a08baa95a..1afc77bf5a7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -292,14 +292,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt
CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt
- CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt
- CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
+ CreateSchemaStmt CreateSeqStmt CreateSessionVarStmt CreateStmt CreateStatsStmt
+ CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertionStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
CreatePropGraphStmt AlterPropGraphStmt
CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
DropOpClassStmt DropOpFamilyStmt DropStmt
- DropCastStmt DropRoleStmt
+ DropCastStmt DropRoleStmt DropSessionVarStmt
DropdbStmt DropTableSpaceStmt
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
@@ -828,8 +828,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
UNLISTEN UNLOGGED UNTIL UPDATE USER USING
- VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VERTEX VIEW VIEWS VIRTUAL VOLATILE
+ VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARIABLE
+ VARYING VERBOSE VERSION_P VERTEX VIEW VIEWS VIRTUAL VOLATILE
WAIT WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -1099,6 +1099,7 @@ stmt:
| CreatePLangStmt
| CreatePropGraphStmt
| CreateSchemaStmt
+ | CreateSessionVarStmt
| CreateSeqStmt
| CreateStmt
| CreateSubscriptionStmt
@@ -1126,6 +1127,7 @@ stmt:
| DropTableSpaceStmt
| DropTransformStmt
| DropRoleStmt
+ | DropSessionVarStmt
| DropUserMappingStmt
| DropdbStmt
| ExecuteStmt
@@ -5442,6 +5444,47 @@ create_extension_opt_item:
}
;
+/*****************************************************************************
+ *
+ * QUERY :
+ * CREATE { TEMP | TEMPORARY } VARIABLE varname [AS] type
+ *
+ *****************************************************************************/
+
+CreateSessionVarStmt:
+ CREATE OptTemp VARIABLE ColId opt_as Typename
+ {
+ CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
+
+ if ($2 != RELPERSISTENCE_TEMP)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only temporal session variables are supported"),
+ parser_errposition(@2)));
+
+ n->name = $4;
+ n->typeName = $6;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY :
+ * DROP VARIABLE varname
+ *
+ *****************************************************************************/
+
+DropSessionVarStmt:
+ DROP VARIABLE ColId
+ {
+ DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
+
+ n->name = $3;
+ $$ = (Node *) n;
+ }
+ ;
+
/*****************************************************************************
*
* ALTER EXTENSION name UPDATE [ TO version ]
@@ -19146,6 +19189,7 @@ unreserved_keyword:
| VALIDATE
| VALIDATOR
| VALUE_P
+ | VARIABLE
| VARYING
| VERSION_P
| VERTEX
@@ -19818,6 +19862,7 @@ bare_label_keyword:
| VALUE_P
| VALUES
| VARCHAR
+ | VARIABLE
| VARIADIC
| VERBOSE
| VERSION_P
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 2b609bfc824..269983987eb 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -49,6 +49,7 @@
#include "commands/schemacmds.h"
#include "commands/seclabel.h"
#include "commands/sequence.h"
+#include "commands/session_variable.h"
#include "commands/subscriptioncmds.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
@@ -186,6 +187,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_CreateRangeStmt:
case T_CreateRoleStmt:
case T_CreateSchemaStmt:
+ case T_CreateSessionVarStmt:
case T_CreateSeqStmt:
case T_CreateStatsStmt:
case T_CreateStmt:
@@ -204,6 +206,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_DropTableSpaceStmt:
case T_DropUserMappingStmt:
case T_DropdbStmt:
+ case T_DropSessionVarStmt:
case T_GrantRoleStmt:
case T_GrantStmt:
case T_ImportForeignSchemaStmt:
@@ -1066,6 +1069,15 @@ standard_ProcessUtility(PlannedStmt *pstmt,
}
break;
+ case T_CreateSessionVarStmt:
+ CreateVariable(pstate, (CreateSessionVarStmt *) parsetree);
+ break;
+
+ case T_DropSessionVarStmt:
+ /* No event triggers for catalog less session variables */
+ DropVariableByName(((DropSessionVarStmt *) parsetree)->name);
+ break;
+
default:
/* All other statement types have event trigger support */
ProcessUtilitySlow(pstate, pstmt, queryString,
@@ -1391,6 +1403,7 @@ ProcessUtilitySlow(ParseState *pstate,
}
break;
+
/*
* ************* object creation / destruction **************
*/
@@ -3265,6 +3278,14 @@ CreateCommandTag(Node *parsetree)
}
break;
+ case T_CreateSessionVarStmt:
+ tag = CMDTAG_CREATE_VARIABLE;
+ break;
+
+ case T_DropSessionVarStmt:
+ tag = CMDTAG_DROP_VARIABLE;
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
@@ -3811,6 +3832,11 @@ GetCommandLogLevel(Node *parsetree)
}
break;
+ case T_CreateSessionVarStmt:
+ case T_DropSessionVarStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 53bf1e21721..fe012829ac0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1373,6 +1373,7 @@ static const pgsql_thing_t words_after_create[] = {
{"USER", Query_for_list_of_roles, NULL, NULL, Keywords_for_user_thing},
{"USER MAPPING FOR", NULL, NULL, NULL},
{"VIEW", NULL, NULL, &Query_for_list_of_views},
+ {"VARIABLE", NULL, NULL, NULL, NULL, THING_NO_CREATE},
{NULL} /* end of list */
};
@@ -3852,7 +3853,7 @@ match_previous_words(int pattern_id,
/* CREATE TABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
else if (TailMatches("CREATE", "TEMP|TEMPORARY"))
- COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW");
+ COMPLETE_WITH("SEQUENCE", "TABLE", "VARIABLE", "VIEW");
/* Complete "CREATE UNLOGGED" with TABLE or SEQUENCE */
else if (TailMatches("CREATE", "UNLOGGED"))
COMPLETE_WITH("TABLE", "SEQUENCE");
@@ -4214,6 +4215,13 @@ match_previous_words(int pattern_id,
COMPLETE_WITH(",", ")");
}
+/* CREATE VARIABLE */
+ else if (Matches("CREATE", "TEMP|TEMPORARY", "VARIABLE", MatchAny))
+ COMPLETE_WITH("AS");
+ else if (TailMatches("VARIABLE", MatchAny, "AS"))
+ /* Complete CREATE VARIABLE <name> with AS types */
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
+
/* CREATE VIEW --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete CREATE [ OR REPLACE ] VIEW <name> with AS or WITH */
else if (TailMatches("CREATE", "VIEW", MatchAny) ||
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
new file mode 100644
index 00000000000..1ed40d87a38
--- /dev/null
+++ b/src/include/commands/session_variable.h
@@ -0,0 +1,25 @@
+/*-------------------------------------------------------------------------
+ *
+ * sessionvariable.h
+ * prototypes for sessionvariable.c.
+ *
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/commands/session_variable.h
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#ifndef SESSIONVARIABLE_H
+#define SESSIONVARIABLE_H
+
+#include "catalog/objectaddress.h"
+#include "parser/parse_node.h"
+#include "nodes/parsenodes.h"
+
+extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
+extern void DropVariableByName(char *varname);
+
+#endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 91377a6cde3..379c740b944 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3664,6 +3664,29 @@ typedef struct AlterStatsStmt
bool missing_ok; /* skip error if statistics object is missing */
} AlterStatsStmt;
+
+/* ----------------------
+ * Create Variable Statement
+ * ----------------------
+ */
+typedef struct CreateSessionVarStmt
+{
+ NodeTag type;
+ char *name; /* the variable to create */
+ TypeName *typeName; /* the type of variable */
+} CreateSessionVarStmt;
+
+/* ----------------------
+ * DROP Variable Statement
+ * ----------------------
+ */
+typedef struct DropSessionVarStmt
+{
+ NodeTag type;
+ char *name;
+} DropSessionVarStmt;
+
+
/* ----------------------
* Create Function Statement
* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 51ead54f015..491347970a2 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -501,6 +501,7 @@ PG_KEYWORD("validator", VALIDATOR, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("value", VALUE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("values", VALUES, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("varchar", VARCHAR, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("variable", VARIABLE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index befae5f6b4f..35229a16add 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -125,6 +125,7 @@ PG_CMDTAG(CMDTAG_CREATE_TRANSFORM, "CREATE TRANSFORM", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_TRIGGER, "CREATE TRIGGER", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_TYPE, "CREATE TYPE", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_USER_MAPPING, "CREATE USER MAPPING", true, false, false)
+PG_CMDTAG(CMDTAG_CREATE_VARIABLE, "CREATE VARIABLE", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_VIEW, "CREATE VIEW", true, false, false)
PG_CMDTAG(CMDTAG_DEALLOCATE, "DEALLOCATE", false, false, false)
PG_CMDTAG(CMDTAG_DEALLOCATE_ALL, "DEALLOCATE ALL", false, false, false)
@@ -178,6 +179,7 @@ PG_CMDTAG(CMDTAG_DROP_TRANSFORM, "DROP TRANSFORM", true, false, false)
PG_CMDTAG(CMDTAG_DROP_TRIGGER, "DROP TRIGGER", true, false, false)
PG_CMDTAG(CMDTAG_DROP_TYPE, "DROP TYPE", true, false, false)
PG_CMDTAG(CMDTAG_DROP_USER_MAPPING, "DROP USER MAPPING", true, false, false)
+PG_CMDTAG(CMDTAG_DROP_VARIABLE, "DROP VARIABLE", true, false, false)
PG_CMDTAG(CMDTAG_DROP_VIEW, "DROP VIEW", true, false, false)
PG_CMDTAG(CMDTAG_EXECUTE, "EXECUTE", false, false, false)
PG_CMDTAG(CMDTAG_EXPLAIN, "EXPLAIN", false, false, false)
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
new file mode 100644
index 00000000000..45c2d27ab44
--- /dev/null
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -0,0 +1,43 @@
+SET log_statement TO ddl;
+-- should to fail
+CREATE VARIABLE x AS int;
+ERROR: only temporal session variables are supported
+-- should be ok
+CREATE TEMPORARY VARIABLE x AS int;
+-- should fail
+CREATE TEMPORARY VARIABLE x AS int;
+ERROR: session variable "x" already exists
+-- should fail
+DROP VARIABLE y;
+ERROR: session variable "y" doesn't exist
+-- should be ok
+DROP VARIABLE x;
+CREATE TYPE test_type AS (x int, y int);
+-- should fail
+CREATE VARIABLE x AS test_type;
+ERROR: only temporal session variables are supported
+DROP TYPE test_type;
+-- should fail
+CREATE VARIABLE x AS int[];
+ERROR: only temporal session variables are supported
+CREATE DOMAIN test_domain AS int;
+-- should fail
+CREATE TEMP VARIABLE x AS test_domain;
+ERROR: test_domain is not a base type
+DROP DOMAIN test_domain;
+CREATE ROLE regress_session_variable_test_role_01;
+CREATE ROLE regress_session_variable_test_role_02;
+SET ROLE TO regress_session_variable_test_role_01;
+CREATE TEMP VARIABLE x AS int;
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_02;
+-- should fail
+DROP VARIABLE x;
+ERROR: must be owner of session variable x
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_01;
+-- should be ok
+DROP VARIABLE x;
+SET ROLE TO DEFAULT;
+DROP ROLE regress_session_variable_test_role_01;
+DROP ROLE regress_session_variable_test_role_02;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 3a044ffd8bf..ec92c717bf6 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -115,7 +115,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
# NB: temp.sql does reconnects which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml session_variables_ddl
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
new file mode 100644
index 00000000000..34f34dd898f
--- /dev/null
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -0,0 +1,56 @@
+SET log_statement TO ddl;
+
+-- should to fail
+CREATE VARIABLE x AS int;
+
+-- should be ok
+CREATE TEMPORARY VARIABLE x AS int;
+
+-- should fail
+CREATE TEMPORARY VARIABLE x AS int;
+
+-- should fail
+DROP VARIABLE y;
+
+-- should be ok
+DROP VARIABLE x;
+
+CREATE TYPE test_type AS (x int, y int);
+
+-- should fail
+CREATE VARIABLE x AS test_type;
+
+DROP TYPE test_type;
+
+-- should fail
+CREATE VARIABLE x AS int[];
+
+CREATE DOMAIN test_domain AS int;
+
+-- should fail
+CREATE TEMP VARIABLE x AS test_domain;
+
+DROP DOMAIN test_domain;
+
+CREATE ROLE regress_session_variable_test_role_01;
+CREATE ROLE regress_session_variable_test_role_02;
+
+SET ROLE TO regress_session_variable_test_role_01;
+
+CREATE TEMP VARIABLE x AS int;
+
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_02;
+
+-- should fail
+DROP VARIABLE x;
+
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_01;
+
+-- should be ok
+DROP VARIABLE x;
+
+SET ROLE TO DEFAULT;
+DROP ROLE regress_session_variable_test_role_01;
+DROP ROLE regress_session_variable_test_role_02;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 5bc517602b1..4716ef8f3be 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -583,6 +583,7 @@ CreateRoleStmt
CreateSchemaStmt
CreateSchemaStmtContext
CreateSeqStmt
+CreateSessionVarStmt
CreateStatsStmt
CreateStmt
CreateStmtContext
@@ -687,6 +688,7 @@ DropBehavior
DropOwnedStmt
DropReplicationSlotCmd
DropRoleStmt
+DropSessionVarStmt
DropStmt
DropSubscriptionStmt
DropTableSpaceStmt
@@ -2743,6 +2745,8 @@ SSL_CTX
STARTUPINFO
STRLEN
SV
+SVariableData
+SVariable
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
SampleScan
--
2.53.0
[text/x-patch] v20260403-2-0002-parsing-session-variable-fences.patch (18.5K, 4-v20260403-2-0002-parsing-session-variable-fences.patch)
download | inline diff:
From 5e27c590f3545d10d715a377456e8abe4539633f Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Fri, 21 Nov 2025 15:28:59 +0100
Subject: [PATCH 02/11] parsing session variable fences
The session variables can be used in query only inside the variable fence.
This is special syntax VARIABLE(varname), that eliminates a risk of
collision between variable and column identifier.
The session variables cannot be used as parameters of CALL or EXECUTE
commands. These commands evaluates arguments by direct call of expression
executor, and direct access to session variables from expression executor
will be implemented later (in next step)
---
doc/src/sgml/ddl.sgml | 10 ++
src/backend/commands/prepare.c | 8 ++
src/backend/commands/session_variable.c | 21 ++++
src/backend/nodes/nodeFuncs.c | 6 ++
src/backend/parser/analyze.c | 7 ++
src/backend/parser/gram.y | 17 +++-
src/backend/parser/parse_expr.c | 121 ++++++++++++++++++++++++
src/backend/parser/parse_merge.c | 1 +
src/backend/parser/parse_target.c | 7 ++
src/backend/utils/adt/ruleutils.c | 8 ++
src/include/commands/session_variable.h | 5 +
src/include/nodes/parsenodes.h | 12 +++
src/include/nodes/primnodes.h | 5 +
src/include/parser/parse_node.h | 1 +
src/pl/plpgsql/src/pl_exec.c | 3 +-
src/tools/pgindent/typedefs.list | 1 +
16 files changed, 230 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 17618813582..0055eb84a78 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5954,6 +5954,16 @@ SELECT ... FROM GRAPH_TABLE (myshop MATCH (IS person WHERE name = '...')-[]->...
variable is stored in session memory and is private to each session. It is
automatically released when the session ends.
</para>
+
+ <para>
+ 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.
+ </para>
+<programlisting>
+SELECT VARIABLE(current_user_id);
+</programlisting>
+ </para>
</sect1>
<sect1 id="ddl-others">
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 876aad2100a..5e0774247a7 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -343,6 +343,14 @@ EvaluateParams(ParseState *pstate, PreparedStatement *pstmt, List *params,
i++;
}
+ /*
+ * The arguments of EXECUTE are evaluated by a direct expression executor
+ * call. This mode doesn't support session variables yet. It will be
+ * enabled later. This case should be blocked parser by
+ * expr_kind_allows_session_variables, so only assertions is used here.
+ */
+ Assert(!pstate->p_hasSessionVariables);
+
/* Prepare the expressions for execution */
exprstates = ExecPrepareExprList(params, estate);
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index a865a4c10c4..482737d6797 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -107,6 +107,27 @@ search_variable(char *varname)
return svar;
}
+/*
+ * Returns the type, typmod and collid of the given session variable.
+ *
+ * Raises an error when the variable doesn't exists and *error is null.
+ */
+void
+get_session_variable_type_typmod_collid(char *varname,
+ Oid *typid,
+ int32 *typmod,
+ Oid *collid)
+{
+ SVariable svar;
+
+ svar = search_variable(varname);
+
+ /* only owner can set content of variable */
+ *typid = svar->vartype;
+ *typmod = svar->vartypmod;
+ *collid = svar->varcollation;
+}
+
/*
* Creates a new variable - does new entry in sessionvars
*
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c0b880ec233..0f6990f1b84 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1677,6 +1677,9 @@ exprLocation(const Node *expr)
case T_ParamRef:
loc = ((const ParamRef *) expr)->location;
break;
+ case T_VariableFence:
+ loc = ((const VariableFence *) expr)->location;
+ break;
case T_A_Const:
loc = ((const A_Const *) expr)->location;
break;
@@ -4808,6 +4811,9 @@ raw_expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_VariableFence:
+ /* we assume the fields contain nothing interesting */
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 84deed9aaa6..a2b9ce66e12 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -635,6 +635,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
assign_query_collations(pstate, qry);
@@ -1074,6 +1075,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
assign_query_collations(pstate, qry);
@@ -1866,6 +1868,7 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt,
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
foreach(l, stmt->lockingClause)
{
@@ -2092,6 +2095,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
assign_query_collations(pstate, qry);
@@ -2343,6 +2347,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
foreach(l, lockingClause)
{
@@ -2835,6 +2840,7 @@ transformReturnStmt(ParseState *pstate, ReturnStmt *stmt)
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
assign_query_collations(pstate, qry);
@@ -2909,6 +2915,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
assign_query_collations(pstate, qry);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1afc77bf5a7..f3a4aeef3fe 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -535,7 +535,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> def_arg columnElem where_clause where_or_current_clause
a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound
columnref having_clause func_table xmltable array_expr
- OptWhereClause operator_def_arg
+ OptWhereClause operator_def_arg variable_fence
%type <list> opt_column_and_period_list
%type <list> rowsfrom_item rowsfrom_list opt_col_def_list
%type <boolean> opt_ordinality opt_without_overlaps
@@ -929,7 +929,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
*/
%nonassoc UNBOUNDED NESTED /* ideally would have same precedence as IDENT */
%nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
- SET KEYS OBJECT_P SCALAR TO USING VALUE_P WITH WITHOUT PATH
+ SET KEYS OBJECT_P SCALAR TO USING VALUE_P WITH WITHOUT PATH VARIABLE
%left Op OPERATOR RIGHT_ARROW '|' /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
@@ -16490,6 +16490,8 @@ c_expr: columnref { $$ = $1; }
else
$$ = $2;
}
+ | variable_fence
+ { $$ = $1; }
| case_expr
{ $$ = $1; }
| func_expr
@@ -17894,6 +17896,17 @@ case_arg: a_expr { $$ = $1; }
| /*EMPTY*/ { $$ = NULL; }
;
+variable_fence:
+ VARIABLE '(' ColId ')'
+ {
+ VariableFence *vf = makeNode(VariableFence);
+
+ vf->varname = $3;
+ vf->location = @3;
+ $$ = (Node *) vf;
+ }
+ ;
+
columnref: ColId
{
$$ = makeColumnRef($1, NIL, @1, yyscanner);
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f535f3b9351..4b995ab4a95 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -18,6 +18,7 @@
#include "access/htup_details.h"
#include "catalog/pg_aggregate.h"
#include "catalog/pg_type.h"
+#include "commands/session_variable.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -79,6 +80,7 @@ static Node *transformWholeRowRef(ParseState *pstate,
static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
+static Node *transformVariableFence(ParseState *pstate, VariableFence *vf);
static Node *transformJsonObjectConstructor(ParseState *pstate,
JsonObjectConstructor *ctor);
static Node *transformJsonArrayConstructor(ParseState *pstate,
@@ -373,6 +375,10 @@ transformExprRecurse(ParseState *pstate, Node *expr)
result = transformJsonFuncExpr(pstate, (JsonFuncExpr *) expr);
break;
+ case T_VariableFence:
+ result = transformVariableFence(pstate, (VariableFence *) expr);
+ break;
+
default:
/* should not reach here */
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
@@ -920,6 +926,121 @@ transformParamRef(ParseState *pstate, ParamRef *pref)
return result;
}
+/*
+ * Returns true if the given expression kind is valid for session variables.
+ * Session variables can be used everywhere where external parameters can be
+ * used. Session variables are not allowed in DDL commands or in constraints.
+ *
+ * An identifier can be parsed as a session variable only for expression kinds
+ * where session variables are allowed. This is the primary usage of this
+ * function.
+ *
+ * The second usage of this function is to decide whether a "column does not
+ * exist" or a "column or variable does not exist" error message should be
+ * printed. When we are in an expression where session variables cannot be
+ * used, we raise the first form of error message.
+ */
+static bool
+expr_kind_allows_session_variables(ParseExprKind p_expr_kind)
+{
+ bool result = false;
+
+ switch (p_expr_kind)
+ {
+ case EXPR_KIND_NONE:
+ Assert(false); /* can't happen */
+ return false;
+
+ /* session variables allowed */
+ case EXPR_KIND_OTHER:
+ case EXPR_KIND_JOIN_ON:
+ case EXPR_KIND_FROM_SUBSELECT:
+ case EXPR_KIND_FROM_FUNCTION:
+ case EXPR_KIND_WHERE:
+ case EXPR_KIND_HAVING:
+ case EXPR_KIND_FILTER:
+ case EXPR_KIND_WINDOW_PARTITION:
+ case EXPR_KIND_WINDOW_ORDER:
+ case EXPR_KIND_WINDOW_FRAME_RANGE:
+ case EXPR_KIND_WINDOW_FRAME_ROWS:
+ case EXPR_KIND_WINDOW_FRAME_GROUPS:
+ case EXPR_KIND_SELECT_TARGET:
+ case EXPR_KIND_UPDATE_TARGET:
+ case EXPR_KIND_UPDATE_SOURCE:
+ case EXPR_KIND_MERGE_WHEN:
+ case EXPR_KIND_MERGE_RETURNING:
+ case EXPR_KIND_GROUP_BY:
+ case EXPR_KIND_ORDER_BY:
+ case EXPR_KIND_DISTINCT_ON:
+ case EXPR_KIND_LIMIT:
+ case EXPR_KIND_OFFSET:
+ case EXPR_KIND_RETURNING:
+ case EXPR_KIND_VALUES:
+ case EXPR_KIND_VALUES_SINGLE:
+ case EXPR_KIND_PROPGRAPH_PROPERTY:
+ case EXPR_KIND_FOR_PORTION:
+ result = true;
+ break;
+
+ /* session variables not allowed */
+ case EXPR_KIND_INSERT_TARGET:
+ case EXPR_KIND_EXECUTE_PARAMETER:
+ case EXPR_KIND_CALL_ARGUMENT:
+ case EXPR_KIND_CHECK_CONSTRAINT:
+ case EXPR_KIND_DOMAIN_CHECK:
+ case EXPR_KIND_COLUMN_DEFAULT:
+ case EXPR_KIND_FUNCTION_DEFAULT:
+ case EXPR_KIND_INDEX_EXPRESSION:
+ case EXPR_KIND_INDEX_PREDICATE:
+ case EXPR_KIND_STATS_EXPRESSION:
+ case EXPR_KIND_TRIGGER_WHEN:
+ case EXPR_KIND_PARTITION_BOUND:
+ case EXPR_KIND_PARTITION_EXPRESSION:
+ case EXPR_KIND_GENERATED_COLUMN:
+ case EXPR_KIND_JOIN_USING:
+ case EXPR_KIND_CYCLE_MARK:
+ case EXPR_KIND_ALTER_COL_TRANSFORM:
+ case EXPR_KIND_POLICY:
+ case EXPR_KIND_COPY_WHERE:
+ result = false;
+ break;
+ }
+
+ return result;
+}
+
+static Node *
+transformVariableFence(ParseState *pstate, VariableFence *vf)
+{
+ Param *param;
+ Oid typid;
+ int32 typmod;
+ Oid collid;
+
+ /* VariableFence can be used only in context when variables are supported */
+ if (!expr_kind_allows_session_variables(pstate->p_expr_kind))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("session variable reference is not supported here"),
+ parser_errposition(pstate, vf->location)));
+
+ get_session_variable_type_typmod_collid(vf->varname,
+ &typid, &typmod, &collid);
+
+
+ param = makeNode(Param);
+
+ param->paramkind = PARAM_VARIABLE;
+ param->paramvarname = pstrdup(vf->varname);
+ param->paramtype = typid;
+ param->paramtypmod = typmod;
+ param->paramcollid = collid;
+
+ pstate->p_hasSessionVariables = true;
+
+ return (Node *) param;
+}
+
/* Test whether an a_expr is a plain NULL constant or not */
static bool
exprIsNullConstant(Node *arg)
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
index 2e6dd166c98..84c234947dd 100644
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -400,6 +400,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
qry->hasTargetSRFs = false;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
assign_query_collations(pstate, qry);
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 541fef5f183..d1a3ea6b1d5 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -2040,6 +2040,13 @@ FigureColnameInternal(Node *node, char **name)
(int) ((JsonFuncExpr *) node)->op);
}
break;
+ case T_VariableFence:
+ {
+ /* return last field name */
+ *name = ((VariableFence *) node)->varname;
+ return 2;
+ }
+ break;
default:
break;
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index aec5556b008..0ac09a57e22 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9368,6 +9368,14 @@ get_parameter(Param *param, deparse_context *context)
}
}
+ /* Note: can be be used by EXPLAIN */
+ if (param->paramkind == PARAM_VARIABLE)
+ {
+ appendStringInfo(context->buf, "VARIABLE(%s)",
+ quote_identifier(param->paramvarname));
+ return;
+ }
+
/*
* Not PARAM_EXEC, or couldn't find referent: just print $N.
*
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 1ed40d87a38..96be968c3d4 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -22,4 +22,9 @@
extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
extern void DropVariableByName(char *varname);
+extern void get_session_variable_type_typmod_collid(char *varname,
+ Oid *typid,
+ int32 *typmod,
+ Oid *collid);
+
#endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 379c740b944..97f63a75939 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -170,6 +170,8 @@ typedef struct Query
bool hasRowSecurity pg_node_attr(query_jumble_ignore);
/* parser has added an RTE_GROUP RTE */
bool hasGroupRTE pg_node_attr(query_jumble_ignore);
+ /* uses session variables */
+ bool hasSessionVariables pg_node_attr(query_jumble_ignore);
/* is a RETURN statement */
bool isReturn pg_node_attr(query_jumble_ignore);
@@ -325,6 +327,16 @@ typedef struct ParamRef
ParseLoc location; /* token location, or -1 if unknown */
} ParamRef;
+/*
+ * VariableFence - ensure so fields will be interpretted as a variable
+ */
+typedef struct VariableFence
+{
+ NodeTag type;
+ char *varname; /* variable name */
+ ParseLoc location; /* token location, or -1 if unknown */
+} VariableFence;
+
/*
* A_Expr - infix, prefix, and postfix expressions
*/
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6dfc946c20b..0c762e492e9 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -379,6 +379,8 @@ typedef struct Const
* of the `paramid' field contain the SubLink's subLinkId, and
* the low-order 16 bits contain the column number. (This type
* of Param is also converted to PARAM_EXEC during planning.)
+ * PARAM_VARIABLE: The parameter is a reference to a session variable
+ * (paramvarname holds the variable's name).
*/
typedef enum ParamKind
{
@@ -386,6 +388,7 @@ typedef enum ParamKind
PARAM_EXEC,
PARAM_SUBLINK,
PARAM_MULTIEXPR,
+ PARAM_VARIABLE,
} ParamKind;
typedef struct Param
@@ -400,6 +403,8 @@ typedef struct Param
int32 paramtypmod;
/* OID of collation, or InvalidOid if none */
Oid paramcollid;
+ /* OID of used session variable or InvalidOid if none */
+ char *paramvarname pg_node_attr(query_jumble_ignore);
/* token location, or -1 if unknown */
ParseLoc location;
} Param;
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7f4ba6c2a8..00d77a1abc4 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -248,6 +248,7 @@ struct ParseState
bool p_hasTargetSRFs;
bool p_hasSubLinks;
bool p_hasModifyingCTE;
+ bool p_hasSessionVariables;
Node *p_last_srf; /* most recent set-returning func/op found */
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 65b0fd0790f..f432abaaa95 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8355,7 +8355,8 @@ exec_is_simple_query(PLpgSQL_expr *expr)
query->sortClause ||
query->limitOffset ||
query->limitCount ||
- query->setOperations)
+ query->setOperations ||
+ query->hasSessionVariables)
return false;
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 4716ef8f3be..6f889e18a6d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3314,6 +3314,7 @@ ValidatorValidateCB
ValuesScan
ValuesScanState
Var
+VariableFence
VarBit
VarChar
VarParamState
--
2.53.0
[text/x-patch] v20260403-2-0004-fill-an-auxiliary-buffer-with-values-of-session-vari.patch (14.3K, 5-v20260403-2-0004-fill-an-auxiliary-buffer-with-values-of-session-vari.patch)
download | inline diff:
From 132459dad1e46296fb32f8baff27dbb4fae77d4f Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sat, 22 Nov 2025 06:40:46 +0100
Subject: [PATCH 04/11] fill an auxiliary buffer with values of session
variables used in query
and locks variables used in query. Now we can read the content of any
session variable. Direct reading from expression executor is not allowed,
so we cannot to use session variables inside CALL or EXECUTE commands
(can be supported with direct access to session variables (from expression
executor) - postponed).
Using session variables blocks parallel query execution. It is not
technical problem (it just needs a serialization/deserialization of
es_session_varibles buffer), but it increases a size of patch (and then
it is postponed).
---
src/backend/executor/execExpr.c | 29 ++++
src/backend/executor/execMain.c | 49 +++++++
src/include/nodes/execnodes.h | 14 ++
.../expected/session_variables_dml.out | 135 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +
.../regress/sql/session_variables_dml.sql | 120 ++++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
7 files changed, 353 insertions(+)
create mode 100644 src/test/regress/expected/session_variables_dml.out
create mode 100644 src/test/regress/sql/session_variables_dml.sql
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 77229141b38..8e813ba2d7a 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1102,6 +1102,35 @@ ExecInitExprRec(Expr *node, ExprState *state,
ExprEvalPushStep(state, &scratch);
}
break;
+ case PARAM_VARIABLE:
+ {
+ int es_num_session_variables = 0;
+ SessionVariableValue *es_session_variables = NULL;
+ SessionVariableValue *var;
+
+ if (state->parent && state->parent->state)
+ {
+ es_session_variables = state->parent->state->es_session_variables;
+ es_num_session_variables = state->parent->state->es_num_session_variables;
+ }
+
+ Assert(es_session_variables);
+
+ /* parameter sanity checks */
+ if (param->paramid >= es_num_session_variables)
+ elog(ERROR, "paramid of PARAM_VARIABLE param is out of range");
+
+ var = &es_session_variables[param->paramid];
+
+ /*
+ * In this case, pass the value like a constant.
+ */
+ scratch.opcode = EEOP_CONST;
+ scratch.d.constval.value = var->value;
+ scratch.d.constval.isnull = var->isnull;
+ ExprEvalPushStep(state, &scratch);
+ }
+ break;
default:
elog(ERROR, "unrecognized paramkind: %d",
(int) param->paramkind);
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 45e00c6af85..6c8e434bf37 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -45,6 +45,7 @@
#include "catalog/namespace.h"
#include "catalog/partition.h"
#include "commands/matview.h"
+#include "commands/session_variable.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "executor/execPartition.h"
@@ -198,6 +199,54 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
Assert(queryDesc->sourceText != NULL);
estate->es_sourceText = queryDesc->sourceText;
+ /*
+ * The executor doesn't work with session variables directly. Values of
+ * related session variables are copied to a dedicated array, and this
+ * array is passed to the executor. This array is stable "snapshot" of
+ * values of used session variables. There are three benefits of this
+ * strategy:
+ *
+ * - consistency with external parameters and plpgsql variables,
+ *
+ * - session variables can be parallel safe,
+ *
+ * - we don't need make fresh copy for any read of session variable (this
+ * is necessary because the internally the session variable can be changed
+ * inside query execution time, and then a reference to previously
+ * returned value can be corrupted).
+ */
+ if (queryDesc->plannedstmt->sessionVariables)
+ {
+ int nSessionVariables;
+ int i = 0;
+
+ /*
+ * In this case, the query uses session variables, but we have to
+ * prepare the array with passed values (of used session variables)
+ * first.
+ */
+ Assert(!IsParallelWorker());
+ nSessionVariables = list_length(queryDesc->plannedstmt->sessionVariables);
+
+ /* create the array used for passing values of used session variables */
+ estate->es_session_variables = palloc_array(SessionVariableValue,
+ nSessionVariables);
+
+ /* fill the array */
+ foreach_node(Param, param, queryDesc->plannedstmt->sessionVariables)
+ {
+ estate->es_session_variables[i].value =
+ GetSessionVariableWithTypecheck(param->paramvarname,
+ param->paramtype,
+ param->paramtypmod,
+ &estate->es_session_variables[i].isnull);
+
+ i++;
+ }
+
+ estate->es_num_session_variables = nSessionVariables;
+ }
+
/*
* Fill in the query environment, if any, from queryDesc.
*/
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 090cfccf65f..1723594e016 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -679,6 +679,16 @@ typedef struct AsyncRequest
* tuples) */
} AsyncRequest;
+/* ----------------
+ * SessionVariableValue
+ * ----------------
+ */
+typedef struct SessionVariableValue
+{
+ bool isnull;
+ Datum value;
+} SessionVariableValue;
+
/* ----------------
* EState information
*
@@ -738,6 +748,10 @@ typedef struct EState
ParamListInfo es_param_list_info; /* values of external params */
ParamExecData *es_param_exec_vals; /* values of internal params */
+ /* Session variables info: */
+ int es_num_session_variables; /* number of used variables */
+ SessionVariableValue *es_session_variables; /* array of copies of values */
+
QueryEnvironment *es_queryEnv; /* query environment */
/* Other working state: */
diff --git a/src/test/regress/expected/session_variables_dml.out b/src/test/regress/expected/session_variables_dml.out
new file mode 100644
index 00000000000..1519bf723e0
--- /dev/null
+++ b/src/test/regress/expected/session_variables_dml.out
@@ -0,0 +1,135 @@
+CREATE TEMP VARIABLE temp_var01 AS int;
+-- should not be accessible without variable's fence
+-- should fail
+SELECT temp_var01;
+ERROR: column "temp_var01" does not exist
+LINE 1: SELECT temp_var01;
+ ^
+-- should be ok
+SELECT VARIABLE(temp_var01);
+ temp_var01
+------------
+
+(1 row)
+
+-- should not crash
+DO $$
+BEGIN
+ RAISE NOTICE '%', VARIABLE(temp_var01);
+END;
+$$;
+NOTICE: <NULL>
+-- variables cannot be used by persistent objects
+-- that checks dependency
+-- should fail
+CREATE TEMP VIEW tempv AS SELECT VARIABLE(temp_var01);
+ERROR: session variable "temp_var01" cannot be referenced in a catalog object
+CREATE OR REPLACE FUNCTION testvar_sql()
+RETURNS int AS $$
+SELECT VARIABLE(temp_var01);
+$$ LANGUAGE sql;
+SELECT testvar_sql();
+ testvar_sql
+-------------
+
+(1 row)
+
+-- session variable cannot be used as parameter of CALL or EXECUTE
+CREATE OR REPLACE PROCEDURE testvar_proc(int)
+AS $$
+BEGIN
+ RAISE NOTICE '%', $1;
+END;
+$$ LANGUAGE plpgsql;
+-- should not crash
+CALL testvar_proc(VARIABLE(temp_var01));
+ERROR: session variable reference is not supported here
+LINE 1: CALL testvar_proc(VARIABLE(temp_var01));
+ ^
+PREPARE prepstmt(int) AS SELECT $1;
+-- should not crash
+EXECUTE prepstmt(VARIABLE(temp_var01));
+ERROR: session variable reference is not supported here
+LINE 1: EXECUTE prepstmt(VARIABLE(temp_var01));
+ ^
+DROP PROCEDURE testvar_proc;
+DEALLOCATE prepstmt;
+CREATE ROLE regress_session_variable_test_role_03;
+CREATE OR REPLACE FUNCTION testvar_sd()
+RETURNS void AS $$
+BEGIN
+ RAISE NOTICE '%', VARIABLE(temp_var01);
+END;
+$$ LANGUAGE plpgsql;
+-- only owner can read data
+SET ROLE TO regress_session_variable_test_role_03;
+-- should fail
+SELECT VARIABLE(temp_var01);
+ERROR: permission denied for session variable temp_var01
+-- fx with security definer should be ok
+SELECT testvar_sd();
+ERROR: permission denied for session variable temp_var01
+CONTEXT: PL/pgSQL expression "VARIABLE(temp_var01)"
+PL/pgSQL function testvar_sd() line 3 at RAISE
+SET ROLE TO default;
+DROP VARIABLE temp_var01;
+-- there is not plan cache invalidation
+-- but still functions that uses dropped variables
+-- should not to crash
+SELECT testvar_sd();
+ERROR: session variable "temp_var01" doesn't exist
+CONTEXT: PL/pgSQL expression "VARIABLE(temp_var01)"
+PL/pgSQL function testvar_sd() line 3 at RAISE
+SELECT testvar_sql();
+ERROR: session variable "temp_var01" doesn't exist
+CONTEXT: SQL function "testvar_sql" during inlining
+DROP FUNCTION testvar_sql();
+DROP FUNCTION testvar_sd();
+DROP ROLE regress_session_variable_test_role_03;
+CREATE TABLE testvar_testtab(a int);
+CREATE TEMP VARIABLE temp_var02 AS int;
+INSERT INTO testvar_testtab SELECT * FROM generate_series(1,1000);
+CREATE INDEX testvar_testtab_a ON testvar_testtab(a);
+ANALYZE testvar_testtab;
+-- force index
+SET enable_seqscan TO OFF;
+-- index scan should be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = VARIABLE(temp_var02);
+ QUERY PLAN
+------------------------------------------------------------
+ Index Only Scan using testvar_testtab_a on testvar_testtab
+ Index Cond: (a = VARIABLE(temp_var02))
+(2 rows)
+
+DROP INDEX testvar_testtab_a;
+SET enable_seqscan TO DEFAULT;
+-- parallel execution should be blocked
+-- Encourage use of parallel plans
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+SET min_parallel_table_scan_size = 0;
+SET max_parallel_workers_per_gather = 2;
+-- parallel plan should be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = 100;
+ QUERY PLAN
+--------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Parallel Seq Scan on testvar_testtab
+ Filter: (a = 100)
+(4 rows)
+
+-- parallel plan should not be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = VARIABLE(temp_var02);
+ QUERY PLAN
+--------------------------------------
+ Seq Scan on testvar_testtab
+ Filter: (a = VARIABLE(temp_var02))
+(2 rows)
+
+RESET parallel_setup_cost;
+RESET parallel_tuple_cost;
+RESET min_parallel_table_scan_size;
+RESET max_parallel_workers_per_gather;
+DROP TABLE testvar_testtab;
+DROP VARIABLE temp_var02;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index ec92c717bf6..b7c0ad00149 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -140,3 +140,8 @@ test: fast_default
# run tablespace test at the end because it drops the tablespace created during
# setup that other tests may use.
test: tablespace
+
+# ----------
+# Another group of parallel tests (session variables related)
+# ----------
+test: session_variables_ddl session_variables_dml
diff --git a/src/test/regress/sql/session_variables_dml.sql b/src/test/regress/sql/session_variables_dml.sql
new file mode 100644
index 00000000000..bf56b19467b
--- /dev/null
+++ b/src/test/regress/sql/session_variables_dml.sql
@@ -0,0 +1,120 @@
+CREATE TEMP VARIABLE temp_var01 AS int;
+
+-- should not be accessible without variable's fence
+-- should fail
+SELECT temp_var01;
+
+-- should be ok
+SELECT VARIABLE(temp_var01);
+
+-- should not crash
+DO $$
+BEGIN
+ RAISE NOTICE '%', VARIABLE(temp_var01);
+END;
+$$;
+
+-- variables cannot be used by persistent objects
+-- that checks dependency
+-- should fail
+CREATE TEMP VIEW tempv AS SELECT VARIABLE(temp_var01);
+
+CREATE OR REPLACE FUNCTION testvar_sql()
+RETURNS int AS $$
+SELECT VARIABLE(temp_var01);
+$$ LANGUAGE sql;
+
+SELECT testvar_sql();
+
+-- session variable cannot be used as parameter of CALL or EXECUTE
+CREATE OR REPLACE PROCEDURE testvar_proc(int)
+AS $$
+BEGIN
+ RAISE NOTICE '%', $1;
+END;
+$$ LANGUAGE plpgsql;
+
+-- should not crash
+CALL testvar_proc(VARIABLE(temp_var01));
+
+PREPARE prepstmt(int) AS SELECT $1;
+
+-- should not crash
+EXECUTE prepstmt(VARIABLE(temp_var01));
+
+DROP PROCEDURE testvar_proc;
+DEALLOCATE prepstmt;
+
+CREATE ROLE regress_session_variable_test_role_03;
+
+CREATE OR REPLACE FUNCTION testvar_sd()
+RETURNS void AS $$
+BEGIN
+ RAISE NOTICE '%', VARIABLE(temp_var01);
+END;
+$$ LANGUAGE plpgsql;
+
+-- only owner can read data
+SET ROLE TO regress_session_variable_test_role_03;
+
+-- should fail
+SELECT VARIABLE(temp_var01);
+
+-- fx with security definer should be ok
+SELECT testvar_sd();
+
+SET ROLE TO default;
+
+DROP VARIABLE temp_var01;
+
+-- there is not plan cache invalidation
+-- but still functions that uses dropped variables
+-- should not to crash
+
+SELECT testvar_sd();
+SELECT testvar_sql();
+
+DROP FUNCTION testvar_sql();
+DROP FUNCTION testvar_sd();
+
+DROP ROLE regress_session_variable_test_role_03;
+
+CREATE TABLE testvar_testtab(a int);
+CREATE TEMP VARIABLE temp_var02 AS int;
+
+INSERT INTO testvar_testtab SELECT * FROM generate_series(1,1000);
+
+CREATE INDEX testvar_testtab_a ON testvar_testtab(a);
+
+ANALYZE testvar_testtab;
+
+-- force index
+SET enable_seqscan TO OFF;
+
+-- index scan should be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = VARIABLE(temp_var02);
+
+DROP INDEX testvar_testtab_a;
+
+SET enable_seqscan TO DEFAULT;
+
+-- parallel execution should be blocked
+-- Encourage use of parallel plans
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+SET min_parallel_table_scan_size = 0;
+SET max_parallel_workers_per_gather = 2;
+
+-- parallel plan should be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = 100;
+
+-- parallel plan should not be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = VARIABLE(temp_var02);
+
+RESET parallel_setup_cost;
+RESET parallel_tuple_cost;
+RESET min_parallel_table_scan_size;
+RESET max_parallel_workers_per_gather;
+
+DROP TABLE testvar_testtab;
+DROP VARIABLE temp_var02;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 6f889e18a6d..04f1bd17dcc 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2805,6 +2805,7 @@ SerializedTransactionState
Session
SessionBackupState
SessionEndType
+SessionVariableValue
SetConstraintState
SetConstraintStateData
SetConstraintTriggerData
--
2.53.0
[text/x-patch] v20260403-2-0005-svariableReceiver.patch (10.7K, 6-v20260403-2-0005-svariableReceiver.patch)
download | inline diff:
From 654e7eb78cb07434aebfb48096a66b22e3c46094 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sun, 1 Jun 2025 21:20:16 +0200
Subject: [PATCH 05/11] svariableReceiver
allows to store result of the query to session variable
Check correct format of result - one column, one row.
---
src/backend/commands/session_variable.c | 50 ++++++++
src/backend/executor/Makefile | 1 +
src/backend/executor/meson.build | 1 +
src/backend/executor/svariableReceiver.c | 139 +++++++++++++++++++++++
src/backend/tcop/dest.c | 7 ++
src/include/commands/session_variable.h | 3 +
src/include/executor/svariableReceiver.h | 22 ++++
src/include/tcop/dest.h | 1 +
src/tools/pgindent/typedefs.list | 1 +
9 files changed, 225 insertions(+)
create mode 100644 src/backend/executor/svariableReceiver.c
create mode 100644 src/include/executor/svariableReceiver.h
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 19c153dae3f..cd171a68cbf 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -167,6 +167,56 @@ GetSessionVariableWithTypecheck(char *varname,
return result;
}
+/*
+ * Store the given value in a session variable in the cache.
+ */
+void
+SetSessionVariableWithTypecheck(char *varname,
+ Oid typid, int32 typmod,
+ Datum value, bool isnull)
+{
+ SVariable svar;
+
+ svar = search_variable(varname);
+
+ if (svar->vartype != typid || svar->vartypmod != typmod)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("session variable %s is not of a type %s but type %s",
+ varname,
+ format_type_with_typemod(typid, typmod),
+ format_type_with_typemod(svar->vartype, svar->vartypmod))));
+
+ /* only owner can set content of variable */
+ if (svar->varowner != GetUserId() && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for session variable %s",
+ varname)));
+
+ if (!svar->typbyval)
+ {
+ if (!isnull)
+ {
+ MemoryContext oldcxt;
+
+ /*
+ * Do copy of value in session variables context. This operation
+ * can fail, so do it before releasing the old content.
+ */
+ oldcxt = MemoryContextSwitchTo(SVariableMemoryContext);
+ value = datumCopy(value, svar->typbyval, svar->typlen);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ if (!svar->isnull)
+ pfree(DatumGetPointer(svar->value));
+ }
+
+ svar->value = value;
+ svar->isnull = isnull;
+}
+
/*
* Creates a new variable - does new entry in sessionvars
*
diff --git a/src/backend/executor/Makefile b/src/backend/executor/Makefile
index 11118d0ce02..71248a34f26 100644
--- a/src/backend/executor/Makefile
+++ b/src/backend/executor/Makefile
@@ -76,6 +76,7 @@ OBJS = \
nodeWindowAgg.o \
nodeWorktablescan.o \
spi.o \
+ svariableReceiver.o \
tqueue.o \
tstoreReceiver.o
diff --git a/src/backend/executor/meson.build b/src/backend/executor/meson.build
index dc45be0b2ce..a572b6dab7c 100644
--- a/src/backend/executor/meson.build
+++ b/src/backend/executor/meson.build
@@ -64,6 +64,7 @@ backend_sources += files(
'nodeWindowAgg.c',
'nodeWorktablescan.c',
'spi.c',
+ 'svariableReceiver.c',
'tqueue.c',
'tstoreReceiver.c',
)
diff --git a/src/backend/executor/svariableReceiver.c b/src/backend/executor/svariableReceiver.c
new file mode 100644
index 00000000000..f8fbb7a8e71
--- /dev/null
+++ b/src/backend/executor/svariableReceiver.c
@@ -0,0 +1,139 @@
+/*-------------------------------------------------------------------------
+ *
+ * svariableReceiver.c
+ * An implementation of DestReceiver that stores the result value in
+ * a session variable.
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/executor/svariableReceiver.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "commands/session_variable.h"
+#include "executor/svariableReceiver.h"
+
+/*
+ * This DestReceiver is used by the LET command for storing the result to a
+ * session variable. The result has to have only one tuple with only one
+ * non-deleted attribute. The row counter (field "rows") is incremented
+ * after receiving a row, and an error is raised when there are no rows or
+ * there are more than one received rows. A received tuple cannot to have
+ * deleted attributes.
+ *
+ * The assignment to session variable have to be postponed until we are
+ * sure so only one row was received.
+ */
+typedef struct
+{
+ DestReceiver pub;
+ char *varname;
+ int rows; /* row counter */
+ TupleDesc tupdesc;
+ HeapTuple tuple;
+ MemoryContext tuple_cxt; /* holds a value before storing to variable */
+} SVariableState;
+
+/*
+ * Prepare to receive tuples from executor.
+ */
+static void
+svariableStartupReceiver(DestReceiver *self, int operation, TupleDesc typeinfo)
+{
+ SVariableState *myState = (SVariableState *) self;
+
+ Assert(myState->pub.mydest == DestVariable);
+ Assert(typeinfo->natts == 1);
+
+ myState->rows = 0;
+ myState->tupdesc = typeinfo;
+ myState->tuple = NULL;
+ myState->tuple_cxt = CurrentMemoryContext;
+}
+
+/*
+ * Receive a tuple from the executor and store it in the buffer
+ */
+static bool
+svariableReceiveSlot(TupleTableSlot *slot, DestReceiver *self)
+{
+ SVariableState *myState = (SVariableState *) self;
+ MemoryContext oldcxt;
+
+ if (++myState->rows > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_TOO_MANY_ROWS),
+ errmsg("expression returned more than one row")));
+
+ /*
+ * We cannot to assign received value directly, so we should to
+ * save received value in the buffer.
+ */
+ oldcxt = MemoryContextSwitchTo(myState->tuple_cxt);
+ myState->tuple = ExecCopySlotHeapTuple(slot);
+ MemoryContextSwitchTo(oldcxt);
+
+ return true;
+}
+
+/*
+ * Clean up at end of the executor run
+ */
+static void
+svariableShutdownReceiver(DestReceiver *self)
+{
+ SVariableState *myState = (SVariableState *) self;
+ Form_pg_attribute attr;
+ Datum value;
+ bool isnull;
+
+ if (myState->rows == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_DATA_FOUND),
+ errmsg("expression returned no rows")));
+
+ attr = TupleDescAttr(myState->tupdesc, 0);
+ Assert(!attr->attisdropped);
+
+ value = heap_getattr(myState->tuple, 1, myState->tupdesc, &isnull);
+
+ SetSessionVariableWithTypecheck(myState->varname,
+ attr->atttypid, attr->atttypmod,
+ value, isnull);
+
+ heap_freetuple(myState->tuple);
+}
+
+/*
+ * Destroy the receiver when we are done with it
+ */
+static void
+svariableDestroyReceiver(DestReceiver *self)
+{
+ pfree(self);
+}
+
+/*
+ * Initially create a DestReceiver object.
+ */
+DestReceiver *
+CreateVariableDestReceiver(char *varname)
+{
+ SVariableState *self = (SVariableState *) palloc0(sizeof(SVariableState));
+
+ self->pub.receiveSlot = svariableReceiveSlot;
+ self->pub.rStartup = svariableStartupReceiver;
+ self->pub.rShutdown = svariableShutdownReceiver;
+ self->pub.rDestroy = svariableDestroyReceiver;
+ self->pub.mydest = DestVariable;
+
+ self->varname = varname;
+
+ return (DestReceiver *) self;
+}
diff --git a/src/backend/tcop/dest.c b/src/backend/tcop/dest.c
index fb163930c89..13fe536b432 100644
--- a/src/backend/tcop/dest.c
+++ b/src/backend/tcop/dest.c
@@ -38,6 +38,7 @@
#include "executor/functions.h"
#include "executor/tqueue.h"
#include "executor/tstoreReceiver.h"
+#include "executor/svariableReceiver.h"
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
@@ -155,6 +156,9 @@ CreateDestReceiver(CommandDest dest)
case DestExplainSerialize:
return CreateExplainSerializeDestReceiver(NULL);
+
+ case DestVariable:
+ return CreateVariableDestReceiver(NULL);
}
/* should never get here */
@@ -192,6 +196,7 @@ EndCommand(const QueryCompletion *qc, CommandDest dest, bool force_undecorated_o
case DestTransientRel:
case DestTupleQueue:
case DestExplainSerialize:
+ case DestVariable:
break;
}
}
@@ -238,6 +243,7 @@ NullCommand(CommandDest dest)
case DestTransientRel:
case DestTupleQueue:
case DestExplainSerialize:
+ case DestVariable:
break;
}
}
@@ -282,6 +288,7 @@ ReadyForQuery(CommandDest dest)
case DestTransientRel:
case DestTupleQueue:
case DestExplainSerialize:
+ case DestVariable:
break;
}
}
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 3687490bcb1..610b757899e 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -23,6 +23,9 @@ extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
extern void DropVariableByName(char *varname);
extern Datum GetSessionVariableWithTypecheck(char *varname, Oid typid, int32 typmod, bool *isnull);
+extern void SetSessionVariableWithTypecheck(char *varname,
+ Oid typid, int32 typmod,
+ Datum value, bool isnull);
extern void get_session_variable_type_typmod_collid(char *varname,
Oid *typid,
diff --git a/src/include/executor/svariableReceiver.h b/src/include/executor/svariableReceiver.h
new file mode 100644
index 00000000000..dd01c93c9e8
--- /dev/null
+++ b/src/include/executor/svariableReceiver.h
@@ -0,0 +1,22 @@
+/*-------------------------------------------------------------------------
+ *
+ * svariableReceiver.h
+ * prototypes for svariableReceiver.c
+ *
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/executor/svariableReceiver.h
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#ifndef SVARIABLE_RECEIVER_H
+#define SVARIABLE_RECEIVER_H
+
+#include "tcop/dest.h"
+
+extern DestReceiver *CreateVariableDestReceiver(char *varname);
+
+#endif /* SVARIABLE_RECEIVER_H */
diff --git a/src/include/tcop/dest.h b/src/include/tcop/dest.h
index 4e4f532d8cc..1e7043dc7f7 100644
--- a/src/include/tcop/dest.h
+++ b/src/include/tcop/dest.h
@@ -97,6 +97,7 @@ typedef enum
DestTransientRel, /* results sent to transient relation */
DestTupleQueue, /* results sent to tuple queue */
DestExplainSerialize, /* results are serialized and discarded */
+ DestVariable, /* results sent to session variable */
} CommandDest;
/* ----------------
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 04f1bd17dcc..22198dfafef 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2747,6 +2747,7 @@ STRLEN
SV
SVariableData
SVariable
+SVariableState
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
SampleScan
--
2.53.0
[text/x-patch] v20260403-2-0003-collect-session-variables-used-in-plan-and-assign-pa.patch (15.9K, 7-v20260403-2-0003-collect-session-variables-used-in-plan-and-assign-pa.patch)
download | inline diff:
From 04c34377c252050398ea77f8bd964d4102b7493e Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Fri, 21 Nov 2025 20:42:56 +0100
Subject: [PATCH 03/11] collect session variables used in plan and assign
paramid
In the plan stage we need to collect used session variables. On the
order of this list, the param nodes gets paramid (fix_param_node).
This number is used (later) as index to buffer of values of the
used session variables. The buffer is prepared and filled by executor.
Some unsupported optimizations are disabled:
* parallel execution
* simple expression execution in PL/pgSQL
* SQL functions inlining
Before execution of query with session variables we need to collect
used session variables. This list is used for loading variables to
executed query.
plan
---
doc/src/sgml/parallel.sgml | 6 ++
src/backend/catalog/dependency.c | 10 +++
src/backend/commands/session_variable.c | 39 ++++++++++
src/backend/optimizer/plan/planner.c | 11 +++
src/backend/optimizer/plan/setrefs.c | 94 ++++++++++++++++++++++-
src/backend/optimizer/prep/prepjointree.c | 3 +
src/backend/optimizer/util/clauses.c | 35 ++++++++-
src/backend/utils/fmgr/fmgr.c | 10 ++-
src/include/commands/session_variable.h | 2 +
src/include/nodes/pathnodes.h | 5 ++
src/include/nodes/plannodes.h | 3 +
src/include/optimizer/planmain.h | 2 +
12 files changed, 214 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/parallel.sgml b/doc/src/sgml/parallel.sgml
index af43484703e..843e2c3f663 100644
--- a/doc/src/sgml/parallel.sgml
+++ b/doc/src/sgml/parallel.sgml
@@ -524,6 +524,12 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
Plan nodes that reference a correlated <literal>SubPlan</literal>.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ Usage of a session variable.
+ </para>
+ </listitem>
</itemizedlist>
<sect2 id="parallel-labeling">
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index fdb8e67e1f5..7bd9200ec72 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1972,6 +1972,16 @@ find_expr_references_walker(Node *node,
{
Param *param = (Param *) node;
+ /*
+ * catalog less session variable variable cannot be used in persistent
+ * catalog based object.
+ */
+ if (param->paramkind == PARAM_VARIABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("session variable \"%s\" cannot be referenced in a catalog object",
+ param->paramvarname)));
+
/* A parameter must depend on the parameter's datatype */
add_object_address(TypeRelationId, param->paramtype, 0,
context->addrs);
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 482737d6797..19c153dae3f 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -128,6 +128,45 @@ get_session_variable_type_typmod_collid(char *varname,
*collid = svar->varcollation;
}
+/*
+ * Returns a copy of the value of the session variable (in the current memory
+ * context).
+ */
+Datum
+GetSessionVariableWithTypecheck(char *varname,
+ Oid typid, int32 typmod,
+ bool *isnull)
+{
+ SVariable svar;
+ Datum result;
+
+ svar = search_variable(varname);
+
+ if (svar->vartype != typid || svar->vartypmod != typmod)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("session variable %s is not of a type %s but type %s",
+ varname,
+ format_type_with_typemod(typid, typmod),
+ format_type_with_typemod(svar->vartype, svar->vartypmod))));
+
+ /* only owner can get content of variable */
+ if (svar->varowner != GetUserId() && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for session variable %s",
+ varname)));
+
+ if (!svar->isnull)
+ result = datumCopy(svar->value, svar->typbyval, svar->typlen);
+ else
+ result = (Datum) 0;
+
+ *isnull = svar->isnull;
+
+ return result;
+}
+
/*
* Creates a new variable - does new entry in sessionvars
*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 4ec76ce31a9..22f8008d238 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -375,6 +375,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
glob->dependsOnRole = false;
glob->partition_directory = NULL;
glob->rel_notnullatts_hash = NULL;
+ glob->sessionVariables = NIL;
/*
* Assess whether it's feasible to use parallel mode for this query. We
@@ -679,6 +680,9 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
/* utilityStmt should be null, but we might as well copy it */
result->utilityStmt = parse->utilityStmt;
result->elidedNodes = glob->elidedNodes;
+
+ result->sessionVariables = glob->sessionVariables;
+
result->stmt_location = parse->stmt_location;
result->stmt_len = parse->stmt_len;
@@ -874,6 +878,13 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name,
*/
pull_up_subqueries(root);
+ /*
+ * Check if some subquery uses a session variable. The flag
+ * hasSessionVariables should be true if the query or some subquery uses a
+ * session variable.
+ */
+ pull_up_has_session_variables(root);
+
/*
* If this is a simple UNION ALL query, flatten it into an appendrel. We
* do this now because it requires applying pull_up_subqueries to the leaf
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index ff0e875f2a2..e55c4df48ed 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -210,6 +210,8 @@ static List *set_returning_clause_references(PlannerInfo *root,
static List *set_windowagg_runcondition_references(PlannerInfo *root,
List *runcondition,
Plan *plan);
+static bool pull_up_has_session_variables_walker(Node *node,
+ PlannerInfo *root);
static void record_elided_node(PlannerGlobal *glob, int plan_node_id,
NodeTag elided_type, Bitmapset *relids);
@@ -1365,6 +1367,50 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
return plan;
}
+/*
+ * Search usage of session variables in subqueries
+ */
+void
+pull_up_has_session_variables(PlannerInfo *root)
+{
+ Query *query = root->parse;
+
+ if (query->hasSessionVariables)
+ {
+ root->hasSessionVariables = true;
+ }
+ else
+ {
+ (void) query_tree_walker(query,
+ pull_up_has_session_variables_walker,
+ (void *) root, 0);
+ }
+}
+
+static bool
+pull_up_has_session_variables_walker(Node *node, PlannerInfo *root)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Query))
+ {
+ Query *query = (Query *) node;
+
+ if (query->hasSessionVariables)
+ {
+ root->hasSessionVariables = true;
+ return false;
+ }
+
+ /* recurse into subselects */
+ return query_tree_walker((Query *) node,
+ pull_up_has_session_variables_walker,
+ (void *) root, 0);
+ }
+ return expression_tree_walker(node, pull_up_has_session_variables_walker,
+ (void *) root);
+}
+
/*
* set_indexonlyscan_references
* Do set_plan_references processing on an IndexOnlyScan
@@ -2192,6 +2238,10 @@ fix_expr_common(PlannerInfo *root, Node *node)
* If it's a PARAM_MULTIEXPR, replace it with the appropriate Param from
* root->multiexpr_params; otherwise no change is needed.
* Just for paranoia's sake, we make a copy of the node in either case.
+ *
+ * If it's a PARAM_VARIABLE, then we collect used session variables in
+ * the list root->glob->sessionVariable. Also, assign the parameter's
+ * "paramid" to the parameter's position in that list.
*/
static Node *
fix_param_node(PlannerInfo *root, Param *p)
@@ -2210,6 +2260,43 @@ fix_param_node(PlannerInfo *root, Param *p)
elog(ERROR, "unexpected PARAM_MULTIEXPR ID: %d", p->paramid);
return copyObject(list_nth(params, colno - 1));
}
+
+ if (p->paramkind == PARAM_VARIABLE)
+ {
+ int n = 0;
+
+ /* we will modify object */
+ p = (Param *) copyObject(p);
+
+ /*
+ * Now, we can actualize list of session variables, and we can
+ * complete paramid parameter.
+ */
+ foreach_node(Param, paramvar, root->glob->sessionVariables)
+ {
+ if (strcmp(paramvar->paramvarname, p->paramvarname) == 0)
+ {
+ p->paramid = paramvar->paramid;
+
+ return (Node *) p;
+ }
+
+ n += 1;
+ }
+
+ p->paramid = n;
+
+ /*
+ * Because session variables are catalogless, we cannot to use plan
+ * invalidation. Then we need to check type, typmod, collid any time,
+ * when we load values of session variables to parameter's buffer.
+ * For this purpose it is more easy to save complete Param node.
+ */
+ root->glob->sessionVariables = lappend(root->glob->sessionVariables, p);
+
+ return (Node *) p;
+ }
+
return (Node *) copyObject(p);
}
@@ -2277,7 +2364,9 @@ fix_alternative_subplan(PlannerInfo *root, AlternativeSubPlan *asplan,
* replacing Aggref nodes that should be replaced by initplan output Params,
* choosing the best implementation for AlternativeSubPlans,
* looking up operator opcode info for OpExpr and related nodes,
- * and adding OIDs from regclass Const nodes into root->glob->relationOids.
+ * adding OIDs from regclass Const nodes into root->glob->relationOids,
+ * assigning paramvarid to PARAM_VARIABLE params, and collecting the
+ * of session variables in the root->glob->sessionVariables list.
*
* 'node': the expression to be modified
* 'rtoffset': how much to increment varnos by
@@ -2299,7 +2388,8 @@ fix_scan_expr(PlannerInfo *root, Node *node, int rtoffset, double num_exec)
root->multiexpr_params != NIL ||
root->glob->lastPHId != 0 ||
root->minmax_aggs != NIL ||
- root->hasAlternativeSubPlans)
+ root->hasAlternativeSubPlans ||
+ root->hasSessionVariables)
{
return fix_scan_expr_mutator(node, &context);
}
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 95bf51606cc..053b070e609 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1713,6 +1713,9 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
/* If subquery had any RLS conditions, now main query does too */
parse->hasRowSecurity |= subquery->hasRowSecurity;
+ /* if the subquery had session variables, the main query does too */
+ parse->hasSessionVariables |= subquery->hasSessionVariables;
+
/*
* subquery won't be pulled up if it hasAggs, hasWindowFuncs, or
* hasTargetSRFs, so no work needed on those flags
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 9fb266d089d..682dc2d6cf7 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -26,6 +26,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
+#include "commands/session_variable.h"
#include "executor/executor.h"
#include "executor/functions.h"
#include "funcapi.h"
@@ -946,6 +947,13 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
if (param->paramkind == PARAM_EXTERN)
return false;
+ /* we don't support passing session variables to workers */
+ if (param->paramkind == PARAM_VARIABLE)
+ {
+ if (max_parallel_hazard_test(PROPARALLEL_RESTRICTED, context))
+ return true;
+ }
+
if (param->paramkind != PARAM_EXEC ||
!list_member_int(context->safe_param_ids, param->paramid))
{
@@ -2633,6 +2641,7 @@ convert_saop_to_hashed_saop_walker(Node *node, void *context)
* value of the Param.
* 2. Fold stable, as well as immutable, functions to constants.
* 3. Reduce PlaceHolderVar nodes to their contained expressions.
+ * 4. Current value of session variable can be used for estimation too.
*--------------------
*/
Node *
@@ -2759,6 +2768,29 @@ eval_const_expressions_mutator(Node *node,
}
}
}
+ else if (param->paramkind == PARAM_VARIABLE &&
+ context->estimate)
+ {
+ int16 typLen;
+ bool typByVal;
+ Datum pval;
+ bool isnull;
+
+ get_typlenbyval(param->paramtype, &typLen, &typByVal);
+
+ pval = GetSessionVariableWithTypecheck(param->paramvarname,
+ param->paramtype,
+ param->paramtypmod,
+ &isnull);
+
+ return (Node *) makeConst(param->paramtype,
+ param->paramtypmod,
+ param->paramcollid,
+ (int) typLen,
+ pval,
+ isnull,
+ typByVal);
+ }
/*
* Not replaceable, so just copy the Param (no need to
@@ -5434,7 +5466,8 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid,
querytree->limitOffset ||
querytree->limitCount ||
querytree->setOperations ||
- list_length(querytree->targetList) != 1)
+ (list_length(querytree->targetList) != 1) ||
+ querytree->hasSessionVariables)
goto fail;
/* If the function result is composite, resolve it */
diff --git a/src/backend/utils/fmgr/fmgr.c b/src/backend/utils/fmgr/fmgr.c
index bfeceb7a92f..24dfbef63f8 100644
--- a/src/backend/utils/fmgr/fmgr.c
+++ b/src/backend/utils/fmgr/fmgr.c
@@ -1992,9 +1992,13 @@ get_call_expr_arg_stable(Node *expr, int argnum)
*/
if (IsA(arg, Const))
return true;
- if (IsA(arg, Param) &&
- ((Param *) arg)->paramkind == PARAM_EXTERN)
- return true;
+ if (IsA(arg, Param))
+ {
+ Param *p = (Param *) arg;
+
+ if (p->paramkind == PARAM_EXTERN || p->paramkind == PARAM_VARIABLE)
+ return true;
+ }
return false;
}
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 96be968c3d4..3687490bcb1 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -22,6 +22,8 @@
extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
extern void DropVariableByName(char *varname);
+extern Datum GetSessionVariableWithTypecheck(char *varname, Oid typid, int32 typmod, bool *isnull);
+
extern void get_session_variable_type_typmod_collid(char *varname,
Oid *typid,
int32 *typmod,
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 693b879f76d..32c98bc7c1e 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -271,6 +271,9 @@ typedef struct PlannerGlobal
/* extension state */
void **extension_state pg_node_attr(read_write_ignore);
int extension_state_allocated;
+
+ /* list of used session variables */
+ List *sessionVariables;
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
@@ -641,6 +644,8 @@ struct PlannerInfo
bool hasRecursion;
/* true if a planner extension may replan this subquery */
bool assumeReplanning;
+ /* true if session variables were used */
+ bool hasSessionVariables;
/*
* The rangetable index for the RTE_GROUP RTE, or 0 if there is no
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 14a1dfed2b9..b33cb92a2ed 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -164,6 +164,9 @@ typedef struct PlannedStmt
*/
List *extension_state;
+ /* PARAM_VARIABLE Params */
+ List *sessionVariables;
+
/* statement location in source string (copied from Query) */
/* start location, or -1 if unknown */
ParseLoc stmt_location;
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index d0dc3761b13..46069ef2e9e 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -132,4 +132,6 @@ extern void record_plan_function_dependency(PlannerInfo *root, Oid funcid);
extern void record_plan_type_dependency(PlannerInfo *root, Oid typid);
extern bool extract_query_dependencies_walker(Node *node, PlannerInfo *context);
+extern void pull_up_has_session_variables(PlannerInfo *root);
+
#endif /* PLANMAIN_H */
--
2.53.0
[text/x-patch] v20260403-2-0006-LET-command-assign-a-result-of-expression-to-the-ses.patch (40.9K, 8-v20260403-2-0006-LET-command-assign-a-result-of-expression-to-the-ses.patch)
download | inline diff:
From b689e47f38aa6c5eae62423532e0a63e2afb88c3 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 24 Nov 2025 18:05:03 +0100
Subject: [PATCH 06/11] LET command - assign a result of expression to the
session variable
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
The value is assigned to session variables usually by SET command. Unfortunately
there are two reasons why SET should not be used for this purpose in Postgres.
1. Using a_expr inside generic_set ram rule produces reduce conflicts, so it needs
  total reimplementation of related gram rules.
2. SET is no plan command - so it doesn't support usage of parameters.
3. Excepting implementation issues, there is fact, so if we use SET command
  for assigning values to session variables, then there can be collisions
  between session variables and GUC, and then we need some concepts, how
  these collisions should be solved, or how to protect self against these
  collisions. With the dedicated command, the collisions between GUC and session
  variables are not possible.
The command LET is executed as usual query execution. The result is stored
to the target session variable (resultVariable) by using VariableDestReceiver.
Implementations of EXPLAIN LET and PREPARE LET statements are not supported
now. Postponed to next step due reducing patch size.
---
doc/src/sgml/ddl.sgml | 22 +++
doc/src/sgml/ref/allfiles.sgml | 1 +
doc/src/sgml/ref/create_variable.sgml | 3 +
doc/src/sgml/ref/drop_variable.sgml | 1 +
doc/src/sgml/ref/let.sgml | 95 +++++++++++
doc/src/sgml/reference.sgml | 1 +
src/backend/commands/session_variable.c | 87 ++++++++++
src/backend/nodes/nodeFuncs.c | 8 +
src/backend/optimizer/plan/planner.c | 1 +
src/backend/parser/analyze.c | 103 ++++++++++--
src/backend/parser/gram.y | 38 ++++-
src/backend/parser/parse_agg.c | 6 +
src/backend/parser/parse_expr.c | 9 +
src/backend/parser/parse_func.c | 3 +
src/backend/tcop/utility.c | 15 ++
src/bin/psql/tab-complete.in.c | 9 +-
src/include/commands/session_variable.h | 5 +
src/include/nodes/parsenodes.h | 15 ++
src/include/nodes/pathnodes.h | 3 +
src/include/parser/kwlist.h | 1 +
src/include/parser/parse_node.h | 1 +
src/include/tcop/cmdtaglist.h | 1 +
.../expected/session_variables_dml.out | 156 ++++++++++++++++++
.../regress/sql/session_variables_dml.sql | 113 +++++++++++++
src/tools/pgindent/typedefs.list | 1 +
25 files changed, 678 insertions(+), 20 deletions(-)
create mode 100644 doc/src/sgml/ref/let.sgml
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 0055eb84a78..91607a72188 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5960,10 +5960,32 @@ SELECT ... FROM GRAPH_TABLE (myshop MATCH (IS person WHERE name = '...')-[]->...
<literal>VARIABLE(varname)</literal> syntax. This avoids any risk of
collision between variable names and column names.
</para>
+
+ <para>
+ You set the value of a session variable with the <command>LET</command>
+ statement and retrieve it with <command>SELECT</command>:
+<programlisting>
+CREATE TEMP VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT VARIABLE(var1);
+</programlisting>
+
+ or
+
<programlisting>
+CREATE TEMP VARIABLE current_user_id AS integer;
+LET current_user_id = (SELECT id FROM users WHERE usename = session_user);
SELECT VARIABLE(current_user_id);
</programlisting>
</para>
+
+ <para>
+ 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
+ </para>
</sect1>
<sect1 id="ddl-others">
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 35d485f5bc4..a0a6150feab 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -160,6 +160,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY grant SYSTEM "grant.sgml">
<!ENTITY importForeignSchema SYSTEM "import_foreign_schema.sgml">
<!ENTITY insert SYSTEM "insert.sgml">
+<!ENTITY let SYSTEM "let.sgml">
<!ENTITY listen SYSTEM "listen.sgml">
<!ENTITY load SYSTEM "load.sgml">
<!ENTITY lock SYSTEM "lock.sgml">
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
index 4e8c1940252..1315b1248c7 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -108,6 +108,8 @@ CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replac
Create an date session variable <literal>var1</literal>:
<programlisting>
CREATE TEMPORARY VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT VARIABLE(var1);
</programlisting>
</para>
@@ -127,6 +129,7 @@ CREATE TEMPORARY VARIABLE var1 AS date;
<simplelist type="inline">
<member><xref linkend="sql-dropvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
</simplelist>
</refsect1>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
index e8517a78200..dede42e4ffb 100644
--- a/doc/src/sgml/ref/drop_variable.sgml
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -78,6 +78,7 @@ DROP VARIABLE var1;
<simplelist type="inline">
<member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
</simplelist>
</refsect1>
diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml
new file mode 100644
index 00000000000..33ee42d3f20
--- /dev/null
+++ b/doc/src/sgml/ref/let.sgml
@@ -0,0 +1,95 @@
+<!--
+doc/src/sgml/ref/let.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-let">
+ <indexterm zone="sql-let">
+ <primary>LET</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>changing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>LET</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>LET</refname>
+ <refpurpose>change a session variable's value</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+LET <replaceable class="parameter">session_variable</replaceable> = <replaceable class="parameter">sql_expression</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>LET</command> command assigns a value to the specified session
+ variable.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">session_variable</replaceable></term>
+ <listitem>
+ <para>
+ The name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">sql_expression</replaceable></term>
+ <listitem>
+ <para>
+ An arbitrary SQL expression. The result must be of a data type that can
+ be cast to the type of the session variable in an assignment.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+<programlisting>
+CREATE TEMPORARY VARIABLE myvar AS integer;
+LET myvar = 10;
+LET myvar = (SELECT sum(val) FROM tab);
+</programlisting>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>LET</command> is a <productname>PostgreSQL</productname>
+ extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 342a7afb517..4d5a88f753b 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -188,6 +188,7 @@
&grant;
&importForeignSchema;
&insert;
+ &let;
&listen;
&load;
&lock;
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index cd171a68cbf..abcb0bb531a 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -17,13 +17,18 @@
#include "catalog/pg_language.h"
#include "catalog/pg_type.h"
#include "commands/session_variable.h"
+#include "executor/executor.h"
+#include "executor/svariableReceiver.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
+#include "rewrite/rewriteHandler.h"
#include "storage/proc.h"
+#include "tcop/tcopprot.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/snapmgr.h"
/*
* The session variables are stored in the backend's private memory (data,
@@ -324,3 +329,85 @@ DropVariableByName(char *varname)
NULL) == NULL)
elog(ERROR, "hash table corrupted");
}
+
+/*
+ * Assign the result of the evaluated expression to the session variable
+ */
+void
+ExecuteLetStmt(ParseState *pstate,
+ LetStmt *stmt,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv,
+ QueryCompletion *qc)
+{
+ Query *query = castNode(Query, stmt->query);
+ List *rewritten;
+ DestReceiver *dest;
+ PlannedStmt *plan;
+ QueryDesc *queryDesc;
+ char *varname = query->resultVariable;
+ SVariable svar;
+
+ svar = search_variable(varname);
+
+ /* only owner can set content of variable */
+ if (svar->varowner != GetUserId() && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for session variable %s",
+ varname)));
+
+ /* create a dest receiver for LET */
+ dest = CreateVariableDestReceiver(varname);
+
+ /* run the query rewriter */
+ query = copyObject(query);
+
+ rewritten = QueryRewrite(query);
+
+ Assert(list_length(rewritten) == 1);
+
+ query = linitial_node(Query, rewritten);
+ Assert(query->commandType == CMD_SELECT);
+
+ /* plan the query */
+ plan = pg_plan_query(query, pstate->p_sourcetext,
+ CURSOR_OPT_PARALLEL_OK, params, NULL);
+
+ /*
+ * Use a snapshot with an updated command ID to ensure this query sees the
+ * results of any previously executed queries. (This could only matter if
+ * the planner executed an allegedly-stable function that changed the
+ * database contents, but let's do it anyway to be parallel to the EXPLAIN
+ * code path.)
+ */
+ PushCopiedSnapshot(GetActiveSnapshot());
+ UpdateActiveSnapshotCommandId();
+
+ /* create a QueryDesc, redirecting output to our tuple receiver */
+ queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext,
+ GetActiveSnapshot(), InvalidSnapshot,
+ dest, params, queryEnv, 0);
+
+ /* call ExecutorStart to prepare the plan for execution */
+ ExecutorStart(queryDesc, 0);
+
+ /*
+ * Run the plan to completion. The result should be only one row. To
+ * check if there are too many result rows, we try to fetch two.
+ */
+ ExecutorRun(queryDesc, ForwardScanDirection, 2L);
+
+ /* save the rowcount if we're given a QueryCompletion to fill */
+ if (qc)
+ SetQueryCompletion(qc, CMDTAG_LET, queryDesc->estate->es_processed);
+
+ /* and clean up */
+ ExecutorFinish(queryDesc);
+ ExecutorEnd(queryDesc);
+
+ dest->rDestroy(dest);
+ FreeQueryDesc(queryDesc);
+
+ PopActiveSnapshot();
+}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 0f6990f1b84..9efca476866 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4449,6 +4449,14 @@ raw_expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_LetStmt:
+ {
+ LetStmt *stmt = (LetStmt *) node;
+
+ if (WALK(stmt->query))
+ return true;
+ }
+ break;
case T_PLAssignStmt:
{
PLAssignStmt *stmt = (PLAssignStmt *) node;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 22f8008d238..5f04603d026 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -376,6 +376,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
glob->partition_directory = NULL;
glob->rel_notnullatts_hash = NULL;
glob->sessionVariables = NIL;
+ glob->resultVariable = parse->resultVariable;
/*
* Assess whether it's feasible to use parallel mode for this query. We
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index a2b9ce66e12..409d3ee411b 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -59,15 +59,18 @@
#include "utils/lsyscache.h"
#include "utils/rangetypes.h"
#include "utils/rel.h"
+#include "utils/lsyscache.h"
#include "utils/syscache.h"
-/* Passthrough data for transformPLAssignStmtTarget */
+/* Passthrough data for transformAssignTarget */
typedef struct SelectStmtPassthrough
{
- PLAssignStmt *stmt; /* the assignment statement */
+ Node *stmt; /* the assignment statement */
Node *target; /* node representing the target variable */
+ char *target_name; /* the name used by err */
List *indirection; /* indirection yet to be applied to target */
+ CoercionContext ccontext; /* context indicators to control coercions */
} SelectStmtPassthrough;
/* Hook for plugins to get control at end of parse analysis */
@@ -95,7 +98,7 @@ static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
static Query *transformPLAssignStmt(ParseState *pstate,
PLAssignStmt *stmt);
-static List *transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
+static List *transformAssignTarget(ParseState *pstate, List *tlist,
SelectStmtPassthrough *passthru);
static Query *transformDeclareCursorStmt(ParseState *pstate,
DeclareCursorStmt *stmt);
@@ -105,6 +108,8 @@ static Query *transformCreateTableAsStmt(ParseState *pstate,
CreateTableAsStmt *stmt);
static Query *transformCallStmt(ParseState *pstate,
CallStmt *stmt);
+static Query *transformLetStmt(ParseState *pstate,
+ LetStmt *stmt);
static void transformLockingClause(ParseState *pstate, Query *qry,
LockingClause *lc, bool pushedDown);
#ifdef DEBUG_NODE_TESTS_ENABLED
@@ -352,6 +357,7 @@ transformStmt(ParseState *pstate, Node *parseTree)
case T_UpdateStmt:
case T_DeleteStmt:
case T_MergeStmt:
+ case T_LetStmt:
(void) test_raw_expression_coverage(parseTree, NULL);
break;
default:
@@ -431,6 +437,11 @@ transformStmt(ParseState *pstate, Node *parseTree)
(CallStmt *) parseTree);
break;
+ case T_LetStmt:
+ result = transformLetStmt(pstate,
+ (LetStmt *) parseTree);
+ break;
+
default:
/*
@@ -492,6 +503,7 @@ stmt_requires_parse_analysis(RawStmt *parseTree)
case T_ExplainStmt:
case T_CreateTableAsStmt:
case T_CallStmt:
+ case T_LetStmt:
result = true;
break;
@@ -557,6 +569,7 @@ query_requires_rewrite_plan(Query *query)
case T_ExplainStmt:
case T_CreateTableAsStmt:
case T_CallStmt:
+ case T_LetStmt:
result = true;
break;
default:
@@ -1729,7 +1742,7 @@ count_rowexpr_columns(ParseState *pstate, Node *expr)
*
* This function is also used to transform the source expression of a
* PLAssignStmt. In that usage, passthru is non-NULL and we need to
- * call transformPLAssignStmtTarget after the initial transformation of the
+ * call transformAssignTarget after the initial transformation of the
* SELECT's targetlist. (We could generalize this into an arbitrary callback
* function, but for now that would just be more notation with no benefit.)
* All the rest is the same as a regular SelectStmt.
@@ -1782,8 +1795,8 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt,
* Otherwise, mark column origins (which are useless in a PLAssignStmt).
*/
if (passthru)
- qry->targetList = transformPLAssignStmtTarget(pstate, qry->targetList,
- passthru);
+ qry->targetList = transformAssignTarget(pstate, qry->targetList,
+ passthru);
else
markTargetListOrigins(pstate, qry->targetList);
@@ -3221,9 +3234,11 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt)
EXPR_KIND_UPDATE_TARGET);
/* Set up passthrough data for transformPLAssignStmtTarget */
- passthru.stmt = stmt;
+ passthru.stmt = (Node *) stmt;
passthru.target = target;
+ passthru.target_name = stmt->name;
passthru.indirection = indirection;
+ passthru.ccontext = COERCION_PLPGSQL;
/*
* To avoid duplicating a lot of code, we use transformSelectStmt to do
@@ -3246,18 +3261,21 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt)
/*
* Callback function to adjust a SELECT's tlist to make the output suitable
- * for assignment to a PLAssignStmt's target variable.
+ * for assignment to a PLAssignStmt's target variable pr LET's target
+ * session variable.
*
* Note: we actually modify the tle->expr in-place, but the function's API
* is set up to not presume that.
*/
static List *
-transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
- SelectStmtPassthrough *passthru)
+transformAssignTarget(ParseState *pstate, List *tlist,
+ SelectStmtPassthrough *passthru)
{
- PLAssignStmt *stmt = passthru->stmt;
+ Node *stmt = passthru->stmt;
Node *target = passthru->target;
+ char *target_name = passthru->target_name;
List *indirection = passthru->indirection;
+ CoercionContext ccontext = passthru->ccontext;
Oid targettype;
int32 targettypmod;
Oid targetcollation;
@@ -3292,7 +3310,7 @@ transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
tle->expr = (Expr *)
transformAssignmentIndirection(pstate,
target,
- stmt->name,
+ target_name,
false,
targettype,
targettypmod,
@@ -3300,10 +3318,10 @@ transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
indirection,
list_head(indirection),
(Node *) tle->expr,
- COERCION_PLPGSQL,
+ ccontext,
exprLocation(target));
}
- else if (targettype != type_id &&
+ else if (IsA(stmt, PLAssignStmt) && targettype != type_id &&
(targettype == RECORDOID || ISCOMPLEX(targettype)) &&
(type_id == RECORDOID || ISCOMPLEX(type_id)))
{
@@ -3326,7 +3344,7 @@ transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
coerce_to_target_type(pstate,
orig_expr, type_id,
targettype, targettypmod,
- COERCION_PLPGSQL,
+ ccontext,
COERCE_IMPLICIT_CAST,
-1);
/* With COERCION_PLPGSQL, this error is probably unreachable */
@@ -3335,7 +3353,7 @@ transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("variable \"%s\" is of type %s"
" but expression is of type %s",
- stmt->name,
+ target_name,
format_type_be(targettype),
format_type_be(type_id)),
errhint("You will need to rewrite or cast the expression."),
@@ -3703,6 +3721,59 @@ transformCallStmt(ParseState *pstate, CallStmt *stmt)
return result;
}
+/*
+ * transformLetStmt -
+ * transform an Let Statement
+ */
+static Query *
+transformLetStmt(ParseState *pstate, LetStmt *stmt)
+{
+ Query *qry;
+ Query *result;
+ Node *target;
+ VariableFence *vf;
+ SelectStmtPassthrough passthru;
+ Param *paramvar;
+
+ /* gram allows only SELECT */
+ Assert(IsA(stmt->query, SelectStmt));
+
+ /* Use implicit VariableFence for forcing session variables */
+ vf = makeNode(VariableFence);
+ vf->varname = stmt->target;
+ vf->location = stmt->location;
+
+ target = transformExpr(pstate, (Node *) vf, EXPR_KIND_LET_TARGET);
+
+ paramvar = castNode(Param, target);
+
+ Assert(paramvar->paramkind == PARAM_VARIABLE);
+
+ /* Set up passthrough data for transformAssignTarget */
+ passthru.stmt = (Node *) stmt;
+ passthru.target = (Node *) paramvar;
+ passthru.target_name = paramvar->paramvarname;
+ passthru.indirection = NIL;
+ passthru.ccontext = COERCION_ASSIGNMENT;
+
+ /* we need to postpone conversion of "unknown" to text */
+ pstate->p_resolve_unknowns = false;
+
+ qry = transformSelectStmt(pstate, (SelectStmt *) stmt->query, &passthru);
+
+ qry->resultVariable = paramvar->paramvarname;
+ qry->canSetTag = true;
+
+ stmt->query = (Node *) qry;
+
+ /* represent the command as a utility Query */
+ result = makeNode(Query);
+ result->commandType = CMD_UTILITY;
+ result->utilityStmt = (Node *) stmt;
+
+ return result;
+}
+
/*
* Produce a string representation of a LockClauseStrength value.
* This should only be applied to valid values (not LCS_NONE).
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f3a4aeef3fe..90821c62087 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -304,7 +304,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
- ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt PreparableStmt
+ LetStmt ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RepackStmt ReturnStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
@@ -786,7 +786,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
KEEP KEY KEYS
LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
- LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
+ LEADING LEAKPROOF LEAST LET LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED LSN_P
MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE MERGE_ACTION METHOD
@@ -1138,6 +1138,7 @@ stmt:
| ImportForeignSchemaStmt
| IndexStmt
| InsertStmt
+ | LetStmt
| ListenStmt
| RefreshMatViewStmt
| LoadStmt
@@ -13621,6 +13622,37 @@ opt_hold: /* EMPTY */ { $$ = 0; }
| WITHOUT HOLD { $$ = 0; }
;
+/*****************************************************************************
+ *
+ * QUERY:
+ * LET STATEMENT
+ *
+ *****************************************************************************/
+LetStmt: LET ColId '=' a_expr
+ {
+ LetStmt *n = makeNode(LetStmt);
+ SelectStmt *select;
+ ResTarget *res;
+
+ n->target = $2;
+
+ select = makeNode(SelectStmt);
+ res = makeNode(ResTarget);
+
+ /* create target list for implicit query */
+ res->name = NULL;
+ res->indirection = NIL;
+ res->val = (Node *) $4;
+ res->location = @4;
+
+ select->targetList = list_make1(res);
+ n->query = (Node *) select;
+
+ n->location = @2;
+ $$ = (Node *) n;
+ }
+ ;
+
/*****************************************************************************
*
* QUERY:
@@ -19021,6 +19053,7 @@ unreserved_keyword:
| LARGE_P
| LAST_P
| LEAKPROOF
+ | LET
| LEVEL
| LISTEN
| LOAD
@@ -19651,6 +19684,7 @@ bare_label_keyword:
| LEAKPROOF
| LEAST
| LEFT
+ | LET
| LEVEL
| LIKE
| LISTEN
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index acb933392de..92fb1ef43a1 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -597,7 +597,10 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
err = _("aggregate functions are not allowed in property definition expressions");
else
err = _("grouping operations are not allowed in property definition expressions");
+ break;
+ case EXPR_KIND_LET_TARGET:
+ errkind = true;
break;
/*
@@ -1045,6 +1048,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_FOR_PORTION:
err = _("window functions are not allowed in FOR PORTION OF expressions");
break;
+ case EXPR_KIND_LET_TARGET:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 4b995ab4a95..d9116c3bfd5 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -597,6 +597,9 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_FOR_PORTION:
err = _("cannot use column reference in FOR PORTION OF expression");
break;
+ case EXPR_KIND_LET_TARGET:
+ err = _("cannot use column reference as target of LET command");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -979,6 +982,7 @@ expr_kind_allows_session_variables(ParseExprKind p_expr_kind)
case EXPR_KIND_VALUES_SINGLE:
case EXPR_KIND_PROPGRAPH_PROPERTY:
case EXPR_KIND_FOR_PORTION:
+ case EXPR_KIND_LET_TARGET:
result = true;
break;
@@ -2013,6 +2017,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_FOR_PORTION:
err = _("cannot use subquery in FOR PORTION OF expression");
break;
+ case EXPR_KIND_LET_TARGET:
+ err = _("cannot use subquery as a target of LET command");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -3376,6 +3383,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "property definition expression";
case EXPR_KIND_FOR_PORTION:
return "FOR PORTION OF";
+ case EXPR_KIND_LET_TARGET:
+ return "LET";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 35ff6427147..8ddb5a4e219 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2789,6 +2789,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_FOR_PORTION:
err = _("set-returning functions are not allowed in FOR PORTION OF expressions");
break;
+ case EXPR_KIND_LET_TARGET:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 269983987eb..fda4e019b13 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -240,6 +240,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_CallStmt:
case T_DoStmt:
+ case T_LetStmt:
{
/*
* Commands inside the DO block or the called procedure might
@@ -1078,6 +1079,11 @@ standard_ProcessUtility(PlannedStmt *pstmt,
DropVariableByName(((DropSessionVarStmt *) parsetree)->name);
break;
+ case T_LetStmt:
+ ExecuteLetStmt(pstate, (LetStmt *) parsetree, params,
+ queryEnv, qc);
+ break;
+
default:
/* All other statement types have event trigger support */
ProcessUtilitySlow(pstate, pstmt, queryString,
@@ -2232,6 +2238,10 @@ UtilityContainsQuery(Node *parsetree)
return UtilityContainsQuery(qry->utilityStmt);
return qry;
+ case T_LetStmt:
+ qry = castNode(Query, ((LetStmt *) parsetree)->query);
+ return qry;
+
default:
return NULL;
}
@@ -2430,6 +2440,10 @@ CreateCommandTag(Node *parsetree)
tag = CMDTAG_SELECT;
break;
+ case T_LetStmt:
+ tag = CMDTAG_LET;
+ break;
+
/* utility statements --- same whether raw or cooked */
case T_TransactionStmt:
{
@@ -3334,6 +3348,7 @@ GetCommandLogLevel(Node *parsetree)
break;
case T_PLAssignStmt:
+ case T_LetStmt:
lev = LOGSTMT_ALL;
break;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index fe012829ac0..69f348c129e 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1273,8 +1273,8 @@ static const char *const sql_commands[] = {
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CALL", "CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN",
- "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LISTEN", "LOAD", "LOCK",
- "MERGE INTO", "MOVE", "NOTIFY", "PREPARE",
+ "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LET", "LISTEN",
+ "LOAD", "LOCK", "MERGE INTO", "MOVE", "NOTIFY", "PREPARE",
"REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", "REPACK",
"RESET", "REVOKE", "ROLLBACK",
"SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START",
@@ -4953,6 +4953,11 @@ match_previous_words(int pattern_id,
else if (TailMatches("VALUES") && !TailMatches("DEFAULT", "VALUES"))
COMPLETE_WITH("(");
+/* LET */
+ /* Complete LET <variable> with "=" */
+ else if (TailMatches("LET", MatchAny))
+ COMPLETE_WITH("=");
+
/* LOCK */
/* Complete LOCK [TABLE] [ONLY] with a list of tables */
else if (Matches("LOCK"))
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 610b757899e..c4b4d9e6832 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -16,8 +16,10 @@
#define SESSIONVARIABLE_H
#include "catalog/objectaddress.h"
+#include "nodes/params.h"
#include "parser/parse_node.h"
#include "nodes/parsenodes.h"
+#include "tcop/cmdtag.h"
extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
extern void DropVariableByName(char *varname);
@@ -32,4 +34,7 @@ extern void get_session_variable_type_typmod_collid(char *varname,
int32 *typmod,
Oid *collid);
+extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo params,
+ QueryEnvironment *queryEnv, QueryCompletion *qc);
+
#endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 97f63a75939..7d77a368b06 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -150,6 +150,9 @@ typedef struct Query
/* FOR PORTION OF clause for UPDATE/DELETE */
ForPortionOfExpr *forPortionOf;
+ /* target variable of LET statement */
+ char *resultVariable;
+
/* has aggregates in tlist or havingQual */
bool hasAggs pg_node_attr(query_jumble_ignore);
/* has window functions in tlist */
@@ -2276,6 +2279,18 @@ typedef struct MergeStmt
WithClause *withClause; /* WITH clause */
} MergeStmt;
+/* ----------------------
+ * Let Statement
+ * ----------------------
+ */
+typedef struct LetStmt
+{
+ NodeTag type;
+ char *target; /* target variable */
+ Node *query; /* source expression */
+ ParseLoc location;
+} LetStmt;
+
/* ----------------------
* Select Statement
*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 32c98bc7c1e..4ba6f709cf2 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -274,6 +274,9 @@ typedef struct PlannerGlobal
/* list of used session variables */
List *sessionVariables;
+
+ /* name of session variable used like target of LET command */
+ char *resultVariable;
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 491347970a2..3bc10aab33f 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -262,6 +262,7 @@ PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("leakproof", LEAKPROOF, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("left", LEFT, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("let", LET, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("level", LEVEL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("like", LIKE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("limit", LIMIT, RESERVED_KEYWORD, AS_LABEL)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 00d77a1abc4..7f0d5fbb1b0 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -84,6 +84,7 @@ typedef enum ParseExprKind
EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */
EXPR_KIND_CYCLE_MARK, /* cycle mark value */
EXPR_KIND_PROPGRAPH_PROPERTY, /* derived property expression */
+ EXPR_KIND_LET_TARGET, /* only session variables */
} ParseExprKind;
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 35229a16add..5ff586e14ef 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -188,6 +188,7 @@ PG_CMDTAG(CMDTAG_GRANT, "GRANT", true, false, false)
PG_CMDTAG(CMDTAG_GRANT_ROLE, "GRANT ROLE", false, false, false)
PG_CMDTAG(CMDTAG_IMPORT_FOREIGN_SCHEMA, "IMPORT FOREIGN SCHEMA", true, false, false)
PG_CMDTAG(CMDTAG_INSERT, "INSERT", false, false, true)
+PG_CMDTAG(CMDTAG_LET, "LET", false, false, false)
PG_CMDTAG(CMDTAG_LISTEN, "LISTEN", false, false, false)
PG_CMDTAG(CMDTAG_LOAD, "LOAD", false, false, false)
PG_CMDTAG(CMDTAG_LOCK_TABLE, "LOCK TABLE", false, false, false)
diff --git a/src/test/regress/expected/session_variables_dml.out b/src/test/regress/expected/session_variables_dml.out
index 1519bf723e0..599751ec5c8 100644
--- a/src/test/regress/expected/session_variables_dml.out
+++ b/src/test/regress/expected/session_variables_dml.out
@@ -133,3 +133,159 @@ RESET min_parallel_table_scan_size;
RESET max_parallel_workers_per_gather;
DROP TABLE testvar_testtab;
DROP VARIABLE temp_var02;
+CREATE TEMP VARIABLE temp_var03 AS numeric;
+-- LET stmt is not allowed inside CTE
+WITH x AS (LET temp_var03 = 3.14) SELECT * FROM x;
+ERROR: syntax error at or near "LET"
+LINE 1: WITH x AS (LET temp_var03 = 3.14) SELECT * FROM x;
+ ^
+-- LET stmt requires result with exactly one row
+LET temp_var03 = generate_series(1,1);
+SELECT VARIABLE(temp_var03);
+ temp_var03
+------------
+ 1
+(1 row)
+
+-- should fail
+LET temp_var03 = generate_series(1,2);
+ERROR: expression returned more than one row
+LET temp_var03 = generate_series(1,0);
+ERROR: expression returned no rows
+CREATE OR REPLACE FUNCTION testvar_sql01(numeric)
+RETURNS void AS $$
+LET temp_var03 = $1;
+$$ LANGUAGE sql;
+CREATE OR REPLACE FUNCTION testvar_sql02()
+RETURNS numeric AS $$
+SELECT VARIABLE(temp_var03);
+$$ LANGUAGE sql;
+SELECT testvar_sql01(3.14);
+ testvar_sql01
+---------------
+
+(1 row)
+
+SELECT testvar_sql02(), VARIABLE(temp_var03);
+ testvar_sql02 | temp_var03
+---------------+------------
+ 3.14 | 3.14
+(1 row)
+
+CREATE OR REPLACE FUNCTION testvar_pl(varchar)
+RETURNS varchar AS $$
+BEGIN
+ LET temp_var03 = $1::numeric;
+ RETURN VARIABLE(temp_var03);
+END
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+SELECT testvar_pl('3.14');
+ testvar_pl
+------------
+ 3.14
+(1 row)
+
+DROP VARIABLE temp_var03;
+SET plan_cache_mode to force_generic_plan;
+-- should not crash
+SELECT testvar_sql01(3.14);
+ERROR: session variable "temp_var03" doesn't exist
+CONTEXT: SQL function "testvar_sql01" during inlining
+SELECT testvar_sql02(), VARIABLE(temp_var03);
+ERROR: session variable "temp_var03" doesn't exist
+SELECT testvar_pl('3.141592');
+ERROR: session variable "temp_var03" doesn't exist
+CONTEXT: SQL statement "LET temp_var03 = $1::numeric"
+PL/pgSQL function testvar_pl(character varying) line 3 at SQL statement
+-- can work again if we create variable
+CREATE TEMP VARIABLE temp_var03 AS numeric;
+SELECT testvar_sql01(3.14);
+ testvar_sql01
+---------------
+
+(1 row)
+
+SELECT testvar_sql02(), VARIABLE(temp_var03);
+ testvar_sql02 | temp_var03
+---------------+------------
+ 3.14 | 3.14
+(1 row)
+
+SELECT testvar_pl('3.141592');
+ testvar_pl
+------------
+ 3.141592
+(1 row)
+
+CREATE ROLE regress_session_variable_test_role_04;
+SET ROLE regress_session_variable_test_role_04;
+-- should fail
+SELECT testvar_sql01(3.14);
+ERROR: permission denied for session variable temp_var03
+CONTEXT: SQL function "testvar_sql01" statement 1
+-- should be ok (security definer)
+SELECT testvar_pl('3.141592');
+ testvar_pl
+------------
+ 3.141592
+(1 row)
+
+SET ROLE TO DEFAULT;
+DROP FUNCTION testvar_sql01(numeric);
+DROP FUNCTION testvar_sql02();
+DROP FUNCTION testvar_pl(varchar);
+DROP ROLE regress_session_variable_test_role_04;
+DROP VARIABLE temp_var03;
+SET plan_cache_mode TO DEFAULT;
+-- test extended query protocol
+CREATE TEMP VARIABLE temp_var04 AS int;
+LET temp_var04 = $1 \bind 10 \g
+SELECT VARIABLE(temp_var04);
+ temp_var04
+------------
+ 10
+(1 row)
+
+LET temp_var04 = $1 \parse letps
+\bind_named letps 100 \g
+SELECT VARIABLE(temp_var04);
+ temp_var04
+------------
+ 100
+(1 row)
+
+\close_prepared letps
+DROP VARIABLE temp_var04;
+-- original value should not be changed when LET fails
+CREATE TEMP VARIABLE temp_var04 AS numeric;
+LET temp_var04 = 42;
+LET temp_var04 = generate_series(1,2); -- ERROR: too many row
+ERROR: expression returned more than one row
+SELECT VARIABLE(temp_var04); -- expected 42
+ temp_var04
+------------
+ 42
+(1 row)
+
+DROP VARIABLE temp_var04;
+CREATE TEMP VARIABLE temp_var04 AS int;
+LET temp_var04 = 42;
+LET temp_var04 = generate_series(1,2); -- ERROR: too many row
+ERROR: expression returned more than one row
+SELECT VARIABLE(temp_var04); -- expected 42
+ temp_var04
+------------
+ 42
+(1 row)
+
+DROP VARIABLE temp_var04;
+CREATE TEMP VARIABLE temp_var04 AS int;
+LET temp_var04 = generate_series(1,2); -- ERROR: too many row
+ERROR: expression returned more than one row
+SELECT VARIABLE(temp_var04); -- expected NULL
+ temp_var04
+------------
+
+(1 row)
+
+DROP VARIABLE temp_var04;
diff --git a/src/test/regress/sql/session_variables_dml.sql b/src/test/regress/sql/session_variables_dml.sql
index bf56b19467b..fcb429f6bbf 100644
--- a/src/test/regress/sql/session_variables_dml.sql
+++ b/src/test/regress/sql/session_variables_dml.sql
@@ -118,3 +118,116 @@ RESET max_parallel_workers_per_gather;
DROP TABLE testvar_testtab;
DROP VARIABLE temp_var02;
+
+CREATE TEMP VARIABLE temp_var03 AS numeric;
+
+-- LET stmt is not allowed inside CTE
+WITH x AS (LET temp_var03 = 3.14) SELECT * FROM x;
+
+-- LET stmt requires result with exactly one row
+LET temp_var03 = generate_series(1,1);
+SELECT VARIABLE(temp_var03);
+
+-- should fail
+LET temp_var03 = generate_series(1,2);
+LET temp_var03 = generate_series(1,0);
+
+CREATE OR REPLACE FUNCTION testvar_sql01(numeric)
+RETURNS void AS $$
+LET temp_var03 = $1;
+$$ LANGUAGE sql;
+
+CREATE OR REPLACE FUNCTION testvar_sql02()
+RETURNS numeric AS $$
+SELECT VARIABLE(temp_var03);
+$$ LANGUAGE sql;
+
+SELECT testvar_sql01(3.14);
+SELECT testvar_sql02(), VARIABLE(temp_var03);
+
+CREATE OR REPLACE FUNCTION testvar_pl(varchar)
+RETURNS varchar AS $$
+BEGIN
+ LET temp_var03 = $1::numeric;
+ RETURN VARIABLE(temp_var03);
+END
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+
+SELECT testvar_pl('3.14');
+
+DROP VARIABLE temp_var03;
+
+SET plan_cache_mode to force_generic_plan;
+
+-- should not crash
+SELECT testvar_sql01(3.14);
+SELECT testvar_sql02(), VARIABLE(temp_var03);
+SELECT testvar_pl('3.141592');
+
+-- can work again if we create variable
+CREATE TEMP VARIABLE temp_var03 AS numeric;
+SELECT testvar_sql01(3.14);
+SELECT testvar_sql02(), VARIABLE(temp_var03);
+SELECT testvar_pl('3.141592');
+
+CREATE ROLE regress_session_variable_test_role_04;
+
+SET ROLE regress_session_variable_test_role_04;
+
+-- should fail
+SELECT testvar_sql01(3.14);
+
+-- should be ok (security definer)
+SELECT testvar_pl('3.141592');
+
+SET ROLE TO DEFAULT;
+
+DROP FUNCTION testvar_sql01(numeric);
+DROP FUNCTION testvar_sql02();
+DROP FUNCTION testvar_pl(varchar);
+
+DROP ROLE regress_session_variable_test_role_04;
+
+DROP VARIABLE temp_var03;
+
+SET plan_cache_mode TO DEFAULT;
+
+-- test extended query protocol
+CREATE TEMP VARIABLE temp_var04 AS int;
+
+LET temp_var04 = $1 \bind 10 \g
+SELECT VARIABLE(temp_var04);
+
+LET temp_var04 = $1 \parse letps
+\bind_named letps 100 \g
+SELECT VARIABLE(temp_var04);
+
+\close_prepared letps
+
+DROP VARIABLE temp_var04;
+
+-- original value should not be changed when LET fails
+CREATE TEMP VARIABLE temp_var04 AS numeric;
+
+LET temp_var04 = 42;
+
+LET temp_var04 = generate_series(1,2); -- ERROR: too many row
+SELECT VARIABLE(temp_var04); -- expected 42
+
+DROP VARIABLE temp_var04;
+
+CREATE TEMP VARIABLE temp_var04 AS int;
+
+LET temp_var04 = 42;
+
+LET temp_var04 = generate_series(1,2); -- ERROR: too many row
+SELECT VARIABLE(temp_var04); -- expected 42
+
+DROP VARIABLE temp_var04;
+
+CREATE TEMP VARIABLE temp_var04 AS int;
+
+LET temp_var04 = generate_series(1,2); -- ERROR: too many row
+SELECT VARIABLE(temp_var04); -- expected NULL
+
+DROP VARIABLE temp_var04;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 22198dfafef..85bd4262f3c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1598,6 +1598,7 @@ LargeObjectDesc
Latch
LauncherLastStartTimesEntry
LerpFunc
+LetStmt
LexDescr
LexemeEntry
LexemeHashKey
--
2.53.0
[text/x-patch] v20260403-2-0007-DISCARD-TEMP.patch (4.4K, 9-v20260403-2-0007-DISCARD-TEMP.patch)
download | inline diff:
From efc1b0b978781eebcdf92d1d1a88119c8aa49c0b Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 24 Nov 2025 20:04:16 +0100
Subject: [PATCH 07/11] DISCARD TEMP
---
doc/src/sgml/ref/discard.sgml | 3 ++-
src/backend/commands/discard.c | 3 +++
src/backend/commands/session_variable.c | 20 +++++++++++++++++++
src/include/commands/session_variable.h | 2 ++
.../expected/session_variables_ddl.out | 7 +++++++
.../regress/sql/session_variables_ddl.sql | 10 ++++++++++
6 files changed, 44 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index bf44c523cac..2700f7b7cd0 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -70,7 +70,8 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
<term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
<listitem>
<para>
- Drops all temporary tables created in the current session.
+ Drops all temporary tables and temporary session variables created in
+ the current session.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c
index 17d172df076..0a33c949fce 100644
--- a/src/backend/commands/discard.c
+++ b/src/backend/commands/discard.c
@@ -19,6 +19,7 @@
#include "commands/discard.h"
#include "commands/prepare.h"
#include "commands/sequence.h"
+#include "commands/session_variable.h"
#include "storage/lock.h"
#include "utils/guc.h"
#include "utils/portal.h"
@@ -47,6 +48,7 @@ DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
case DISCARD_TEMP:
ResetTempTableNamespace();
+ ResetSessionVariables();
break;
default:
@@ -76,4 +78,5 @@ DiscardAll(bool isTopLevel)
ResetPlanCache();
ResetTempTableNamespace();
ResetSequenceCaches();
+ ResetSessionVariables();
}
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index abcb0bb531a..c75f851bad2 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -411,3 +411,23 @@ ExecuteLetStmt(ParseState *pstate,
PopActiveSnapshot();
}
+
+/*
+ * Fast drop of the complete content of the session variables hash table, and
+ * cleanup of any list that wouldn't be relevant anymore.
+ * This is used by the DISCARD TEMP.
+ */
+void
+ResetSessionVariables(void)
+{
+ /* destroy hash table and reset related memory context */
+ if (sessionvars)
+ {
+ hash_destroy(sessionvars);
+ sessionvars = NULL;
+ }
+
+ /* release memory allocated by session variables */
+ if (SVariableMemoryContext != NULL)
+ MemoryContextReset(SVariableMemoryContext);
+}
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index c4b4d9e6832..cc1aa7ce23b 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -37,4 +37,6 @@ extern void get_session_variable_type_typmod_collid(char *varname,
extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo params,
QueryEnvironment *queryEnv, QueryCompletion *qc);
+extern void ResetSessionVariables(void);
+
#endif
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 45c2d27ab44..c36febd894e 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -41,3 +41,10 @@ DROP VARIABLE x;
SET ROLE TO DEFAULT;
DROP ROLE regress_session_variable_test_role_01;
DROP ROLE regress_session_variable_test_role_02;
+CREATE TEMP VARIABLE x AS int;
+-- should fail
+CREATE TEMP VARIABLE x AS int;
+ERROR: session variable "x" already exists
+DISCARD TEMP;
+-- should be ok
+CREATE TEMP VARIABLE x AS int;
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 34f34dd898f..7fd739d6677 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -54,3 +54,13 @@ DROP VARIABLE x;
SET ROLE TO DEFAULT;
DROP ROLE regress_session_variable_test_role_01;
DROP ROLE regress_session_variable_test_role_02;
+
+CREATE TEMP VARIABLE x AS int;
+
+-- should fail
+CREATE TEMP VARIABLE x AS int;
+
+DISCARD TEMP;
+
+-- should be ok
+CREATE TEMP VARIABLE x AS int;
--
2.53.0
[text/x-patch] v20260403-2-0008-support-CREATE-IF-NOT-EXISTS-and-DROP-IF-EXISTS.patch (11.2K, 10-v20260403-2-0008-support-CREATE-IF-NOT-EXISTS-and-DROP-IF-EXISTS.patch)
download | inline diff:
From aff7b8f4cd8e18791fb4140ddb8c93aa18f5b720 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Thu, 4 Dec 2025 06:17:54 +0100
Subject: [PATCH 08/11] support CREATE IF NOT EXISTS and DROP IF EXISTS
---
doc/src/sgml/ref/create_variable.sgml | 12 ++++-
doc/src/sgml/ref/drop_variable.sgml | 12 ++++-
src/backend/commands/session_variable.c | 46 +++++++++++++------
src/backend/parser/gram.y | 31 ++++++++++++-
src/backend/tcop/utility.c | 2 +-
src/include/commands/session_variable.h | 2 +-
src/include/nodes/parsenodes.h | 4 +-
.../expected/session_variables_ddl.out | 6 +++
.../regress/sql/session_variables_ddl.sql | 6 +++
9 files changed, 100 insertions(+), 21 deletions(-)
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
index 1315b1248c7..def368fc237 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
+CREATE { TEMP | TEMPORARY } VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
</synopsis>
</refsynopsisdiv>
<refsect1>
@@ -69,6 +69,16 @@ CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replac
<variablelist>
+ <varlistentry id="sql-createvariable-if-not-exists">
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the name already exists. A notice is issued in
+ this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createvariable-name">
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
index dede42e4ffb..5de6a737493 100644
--- a/doc/src/sgml/ref/drop_variable.sgml
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-DROP VARIABLE <replaceable class="parameter">name</replaceable>
+DROP VARIABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -42,6 +42,16 @@ DROP VARIABLE <replaceable class="parameter">name</replaceable>
<refsect1>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the session variable does not exist. A notice is
+ issued in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index c75f851bad2..861a9317686 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -93,7 +93,7 @@ create_sessionvars_hashtables(void)
* Returns entry of session variable specified by name
*/
static SVariable
-search_variable(char *varname)
+search_variable(char *varname, bool missing_ok)
{
SVariable svar;
@@ -103,7 +103,7 @@ search_variable(char *varname)
svar = (SVariable) hash_search(sessionvars, varname,
HASH_FIND, NULL);
- if (!svar)
+ if (!svar && !missing_ok)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("session variable \"%s\" doesn't exist",
@@ -125,7 +125,7 @@ get_session_variable_type_typmod_collid(char *varname,
{
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
/* only owner can set content of variable */
*typid = svar->vartype;
@@ -145,7 +145,7 @@ GetSessionVariableWithTypecheck(char *varname,
SVariable svar;
Datum result;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
if (svar->vartype != typid || svar->vartypmod != typmod)
ereport(ERROR,
@@ -182,7 +182,7 @@ SetSessionVariableWithTypecheck(char *varname,
{
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
if (svar->vartype != typid || svar->vartypmod != typmod)
ereport(ERROR,
@@ -278,10 +278,21 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
HASH_ENTER, &found);
if (found)
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("session variable \"%s\" already exists",
- stmt->name)));
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists, skipping",
+ stmt->name)));
+ return;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
+ }
namestrcpy(&svar->varname, stmt->name);
svar->vartype = typeid;
@@ -299,7 +310,7 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
* Drop variable by name
*/
void
-DropVariableByName(char *varname)
+DropVariableByName(DropSessionVarStmt *stmt)
{
SVariable svar;
@@ -311,20 +322,27 @@ DropVariableByName(char *varname)
PreventCommandIfParallelMode("DROP VARIABLE");
PreventCommandDuringRecovery("DROP VARIABLE");
- svar = search_variable(varname);
+ svar = search_variable(stmt->name, stmt->missing_ok);
+ if (!svar)
+ {
+ ereport(NOTICE,
+ (errmsg("session variable \"%s\" does not exists, skipping",
+ stmt->name)));
+ return;
+ }
/* only owner can get content of variable */
if (svar->varowner != GetUserId() && !superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be owner of session variable %s",
- varname)));
+ stmt->name)));
if (!svar->typbyval && !svar->isnull)
pfree(DatumGetPointer(svar->value));
if (hash_search(sessionvars,
- varname,
+ stmt->name,
HASH_REMOVE,
NULL) == NULL)
elog(ERROR, "hash table corrupted");
@@ -348,7 +366,7 @@ ExecuteLetStmt(ParseState *pstate,
char *varname = query->resultVariable;
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
/* only owner can set content of variable */
if (svar->varowner != GetUserId() && !superuser())
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 90821c62087..4257f1e0274 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5448,7 +5448,7 @@ create_extension_opt_item:
/*****************************************************************************
*
* QUERY :
- * CREATE { TEMP | TEMPORARY } VARIABLE varname [AS] type
+ * CREATE { TEMP | TEMPORARY } VARIABLE [IF NOT EXISTS ] varname [AS] type
*
*****************************************************************************/
@@ -5465,14 +5465,31 @@ CreateSessionVarStmt:
n->name = $4;
n->typeName = $6;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE OptTemp VARIABLE IF_P NOT EXISTS ColId opt_as Typename
+ {
+ CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
+
+ if ($2 != RELPERSISTENCE_TEMP)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only temporal session variables are supported"),
+ parser_errposition(@2)));
+
+ n->name = $7;
+ n->typeName = $9;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
+
;
/*****************************************************************************
*
* QUERY :
- * DROP VARIABLE varname
+ * DROP VARIABLE [ IF EXISTS ] varname
*
*****************************************************************************/
@@ -5482,8 +5499,18 @@ DropSessionVarStmt:
DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
n->name = $3;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
+ | DROP VARIABLE IF_P EXISTS ColId
+ {
+ DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
+
+ n->name = $5;
+ n->missing_ok = true;
$$ = (Node *) n;
}
+
;
/*****************************************************************************
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index fda4e019b13..5c34a8e95a1 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1076,7 +1076,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_DropSessionVarStmt:
/* No event triggers for catalog less session variables */
- DropVariableByName(((DropSessionVarStmt *) parsetree)->name);
+ DropVariableByName((DropSessionVarStmt *) parsetree);
break;
case T_LetStmt:
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index cc1aa7ce23b..3f07ae55aac 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -22,7 +22,7 @@
#include "tcop/cmdtag.h"
extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
-extern void DropVariableByName(char *varname);
+extern void DropVariableByName(DropSessionVarStmt *stmt);
extern Datum GetSessionVariableWithTypecheck(char *varname, Oid typid, int32 typmod, bool *isnull);
extern void SetSessionVariableWithTypecheck(char *varname,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7d77a368b06..1efd0a22ad5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3701,6 +3701,7 @@ typedef struct CreateSessionVarStmt
NodeTag type;
char *name; /* the variable to create */
TypeName *typeName; /* the type of variable */
+ bool if_not_exists; /* just do nothing if variable already exists? */
} CreateSessionVarStmt;
/* ----------------------
@@ -3710,7 +3711,8 @@ typedef struct CreateSessionVarStmt
typedef struct DropSessionVarStmt
{
NodeTag type;
- char *name;
+ char *name; /* the variable name to drop */
+ bool missing_ok; /* skip error of variable is missing */
} DropSessionVarStmt;
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index c36febd894e..9f5b088de72 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -48,3 +48,9 @@ ERROR: session variable "x" already exists
DISCARD TEMP;
-- should be ok
CREATE TEMP VARIABLE x AS int;
+-- should be ok
+CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
+NOTICE: session variable "x" already exists, skipping
+DROP VARIABLE x;
+DROP VARIABLE IF EXISTS x;
+NOTICE: session variable "x" does not exists, skipping
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 7fd739d6677..60f78671e3b 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -64,3 +64,9 @@ DISCARD TEMP;
-- should be ok
CREATE TEMP VARIABLE x AS int;
+
+-- should be ok
+CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
+
+DROP VARIABLE x;
+DROP VARIABLE IF EXISTS x;
--
2.53.0
[text/x-patch] v20260403-2-0009-use-names-of-currently-used-temp-variables-for-tab-c.patch (6.1K, 11-v20260403-2-0009-use-names-of-currently-used-temp-variables-for-tab-c.patch)
download | inline diff:
From 56630160904ab829f5e1fb241c521dbf59fd21c1 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Thu, 4 Dec 2025 18:49:06 +0100
Subject: [PATCH 09/11] use names of currently used temp variables for tab
complete of DROP VARIABLE, LET and VARIABLE()
---
src/backend/commands/session_variable.c | 37 +++++++++++++++++++
src/bin/psql/tab-complete.in.c | 17 +++++++++
src/include/catalog/pg_proc.dat | 5 +++
.../expected/session_variables_ddl.out | 16 ++++++++
.../regress/sql/session_variables_ddl.sql | 8 ++++
5 files changed, 83 insertions(+)
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 861a9317686..824410a3235 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -19,6 +19,7 @@
#include "commands/session_variable.h"
#include "executor/executor.h"
#include "executor/svariableReceiver.h"
+#include "funcapi.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
#include "rewrite/rewriteHandler.h"
@@ -29,6 +30,7 @@
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/snapmgr.h"
+#include "utils/tuplestore.h"
/*
* The session variables are stored in the backend's private memory (data,
@@ -449,3 +451,38 @@ ResetSessionVariables(void)
if (SVariableMemoryContext != NULL)
MemoryContextReset(SVariableMemoryContext);
}
+
+/*
+ * pg_get_temporary_session_variables_names
+ *
+ * Returns list of temporary session variables. It is used by psql's
+ * tab complete for DROP VARIABLE and LET commands.
+ */
+Datum
+pg_get_temporary_session_variables_names(PG_FUNCTION_ARGS)
+{
+ InitMaterializedSRF(fcinfo, MAT_SRF_USE_EXPECTED_DESC);
+
+ if (sessionvars)
+ {
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HASH_SEQ_STATUS status;
+ SVariable svar;
+
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ Datum values[1];
+ bool nulls[1];
+
+ values[0] = CStringGetTextDatum((NameStr(svar->varname)));
+ nulls[0] = false;
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+ }
+
+ return (Datum) 0;
+}
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 69f348c129e..aafd2136b62 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1208,6 +1208,11 @@ Keywords_for_list_of_owner_roles, "PUBLIC"
" FROM pg_catalog.pg_timezone_names() "\
" WHERE pg_catalog.quote_literal(pg_catalog.lower(name)) LIKE pg_catalog.lower('%s')"
+#define Query_for_list_of_temporary_session_variables \
+"SELECT varname "\
+" FROM pg_catalog.pg_get_temporary_session_variables_names() AS varname "\
+" WHERE varname LIKE '%s'"
+
/* Privilege options shared between GRANT and REVOKE */
#define Privilege_options_of_grant_and_revoke \
"SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", \
@@ -4506,6 +4511,10 @@ match_previous_words(int pattern_id,
else if (Matches("DROP", "TRANSFORM", "FOR", MatchAny, "LANGUAGE", MatchAny))
COMPLETE_WITH("CASCADE", "RESTRICT");
+ /* DROP VARIABLE */
+ else if (Matches("DROP", "VARIABLE"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_temporary_session_variables);
+
/* EXECUTE */
else if (Matches("EXECUTE"))
COMPLETE_WITH_QUERY(Query_for_list_of_prepared_statements);
@@ -4955,6 +4964,8 @@ match_previous_words(int pattern_id,
/* LET */
/* Complete LET <variable> with "=" */
+ else if (Matches("LET"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_temporary_session_variables);
else if (TailMatches("LET", MatchAny))
COMPLETE_WITH("=");
@@ -5566,6 +5577,12 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("'standby_replay'", "'standby_write'", "'standby_flush'", "'primary_flush'");
}
+/*
+ * VARIABLE fence
+ */
+ else if (TailMatches("VARIABLE", "("))
+ COMPLETE_WITH_QUERY(Query_for_list_of_temporary_session_variables);
+
/* WITH [RECURSIVE] */
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index acf16254b21..0b24cde208b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12860,4 +12860,9 @@
proname => 'hashoid8extended', prorettype => 'int8',
proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
+# Session variables support
+{ oid => '8068', descr => 'returns names of temporary session variables',
+ proname => 'pg_get_temporary_session_variables_names', prorows => '1000', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'text', proargtypes => '',
+ prosrc => 'pg_get_temporary_session_variables_names' },
]
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 9f5b088de72..758ce582fca 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -54,3 +54,19 @@ NOTICE: session variable "x" already exists, skipping
DROP VARIABLE x;
DROP VARIABLE IF EXISTS x;
NOTICE: session variable "x" does not exists, skipping
+CREATE TEMP VARIABLE x AS int;
+CREATE TEMP VARIABLE y AS int;
+SELECT * FROM pg_get_temporary_session_variables_names();
+ pg_get_temporary_session_variables_names
+------------------------------------------
+ y
+ x
+(2 rows)
+
+DROP VARIABLE x;
+DROP VARIABLE y;
+SELECT * FROM pg_get_temporary_session_variables_names();
+ pg_get_temporary_session_variables_names
+------------------------------------------
+(0 rows)
+
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 60f78671e3b..c4cbfd17169 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -70,3 +70,11 @@ CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
DROP VARIABLE x;
DROP VARIABLE IF EXISTS x;
+
+CREATE TEMP VARIABLE x AS int;
+CREATE TEMP VARIABLE y AS int;
+SELECT * FROM pg_get_temporary_session_variables_names();
+
+DROP VARIABLE x;
+DROP VARIABLE y;
+SELECT * FROM pg_get_temporary_session_variables_names();
--
2.53.0
[text/x-patch] v20260403-2-0010-transactional-DDL-CREATE-VARIABLE-DROP-VARIABLE.patch (11.4K, 12-v20260403-2-0010-transactional-DDL-CREATE-VARIABLE-DROP-VARIABLE.patch)
download | inline diff:
From 881825490a83a20cb884dbf81490ddb87bb33102 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sat, 6 Dec 2025 07:35:30 +0100
Subject: [PATCH 10/11] transactional DDL - CREATE VARIABLE, DROP VARIABLE
Generally PostgreSQL DDL statements are transactional. We can implement transactional behave
for ( CREATE | DROP ) VARIABLE too. Implementation is almost simple. DROP just set dropped_lxid,
CREATE set created_lxid and moves previous entry of related sessionvars hashtab to stack. At the
end of transaction for commit we just clean this stack. For rollback we do copy the bootom value
from the stack and again clean stack.
---
src/backend/access/transam/xact.c | 5 +
src/backend/commands/session_variable.c | 217 ++++++++++++++++--
src/include/commands/session_variable.h | 2 +
.../expected/session_variables_ddl.out | 38 +++
.../regress/sql/session_variables_ddl.sql | 21 ++
5 files changed, 258 insertions(+), 25 deletions(-)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index aafc53e0164..ccee226cafe 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -39,6 +39,7 @@
#include "commands/async.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
+#include "commands/session_variable.h"
#include "common/pg_prng.h"
#include "executor/spi.h"
#include "libpq/be-fsstubs.h"
@@ -2334,6 +2335,9 @@ CommitTransaction(void)
/* close large objects before lower-level cleanup */
AtEOXact_LargeObject(true);
+ /* remove stacked session variables */
+ AtPreEOXact_SessionVariables(true);
+
/*
* Insert notifications sent by NOTIFY commands into the queue. This
* should be late in the pre-commit sequence to minimize time spent
@@ -2940,6 +2944,7 @@ AbortTransaction(void)
AtAbort_Portals();
smgrDoPendingSyncs(false, is_parallel_worker);
AtEOXact_LargeObject(false);
+ AtPreEOXact_SessionVariables(false);
AtAbort_Notify();
AtEOXact_RelationMap(false, is_parallel_worker);
AtAbort_Twophase();
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 824410a3235..c36af7bc460 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -56,6 +56,11 @@ typedef struct SVariableData
int16 typlen;
bool typbyval;
+
+ struct SVariableData *prev;
+ bool stacked;
+ LocalTransactionId created_lxid;
+ LocalTransactionId dropped_lxid;
} SVariableData;
typedef SVariableData *SVariable;
@@ -64,6 +69,14 @@ static HTAB *sessionvars = NULL; /* hash table for session variables */
static MemoryContext SVariableMemoryContext = NULL;
+/*
+ * When we to remove committed dropped variables or uncommitted
+ * created variables from sessionvars tab. created_or_dropped_lxid
+ * is transaction id of transaction when some of DROP or CREATE variable
+ * was executed.
+ */
+static LocalTransactionId created_or_dropped_lxid = InvalidLocalTransactionId;
+
/*
* Create the hash table for storing session variables.
*/
@@ -105,6 +118,14 @@ search_variable(char *varname, bool missing_ok)
svar = (SVariable) hash_search(sessionvars, varname,
HASH_FIND, NULL);
+ /* Session variable can be dropped inside current transaction */
+ if (svar && svar->dropped_lxid != InvalidLocalTransactionId)
+ {
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(svar->dropped_lxid == MyProc->vxid.lxid);
+ svar = NULL;
+ }
+
if (!svar && !missing_ok)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
@@ -237,6 +258,7 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
Oid typcollation;
Oid varowner = GetUserId();
SVariable svar;
+ SVariable prev_svar = NULL;
bool found;
int16 typlen;
bool typbyval;
@@ -281,19 +303,37 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
if (found)
{
- if (stmt->if_not_exists)
+ if (svar->dropped_lxid == InvalidLocalTransactionId)
{
- ereport(NOTICE,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("session variable \"%s\" already exists, skipping",
- stmt->name)));
- return;
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists, skipping",
+ stmt->name)));
+ return;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
}
else
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("session variable \"%s\" already exists",
- stmt->name)));
+ {
+ MemoryContext oldcxt;
+
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(svar->dropped_lxid == MyProc->vxid.lxid);
+
+ oldcxt = MemoryContextSwitchTo(SVariableMemoryContext);
+ prev_svar = palloc_object(SVariableData);
+ memcpy(prev_svar, svar, sizeof(SVariableData));
+ prev_svar->stacked = true;
+ memset(svar, 0, sizeof(SVariableData));
+
+ MemoryContextSwitchTo(oldcxt);
+ }
}
namestrcpy(&svar->varname, stmt->name);
@@ -306,6 +346,12 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
svar->value = (Datum) 0;
svar->isnull = true;
+
+ svar->prev = prev_svar;
+ svar->stacked = false;
+ svar->dropped_lxid = InvalidLocalTransactionId;
+ svar->created_lxid = MyProc->vxid.lxid;
+ created_or_dropped_lxid = MyProc->vxid.lxid;
}
/*
@@ -340,14 +386,129 @@ DropVariableByName(DropSessionVarStmt *stmt)
errmsg("must be owner of session variable %s",
stmt->name)));
- if (!svar->typbyval && !svar->isnull)
+ svar->dropped_lxid = MyProc->vxid.lxid;
+ created_or_dropped_lxid = MyProc->vxid.lxid;
+}
+
+static void
+free_svar_value(SVariable svar)
+{
+ if (!svar->isnull && !svar->typbyval)
pfree(DatumGetPointer(svar->value));
+}
+
+static void
+free_stacked_svars(SVariable svar)
+{
+ while (svar)
+ {
+ SVariable current = svar;
+
+ free_svar_value(current);
+ svar = current->prev;
+ pfree(current);
+ }
+}
+
+/*
+ * remove dropped committed entries or created uncommitted entries
+ * from hash table.
+ */
+void
+AtPreEOXact_SessionVariables(bool isCommit)
+{
+ if (created_or_dropped_lxid != InvalidLocalTransactionId)
+ {
+ HASH_SEQ_STATUS status;
+ SVariable svar;
- if (hash_search(sessionvars,
- stmt->name,
- HASH_REMOVE,
- NULL) == NULL)
- elog(ERROR, "hash table corrupted");
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(sessionvars);
+
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ if ((svar->dropped_lxid != InvalidLocalTransactionId) ||
+ (svar->created_lxid != InvalidLocalTransactionId))
+ {
+ Assert((svar->dropped_lxid == InvalidLocalTransactionId) ||
+ (svar->dropped_lxid == MyProc->vxid.lxid));
+
+ Assert((svar->created_lxid == InvalidLocalTransactionId) ||
+ (svar->created_lxid == MyProc->vxid.lxid));
+
+ if (isCommit)
+ {
+ if (svar->dropped_lxid == MyProc->vxid.lxid)
+ {
+ free_stacked_svars(svar->prev);
+ free_svar_value(svar);
+
+ (void) hash_search(sessionvars,
+ NameStr(svar->varname),
+ HASH_REMOVE,
+ NULL);
+ svar = NULL;
+ }
+ else
+ {
+ free_stacked_svars(svar->prev);
+ svar->prev = NULL;
+ svar->created_lxid = InvalidLocalTransactionId;
+ }
+ }
+ else
+ {
+ SVariable iter;
+
+ /*
+ * We have to search value the oldest svar in the stack. If it is just dropped,
+ * then we revert dropped flag. If it is created in current transaction, then
+ * we remove this svar too.
+ */
+ iter = svar;
+ while (iter->prev)
+ {
+ SVariable current = iter;
+
+ free_svar_value(current);
+
+ iter = current->prev;
+
+ if (current->stacked)
+ pfree(current);
+ }
+
+ if (iter->created_lxid == MyProc->vxid.lxid)
+ {
+ free_svar_value(iter);
+ if (iter->stacked)
+ pfree(iter);
+
+ (void) hash_search(sessionvars,
+ NameStr(svar->varname),
+ HASH_REMOVE,
+ NULL);
+ }
+ else
+ {
+ if (iter->stacked)
+ {
+ memcpy(svar, iter, sizeof(SVariableData));
+ svar->stacked = false;
+ pfree(iter);
+ }
+
+ /* revert dropped flag */
+ svar->dropped_lxid = InvalidLocalTransactionId;
+ }
+ }
+ }
+ }
+
+ created_or_dropped_lxid = InvalidLocalTransactionId;
+ }
}
/*
@@ -433,23 +594,29 @@ ExecuteLetStmt(ParseState *pstate,
}
/*
- * Fast drop of the complete content of the session variables hash table, and
- * cleanup of any list that wouldn't be relevant anymore.
* This is used by the DISCARD TEMP.
*/
void
ResetSessionVariables(void)
{
- /* destroy hash table and reset related memory context */
+ /* mark all session variables as dropped */
if (sessionvars)
{
- hash_destroy(sessionvars);
- sessionvars = NULL;
- }
+ HASH_SEQ_STATUS status;
+ SVariable svar;
+ bool found = false;
- /* release memory allocated by session variables */
- if (SVariableMemoryContext != NULL)
- MemoryContextReset(SVariableMemoryContext);
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ svar->dropped_lxid = MyProc->vxid.lxid;
+ found = true;
+ }
+
+ if (found)
+ created_or_dropped_lxid = MyProc->vxid.lxid;
+ }
}
/*
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 3f07ae55aac..1218c566767 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -39,4 +39,6 @@ extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo para
extern void ResetSessionVariables(void);
+extern void AtPreEOXact_SessionVariables(bool isCommit);
+
#endif
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 758ce582fca..2d00471da96 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -70,3 +70,41 @@ SELECT * FROM pg_get_temporary_session_variables_names();
------------------------------------------
(0 rows)
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+COMMIT;
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+ROLLBACK;
+SELECT VARIABLE(x);
+ x
+----
+ Hi
+(1 row)
+
+DROP VARIABLE x;
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index c4cbfd17169..7335f15ed39 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -78,3 +78,24 @@ SELECT * FROM pg_get_temporary_session_variables_names();
DROP VARIABLE x;
DROP VARIABLE y;
SELECT * FROM pg_get_temporary_session_variables_names();
+
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+COMMIT;
+SELECT VARIABLE(x);
+
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ROLLBACK;
+SELECT VARIABLE(x);
+
+DROP VARIABLE x;
--
2.53.0
[text/x-patch] v20260403-2-0011-subtransaction-support-for-session-variables-DDL-CRE.patch (8.0K, 13-v20260403-2-0011-subtransaction-support-for-session-variables-DDL-CRE.patch)
download | inline diff:
From a6340f59478f40cce91a1153da03bf7cfb45aea9 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 8 Dec 2025 05:00:12 +0100
Subject: [PATCH 11/11] subtransaction support for session variables DDL
(CREATE, DROP)
If we support transactional DDL for CREATE, DROP session variables, we should to
support subtransactions too. Implementation is simple. Any value has two new flags:
created_subid and dropped_subid. At the subtransaction end for rollback we
clean entries from the stack related to subtransactions. When commit we update
created_subid and dropped_subid for parent subtransaction.
---
src/backend/access/transam/xact.c | 4 +
src/backend/commands/session_variable.c | 109 ++++++++++++++++++
src/include/commands/session_variable.h | 3 +
.../expected/session_variables_ddl.out | 21 ++++
.../regress/sql/session_variables_ddl.sql | 12 ++
5 files changed, 149 insertions(+)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index ccee226cafe..76dc2a843cf 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -5213,6 +5213,8 @@ CommitSubTransaction(void)
AtEOSubXact_SPI(true, s->subTransactionId);
AtEOSubXact_on_commit_actions(true, s->subTransactionId,
s->parent->subTransactionId);
+ AtEOSubXact_SessionVariables(true, s->subTransactionId,
+ s->parent->subTransactionId);
AtEOSubXact_Namespace(true, s->subTransactionId,
s->parent->subTransactionId);
AtEOSubXact_Files(true, s->subTransactionId,
@@ -5382,6 +5384,8 @@ AbortSubTransaction(void)
AtEOSubXact_SPI(false, s->subTransactionId);
AtEOSubXact_on_commit_actions(false, s->subTransactionId,
s->parent->subTransactionId);
+ AtEOSubXact_SessionVariables(false, s->subTransactionId,
+ s->parent->subTransactionId);
AtEOSubXact_Namespace(false, s->subTransactionId,
s->parent->subTransactionId);
AtEOSubXact_Files(false, s->subTransactionId,
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index c36af7bc460..c80ab48ec47 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "access/xact.h"
#include "catalog/pg_language.h"
#include "catalog/pg_type.h"
#include "commands/session_variable.h"
@@ -61,6 +62,8 @@ typedef struct SVariableData
bool stacked;
LocalTransactionId created_lxid;
LocalTransactionId dropped_lxid;
+ SubTransactionId created_subid;
+ SubTransactionId dropped_subid;
} SVariableData;
typedef SVariableData *SVariable;
@@ -351,6 +354,8 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
svar->stacked = false;
svar->dropped_lxid = InvalidLocalTransactionId;
svar->created_lxid = MyProc->vxid.lxid;
+ svar->dropped_subid = InvalidSubTransactionId;
+ svar->created_subid = GetCurrentSubTransactionId();
created_or_dropped_lxid = MyProc->vxid.lxid;
}
@@ -387,6 +392,7 @@ DropVariableByName(DropSessionVarStmt *stmt)
stmt->name)));
svar->dropped_lxid = MyProc->vxid.lxid;
+ svar->dropped_subid = GetCurrentSubTransactionId();
created_or_dropped_lxid = MyProc->vxid.lxid;
}
@@ -456,6 +462,7 @@ AtPreEOXact_SessionVariables(bool isCommit)
free_stacked_svars(svar->prev);
svar->prev = NULL;
svar->created_lxid = InvalidLocalTransactionId;
+ svar->created_subid = InvalidSubTransactionId;
}
}
else
@@ -502,6 +509,7 @@ AtPreEOXact_SessionVariables(bool isCommit)
/* revert dropped flag */
svar->dropped_lxid = InvalidLocalTransactionId;
+ svar->dropped_subid = InvalidSubTransactionId;
}
}
}
@@ -511,6 +519,107 @@ AtPreEOXact_SessionVariables(bool isCommit)
}
}
+/*
+ * Post-subcommit or post-subabort cleanup
+ *
+ * During subabort, we can immediately remove entries created during this
+ * subtransaction. During subcommit, just transfer entries marked during
+ * this subtransaction as being the parent's responsibility.
+ */
+void
+AtEOSubXact_SessionVariables(bool isCommit,
+ SubTransactionId mySubid,
+ SubTransactionId parentSubid)
+{
+ if (created_or_dropped_lxid != InvalidLocalTransactionId)
+ {
+ HASH_SEQ_STATUS status;
+ SVariable svar;
+
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(sessionvars);
+
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ if ((svar->dropped_lxid != InvalidLocalTransactionId) ||
+ (svar->created_lxid != InvalidLocalTransactionId))
+ {
+ if (!isCommit)
+ {
+ SVariable iterator = svar;
+ SVariable last = NULL;
+ SVariable first = NULL;
+
+ /* remove entries or flags by current subtransactions */
+ while (iterator)
+ {
+ SVariable current = iterator;
+
+ iterator = current->prev;
+
+ if (current->dropped_subid == mySubid)
+ {
+ current->dropped_lxid = InvalidLocalTransactionId;
+ current->dropped_subid = InvalidSubTransactionId;
+ }
+
+ if (current->created_subid == mySubid)
+ {
+ free_svar_value(current);
+ if (current->stacked)
+ pfree(current);
+ }
+ else
+ {
+ /* remember first not deleted svar */
+ if (first == NULL)
+ first = current;
+
+ if (last)
+ last->prev = current;
+
+ last = current;
+ }
+ }
+
+ /* Some svars was removed - set hashtab entry or remove it */
+ if (!first)
+ {
+ /* we have to remove entry from hash table */
+ (void) hash_search(sessionvars,
+ NameStr(svar->varname),
+ HASH_REMOVE,
+ NULL);
+ }
+ else if (first->stacked)
+ {
+ memcpy(svar, first, sizeof(SVariableData));
+ svar->stacked = false;
+ pfree(first);
+ }
+ }
+ else
+ {
+ SVariable iterator = svar;
+
+ /* transfer responsibility to parent */
+ while (iterator)
+ {
+ if (iterator->dropped_subid == mySubid)
+ iterator->dropped_subid = parentSubid;
+ if (iterator->created_subid == mySubid)
+ iterator->created_subid = parentSubid;
+
+ iterator = iterator->prev;
+ }
+ }
+ }
+ }
+ }
+}
+
/*
* Assign the result of the evaluated expression to the session variable
*/
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 1218c566767..45ccbe2f046 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -40,5 +40,8 @@ extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo para
extern void ResetSessionVariables(void);
extern void AtPreEOXact_SessionVariables(bool isCommit);
+extern void AtEOSubXact_SessionVariables(bool isCommit,
+ SubTransactionId mySubid,
+ SubTransactionId parentSubid);
#endif
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 2d00471da96..9031b5c384c 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -107,4 +107,25 @@ SELECT VARIABLE(x);
Hi
(1 row)
+BEGIN;
+SAVEPOINT s1;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+ROLLBACK TO s1;
+SELECT VARIABLE(x);
+ x
+----
+ Hi
+(1 row)
+
+COMMIT;
DROP VARIABLE x;
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 7335f15ed39..5ec412ad7c9 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -98,4 +98,16 @@ SELECT VARIABLE(x);
ROLLBACK;
SELECT VARIABLE(x);
+BEGIN;
+SAVEPOINT s1;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ROLLBACK TO s1;
+SELECT VARIABLE(x);
+COMMIT;
+
DROP VARIABLE x;
--
2.53.0
view thread (439+ messages)
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]
Subject: Re: proposal: schema variables
In-Reply-To: <CAFj8pRBH629MNqris2U4wyocNd0na5wv6czeUHRoTKYEH90-yA@mail.gmail.com>
* 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