public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Stehule <[email protected]>
To: Jim Jones <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Dmitry Dolgov <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: DUVAL REMI <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: jian he <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: PegoraroF10 <[email protected]>
Subject: Re: proposal: schema variables
Date: Wed, 3 Dec 2025 05:27:04 +0100
Message-ID: <CAFj8pRDisL7v_t9-QYqxYOLvdQJCbZBY8hjzXNHMWxqEoVun4g@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRBHjSEE3A500MLyOPQogqaYGu=XVKvq4LnSnuHsFSxeJg@mail.gmail.com>
References: <CAFj8pRDJ9cq00VYSHxs6LsoHNWjhYXyWWBtV6UgeWwhs0AHa9A@mail.gmail.com>
<CAFj8pRCRDhQobRx5FAZgK8drzA_T-9c67U_-wZJ2fmqwEVpFSQ@mail.gmail.com>
<CAFj8pRB1VKs8DbymheidYXDC2i1ZShV+40J7h2j8GDfqM+yLbQ@mail.gmail.com>
<CAFj8pRAMaiRSRyye9LdOTh_TgbwmohuXh7cCS9eSLtOUzCJU5Q@mail.gmail.com>
<CAFj8pRBNj_+Q2CQjpURm5copeY27YPJz=Oa+rpRfa5DHu-j1iQ@mail.gmail.com>
<CAFj8pRDiKxpZA1+dkGK-YW_H8oYp2hVOuFpOPHEkCBc5O4Z_ug@mail.gmail.com>
<CAFj8pRAdUF6etyOK3UsAPu-1nuOpv6aHbFJuhtJO73uxC6vObw@mail.gmail.com>
<CAFj8pRDdhYR66dNVz9WKKHDL64QK+CuqYM3yfiMR_Dp-uOm0bg@mail.gmail.com>
<CAFj8pRDJdF=Jv6M1_ePV8tnzxBfyYHfHodeifhOUyMt1N-fxRQ@mail.gmail.com>
<CAFj8pRAwp0ES9BKXbNocG_pYcqP0Cs2GdtGx_-2XsEe3fkCgdg@mail.gmail.com>
<CAFj8pRAhnp63fV5m7ATr2_nW9_yVbFzwhL4Be8oTY_M0jksrXg@mail.gmail.com>
<CAFj8pRBykx1Oh1JVzY5HfcZPjbGE5PH=DyPN4sM7fBafnNkLkA@mail.gmail.com>
<CAFj8pRBx4w3QS0D2W3yc8hWXH7hynsOwO4x+iv4AstmB6Dmkgw@mail.gmail.com>
<CAFj8pRBeF5Tz_DiKWokeEcRxrJR5FFDbTKhaaB-9TMYU3RYDfw@mail.gmail.com>
<[email protected]>
<CAFj8pRBDVrm_Ju9RJe1J1Yhb5BPtOq49cQH7RvQ_ZPuh__Nrcw@mail.gmail.com>
<CAFj8pRAe1BN1iLoVaD4CLtMY4pg2XROVqmU8HngqBaFRhF4zCg@mail.gmail.com>
<CAFj8pRBryCY4LqtrPu9E_mtZYkNfZb64-MetWE0232whdOLgjA@mail.gmail.com>
<CAFj8pRCPjHT8xfnMHRroc_wx4wBT4ocqzjH--jqo0xHqec4qhA@mail.gmail.com>
<CAFj8pRBTmSPUPET-Jo=REsLLx15kmHqYXmXwL2BKLeZjZqUUEg@mail.gmail.com>
<CAFj8pRAOTQ5BkKvCFHkUE=HWzirZhVzxE5iugbcOsz=z5R0ogA@mail.gmail.com>
<CAFj8pRDmCF05tVDbzkwL45WHLhdZnbkQLn9HA3RA-5LGu=wEXg@mail.gmail.com>
<CAFj8pRCACo9_fwfe3ixgrRfThon+Nu26b1phd-ge7v5XCx4=9A@mail.gmail.com>
<CAFj8pRDNBHEf2nrU3Xx3S-Vmtj+YUiGxc0CV78u7uwbxXP_acw@mail.gmail.com>
<CAFj8pRAnWR5TV=kRu9L-xUUkxoeoWUXDKS_Qixfqj4YptjGUsA@mail.gmail.com>
<CAFj8pRBHjSEE3A500MLyOPQogqaYGu=XVKvq4LnSnuHsFSxeJg@mail.gmail.com>
Hi
fresh rebase after a87987cafca683e9076c424f99bae117211a83a4
regards
Pavel
Attachments:
[text/x-patch] v20251203-0007-DISCARD-TEMP.patch (4.3K, 3-v20251203-0007-DISCARD-TEMP.patch)
download | inline diff:
From 267400ed531dd199a8c25b88ab008cbdb6af7c0b Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 24 Nov 2025 20:04:16 +0100
Subject: [PATCH 7/7] 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 81339a75a52..de322c35c51 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 "utils/guc.h"
#include "utils/portal.h"
@@ -46,6 +47,7 @@ DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
case DISCARD_TEMP:
ResetTempTableNamespace();
+ ResetSessionVariables();
break;
default:
@@ -75,4 +77,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 36490387da0..7837e21cf1c 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -412,3 +412,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 cc6f59e8be5..f12d1f1a6ae 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 92e97ea2ab0..c69fd4bfa47 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.52.0
[text/x-patch] v20251203-0006-LET-command-assign-a-result-of-expression-to-the-ses.patch (39.3K, 4-v20251203-0006-LET-command-assign-a-result-of-expression-to-the-ses.patch)
download | inline diff:
From 1d95b38b74959899e6b22831b4af905357f04de0 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 24 Nov 2025 18:05:03 +0100
Subject: [PATCH 6/7] 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 | 27 ++++
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 | 86 ++++++++++++
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 | 7 +
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 | 123 ++++++++++++++++++
.../regress/sql/session_variables_dml.sql | 87 +++++++++++++
src/tools/pgindent/typedefs.list | 1 +
25 files changed, 624 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 150b04f1d4b..92163d0a74a 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5683,10 +5683,37 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
session variable identifier, and can be used only for session variable
identifier. The special syntax for accessing session variables removes
risk of collisions between variable identifiers and column names.
+ </para>
+
+ <para>
+ The value of a session variable is set with the SQL statement
+ <command>LET</command>. The value of a session variable can be retrieved
+ with the SQL statement <command>SELECT</command>.
<programlisting>
+CREATE VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT VARIABLE(var1);
+</programlisting>
+
+ or
+
+<programlisting>
+CREATE VARIABLE public.current_user_id AS integer;
+GRANT SELECT ON VARIABLE public.current_user_id TO PUBLIC;
+LET current_user_id = (SELECT id FROM users WHERE usename = session_user);
SELECT VARIABLE(current_user_id);
</programlisting>
</para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a variable's value returns a <literal>NULL</literal>, unless its value has
+ been set to something else in the current session using the
+ <command>LET</command> command. Session variables are not transactional:
+ any changes made to the value of a session variable in a transaction won't
+ be undone if the transaction is rolled back (just like 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 a7349919658..cd3faa667f0 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -157,6 +157,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 5ed74c5c173..11b1ef74212 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -107,6 +107,8 @@ CREATE { TEMP | TEMPORAL } VARIABLE <replaceable class="parameter">name</replace
Create an date session variable <literal>var1</literal>:
<programlisting>
CREATE TEMPORARY VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT VARIABLE(var1);
</programlisting>
</para>
@@ -126,6 +128,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..5909d203e94
--- /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 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 c03e7692c7a..6fcd7a81321 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -185,6 +185,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 95d70911bcc..36490387da0 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -19,14 +19,19 @@
#include "catalog/pg_proc.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/acl.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/snapmgr.h"
#include "utils/syscache.h"
/*
@@ -326,3 +331,84 @@ DropVariableByName(char *varname)
HASH_REMOVE,
NULL);
}
+
+/*
+ * 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);
+
+ FreeQueryDesc(queryDesc);
+
+ PopActiveSnapshot();
+}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c1eb81f79e5..fdb13ada75d 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4374,6 +4374,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 c00331be6b8..6dc63954a61 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->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 885106922d0..cd6aeff3040 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -53,15 +53,18 @@
#include "utils/builtins.h"
#include "utils/guc.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 */
@@ -85,7 +88,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);
@@ -95,6 +98,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
@@ -342,6 +347,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:
@@ -421,6 +427,11 @@ transformStmt(ParseState *pstate, Node *parseTree)
(CallStmt *) parseTree);
break;
+ case T_LetStmt:
+ result = transformLetStmt(pstate,
+ (LetStmt *) parseTree);
+ break;
+
default:
/*
@@ -482,6 +493,7 @@ stmt_requires_parse_analysis(RawStmt *parseTree)
case T_ExplainStmt:
case T_CreateTableAsStmt:
case T_CallStmt:
+ case T_LetStmt:
result = true;
break;
@@ -547,6 +559,7 @@ query_requires_rewrite_plan(Query *query)
case T_ExplainStmt:
case T_CreateTableAsStmt:
case T_CallStmt:
+ case T_LetStmt:
result = true;
break;
default:
@@ -1390,7 +1403,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.
@@ -1443,8 +1456,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);
@@ -2842,9 +2855,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
@@ -2867,18 +2882,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;
@@ -2913,7 +2931,7 @@ transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
tle->expr = (Expr *)
transformAssignmentIndirection(pstate,
target,
- stmt->name,
+ target_name,
false,
targettype,
targettypmod,
@@ -2921,10 +2939,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)))
{
@@ -2947,7 +2965,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 */
@@ -2956,7 +2974,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."),
@@ -3324,6 +3342,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 837330a807f..327ecf88712 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -301,7 +301,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 ReturnStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
@@ -747,7 +747,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
@@ -1095,6 +1095,7 @@ stmt:
| ImportForeignSchemaStmt
| IndexStmt
| InsertStmt
+ | LetStmt
| ListenStmt
| RefreshMatViewStmt
| LoadStmt
@@ -12932,6 +12933,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:
@@ -18037,6 +18069,7 @@ unreserved_keyword:
| LARGE_P
| LAST_P
| LEAKPROOF
+ | LET
| LEVEL
| LISTEN
| LOAD
@@ -18653,6 +18686,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 b8340557b34..a9bd4e68154 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -584,6 +584,10 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
errkind = true;
break;
+ case EXPR_KIND_LET_TARGET:
+ errkind = true;
+ break;
+
/*
* There is intentionally no default: case here, so that the
* compiler will warn if we add a new ParseExprKind without
@@ -1023,6 +1027,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_CYCLE_MARK:
errkind = true;
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 aa2483c96a6..bc6aab75cca 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -591,6 +591,9 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_PARTITION_BOUND:
err = _("cannot use column reference in partition bound 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
@@ -961,6 +964,7 @@ expr_kind_allows_session_variables(ParseExprKind p_expr_kind)
case EXPR_KIND_RETURNING:
case EXPR_KIND_VALUES:
case EXPR_KIND_VALUES_SINGLE:
+ case EXPR_KIND_LET_TARGET:
result = true;
break;
@@ -1980,6 +1984,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_GENERATED_COLUMN:
err = _("cannot use subquery in column generation 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
@@ -3339,6 +3346,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "GENERATED AS";
case EXPR_KIND_CYCLE_MARK:
return "CYCLE";
+ 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 778d69c6f3c..13616c9b3c2 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2783,6 +2783,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_CYCLE_MARK:
errkind = true;
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 7dc9c0a6c80..3f1c8cb5e89 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -237,6 +237,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_CallStmt:
case T_DoStmt:
+ case T_LetStmt:
{
/*
* Commands inside the DO block or the called procedure might
@@ -1075,6 +1076,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,
@@ -2219,6 +2225,10 @@ UtilityContainsQuery(Node *parsetree)
return UtilityContainsQuery(qry->utilityStmt);
return qry;
+ case T_LetStmt:
+ qry = castNode(Query, ((LetStmt *) parsetree)->query);
+ return qry;
+
default:
return NULL;
}
@@ -2414,6 +2424,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:
{
@@ -3304,6 +3318,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 d257b4fc37a..4da36144145 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1265,8 +1265,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",
"RESET", "REVOKE", "ROLLBACK",
"SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START",
@@ -4798,6 +4798,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 487464b14b1..3decc639d81 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -147,6 +147,9 @@ typedef struct Query
*/
int resultRelation pg_node_attr(query_jumble_ignore);
+ /* 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 */
@@ -2170,6 +2173,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 97a00df8d1f..4aa0a07f44a 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -192,6 +192,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 8b1f2bd3b68..2181927682c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -258,6 +258,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 84e886940d8..026743b7337 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -82,6 +82,7 @@ typedef enum ParseExprKind
EXPR_KIND_COPY_WHERE, /* WHERE condition in COPY FROM */
EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */
EXPR_KIND_CYCLE_MARK, /* cycle mark value */
+ EXPR_KIND_LET_TARGET, /* only session variables */
} ParseExprKind;
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 7e59d0a5275..fd212257167 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -185,6 +185,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 01efa71c8f0..79a062f6efa 100644
--- a/src/test/regress/expected/session_variables_dml.out
+++ b/src/test/regress/expected/session_variables_dml.out
@@ -133,3 +133,126 @@ 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;
diff --git a/src/test/regress/sql/session_variables_dml.sql b/src/test/regress/sql/session_variables_dml.sql
index bf56b19467b..b8408c97cad 100644
--- a/src/test/regress/sql/session_variables_dml.sql
+++ b/src/test/regress/sql/session_variables_dml.sql
@@ -118,3 +118,90 @@ 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;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 088429dd424..7fbc5c8834d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1550,6 +1550,7 @@ LargeObjectDesc
Latch
LauncherLastStartTimesEntry
LerpFunc
+LetStmt
LexDescr
LexemeEntry
LexemeHashKey
--
2.52.0
[text/x-patch] v20251203-0004-fill-an-auxiliary-buffer-with-values-of-session-vari.patch (14.4K, 5-v20251203-0004-fill-an-auxiliary-buffer-with-values-of-session-vari.patch)
download | inline diff:
From 16d674aa9ea39b8e6994810b766b5d5cea7dfeb0 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sat, 22 Nov 2025 06:40:46 +0100
Subject: [PATCH 4/7] 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 b05ff476a63..9a86896221e 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1069,6 +1069,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 27c9eec697b..198be0cf143 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -44,6 +44,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"
@@ -196,6 +197,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 = (SessionVariableValue *)
+ palloc(nSessionVariables * sizeof(SessionVariableValue));
+
+ /* 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 64ff6996431..d0642eae418 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -645,6 +645,16 @@ typedef struct AsyncRequest
* tuples) */
} AsyncRequest;
+/* ----------------
+ * SessionVariableValue
+ * ----------------
+ */
+typedef struct SessionVariableValue
+{
+ bool isnull;
+ Datum value;
+} SessionVariableValue;
+
/* ----------------
* EState information
*
@@ -704,6 +714,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..01efa71c8f0
--- /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 persistent 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 = VARIABLES(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 = VARIABLES(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 9da7c6ab928..55367a13d69 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 7bf4750c5b6..2f562a217bf 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2732,6 +2732,7 @@ SerializedTransactionState
Session
SessionBackupState
SessionEndType
+SessionVariableValue
SetConstraintState
SetConstraintStateData
SetConstraintTriggerData
--
2.52.0
[text/x-patch] v20251203-0003-collect-session-variables-used-in-plan-and-assign-pa.patch (15.8K, 6-v20251203-0003-collect-session-variables-used-in-plan-and-assign-pa.patch)
download | inline diff:
From cccf66a3cb8fff58da013ae00c14d2c2cf36149a Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Fri, 21 Nov 2025 20:42:56 +0100
Subject: [PATCH 3/7] 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 8e70a85a3f7..52442c746a5 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1902,6 +1902,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 persistent 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 861c2a55f4e..0daa46fdde3 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -132,6 +132,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 0e78628bf01..c00331be6b8 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -374,6 +374,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
@@ -617,6 +618,9 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
result->paramExecTypes = glob->paramExecTypes;
/* utilityStmt should be null, but we might as well copy it */
result->utilityStmt = parse->utilityStmt;
+
+ result->sessionVariables = glob->sessionVariables;
+
result->stmt_location = parse->stmt_location;
result->stmt_len = parse->stmt_len;
@@ -805,6 +809,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 ccdc9bc264a..48de8bf4f14 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);
/*****************************************************************************
@@ -1341,6 +1343,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
@@ -2141,6 +2187,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)
@@ -2159,6 +2209,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);
}
@@ -2220,7 +2307,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
@@ -2242,7 +2331,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 7581695647d..282bb7d5e25 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1649,6 +1649,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 bda4c4eb292..c6aa09a8d39 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -25,6 +25,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"
@@ -947,6 +948,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))
{
@@ -2405,6 +2413,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 *
@@ -2531,6 +2540,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
@@ -4903,7 +4935,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 0fe63c6bb83..d26e744e296 100644
--- a/src/backend/utils/fmgr/fmgr.c
+++ b/src/backend/utils/fmgr/fmgr.c
@@ -1991,9 +1991,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 46a8655621d..97a00df8d1f 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -189,6 +189,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 */
@@ -547,6 +550,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 c4393a94321..61754ae4efc 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -158,6 +158,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 00addf15992..fb81ceb375f 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.52.0
[text/x-patch] v20251203-0005-svariableReceiver.patch (10.8K, 7-v20251203-0005-svariableReceiver.patch)
download | inline diff:
From 00abe49e29eefbff77ed1afe0ee9348325381cc5 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sun, 1 Jun 2025 21:20:16 +0200
Subject: [PATCH 5/7] 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 | 149 +++++++++++++++++++++++
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, 235 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 0daa46fdde3..95d70911bcc 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -171,6 +171,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 2cea41f8771..491092fcc4c 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..b2709e9211b
--- /dev/null
+++ b/src/backend/executor/svariableReceiver.c
@@ -0,0 +1,149 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 "miscadmin.h"
+
+#include "access/detoast.h"
+#include "access/htup_details.h"
+#include "commands/session_variable.h"
+#include "executor/svariableReceiver.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+#include "utils/syscache.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 value is detoasted before storing it in the
+ * session variable.
+ */
+typedef struct
+{
+ DestReceiver pub;
+ char *varname;
+ Oid typid;
+ int32 typmod;
+ bool need_detoast; /* do we need to detoast the attribute? */
+ int rows; /* row counter */
+} SVariableState;
+
+/*
+ * Prepare to receive tuples from executor.
+ */
+static void
+svariableStartupReceiver(DestReceiver *self, int operation, TupleDesc typeinfo)
+{
+ SVariableState *myState = (SVariableState *) self;
+ Form_pg_attribute attr;
+
+ Assert(myState->pub.mydest == DestVariable);
+ Assert(typeinfo->natts == 1);
+
+ attr = TupleDescAttr(typeinfo, 0);
+
+ Assert(!attr->attisdropped);
+
+ myState->typid = attr->atttypid;
+ myState->typmod = attr->atttypmod;
+
+ myState->need_detoast = attr->attlen == -1;
+ myState->rows = 0;
+}
+
+/*
+ * Receive a tuple from the executor and store it in the session variable.
+ */
+static bool
+svariableReceiveSlot(TupleTableSlot *slot, DestReceiver *self)
+{
+ SVariableState *myState = (SVariableState *) self;
+ Datum value;
+ bool isnull;
+ bool freeval = false;
+
+ /* make sure the tuple is fully deconstructed */
+ slot_getallattrs(slot);
+
+ value = slot->tts_values[0];
+ isnull = slot->tts_isnull[0];
+
+ if (myState->need_detoast && !isnull && VARATT_IS_EXTERNAL(DatumGetPointer(value)))
+ {
+ value = PointerGetDatum(detoast_external_attr((struct varlena *)
+ DatumGetPointer(value)));
+ freeval = true;
+ }
+
+ myState->rows += 1;
+
+ if (myState->rows > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_TOO_MANY_ROWS),
+ errmsg("expression returned more than one row")));
+
+ SetSessionVariableWithTypecheck(myState->varname,
+ myState->typid, myState->typmod,
+ value, isnull);
+
+ if (freeval)
+ pfree(DatumGetPointer(value));
+
+ return true;
+}
+
+/*
+ * Clean up at end of the executor run
+ */
+static void
+svariableShutdownReceiver(DestReceiver *self)
+{
+ if (((SVariableState *) self)->rows == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_DATA_FOUND),
+ errmsg("expression returned no rows")));
+}
+
+/*
+ * Destroy the receiver when we are done with it
+ */
+static void
+svariableDestroyReceiver(DestReceiver *self)
+{
+ pfree(((SVariableState *) self)->varname);
+ 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 = pstrdup(varname);
+
+ return (DestReceiver *) self;
+}
diff --git a/src/backend/tcop/dest.c b/src/backend/tcop/dest.c
index b620766c938..0c1eeeb22a6 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 */
@@ -191,6 +195,7 @@ EndCommand(const QueryCompletion *qc, CommandDest dest, bool force_undecorated_o
case DestTransientRel:
case DestTupleQueue:
case DestExplainSerialize:
+ case DestVariable:
break;
}
}
@@ -237,6 +242,7 @@ NullCommand(CommandDest dest)
case DestTransientRel:
case DestTupleQueue:
case DestExplainSerialize:
+ case DestVariable:
break;
}
}
@@ -281,6 +287,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 00c092e3d7c..6ce3ea0e617 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 2f562a217bf..088429dd424 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2674,6 +2674,7 @@ STRLEN
SV
SVariableData
SVariable
+SVariableState
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
SampleScan
--
2.52.0
[text/x-patch] v20251203-0002-parsing-session-variable-fences.patch (18.6K, 8-v20251203-0002-parsing-session-variable-fences.patch)
download | inline diff:
From 64330c45cd2aea493b3d8ede40802c85adc90db9 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Fri, 21 Nov 2025 15:28:59 +0100
Subject: [PATCH 2/7] 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 | 11 +++
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 | 119 ++++++++++++++++++++++++
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, 229 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 10f1a00c275..150b04f1d4b 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5676,6 +5676,17 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
The session variable holds value in session memory. This value is private
to each session and is released when the session ends.
</para>
+
+ <para>
+ In an query the session variable can be used only inside
+ <firstterm>variable fence</firstterm>. This is special syntax for
+ session variable identifier, and can be used only for session variable
+ identifier. The special syntax for accessing session variables removes
+ risk of collisions between variable identifiers and column names.
+<programlisting>
+SELECT VARIABLE(current_user_id);
+</programlisting>
+ </para>
</sect1>
<sect1 id="ddl-others">
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 34b6410d6a2..fcadcd9bc3f 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -341,6 +341,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 d78e82b87c0..861c2a55f4e 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -111,6 +111,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 d228318dc72..c1eb81f79e5 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1669,6 +1669,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;
@@ -4701,6 +4704,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 05314e7e76b..885106922d0 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -619,6 +619,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);
@@ -1044,6 +1045,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);
@@ -1527,6 +1529,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)
{
@@ -1753,6 +1756,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);
@@ -2004,6 +2008,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)
{
@@ -2476,6 +2481,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);
@@ -2543,6 +2549,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 0a5efd733dc..837330a807f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -531,7 +531,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
@@ -887,7 +887,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 VALUE_P WITH WITHOUT PATH
+ SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT PATH VARIABLE
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
@@ -15719,6 +15719,8 @@ c_expr: columnref { $$ = $1; }
else
$$ = $2;
}
+ | variable_fence
+ { $$ = $1; }
| case_expr
{ $$ = $1; }
| func_expr
@@ -17121,6 +17123,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 4524e49c326..aa2483c96a6 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"
@@ -77,6 +78,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,
@@ -371,6 +373,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));
@@ -904,6 +910,119 @@ 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:
+ 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 51d7703eff7..244efcddf32 100644
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -405,6 +405,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 905c975d83b..2f42627f009 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -2033,6 +2033,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 6cf90be40bb..cb9c456e331 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8816,6 +8816,14 @@ get_parameter(Param *param, deparse_context *context)
}
}
+ /* Note: can be be used by EXPLAIN */
+ if (param->paramkind == PARAM_VARIABLE)
+ {
+ appendStringInfo(context->buf, "VARIABLES(%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 567e5970e50..487464b14b1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -167,6 +167,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);
@@ -322,6 +324,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 1b4436f2ff6..760ca03f7e1 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -378,6 +378,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
{
@@ -385,6 +387,7 @@ typedef enum ParamKind
PARAM_EXEC,
PARAM_SUBLINK,
PARAM_MULTIEXPR,
+ PARAM_VARIABLE,
} ParamKind;
typedef struct Param
@@ -399,6 +402,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 f7d07c84542..84e886940d8 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -228,6 +228,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 d19425b7a71..96857874ffe 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8268,7 +8268,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 0885e55fdec..7bf4750c5b6 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3225,6 +3225,7 @@ ValidatorValidateCB
ValuesScan
ValuesScanState
Var
+VariableFence
VarBit
VarChar
VarParamState
--
2.52.0
[text/x-patch] v20251203-0001-CREATE-VARIABLE-DROP-VARIABLE.patch (32.9K, 9-v20251203-0001-CREATE-VARIABLE-DROP-VARIABLE.patch)
download | inline diff:
From 6824b12a780e72046b9e73469f133515226f8bfa Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Wed, 19 Nov 2025 19:36:07 +0100
Subject: [PATCH 1/7] CREATE VARIABLE, DROP VARIABLE
---
doc/src/sgml/ddl.sgml | 23 ++
doc/src/sgml/glossary.sgml | 15 ++
doc/src/sgml/ref/allfiles.sgml | 2 +
doc/src/sgml/ref/create_variable.sgml | 132 +++++++++++
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 | 218 ++++++++++++++++++
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, 719 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 09ad8400fd0..10f1a00c275 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5655,6 +5655,29 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
</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 temporal database objects that can hold a value.
+ A session variable can be created by the <command>CREATE VARIABLE</command>
+ command. Only owner can use session variable.
+ </para>
+
+ <para>
+ The session variable holds value in session memory. This value is private
+ to each session and is 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 a76cf5c383f..64d5fd40219 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1726,6 +1726,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 e167406c744..a7349919658 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -99,6 +99,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">
@@ -147,6 +148,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..5ed74c5c173
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,132 @@
+<!--
+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 | TEMPORAL } 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 temporal session
+ variable.
+ </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 2cf02c37b17..c03e7692c7a 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -127,6 +127,7 @@
&createType;
&createUser;
&createUserMapping;
+ &createVariable;
&createView;
&deallocate;
&declare;
@@ -175,6 +176,7 @@
&dropType;
&dropUser;
&dropUserMapping;
+ &dropVariable;
&dropView;
&end;
&execute;
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 64cb6278409..d42ed8952a2 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -54,6 +54,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 ceb9a229b63..9524f867857 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 5fc35826b1c..1eeb18fd960 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -42,6 +42,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..d78e82b87c0
--- /dev/null
+++ b/src/backend/commands/session_variable.c
@@ -0,0 +1,218 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 "access/htup_details.h"
+#include "catalog/pg_language.h"
+#include "catalog/pg_proc.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/acl.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+#include "utils/syscache.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;
+ HeapTuple proctup;
+ Form_pg_proc proc;
+ int16 typlen;
+ bool typbyval;
+ Oid typinput;
+ Oid typioparam;
+ AclResult aclresult;
+
+ typenameTypeIdAndMod(pstate, stmt->typeName, &typeid, &typmod);
+
+ if (get_typtype(typeid) != TYPTYPE_BASE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("%s is not a base type",
+ format_type_be(typeid))));
+
+ if (OidIsValid(get_element_type(typeid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("%s type is an array",
+ format_type_be(typeid))));
+
+ aclresult = object_aclcheck(TypeRelationId, typeid, GetUserId(), ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error_type(aclresult, typeid);
+
+ /* allow only buildin types */
+ getTypeInputInfo(typeid, &typinput, &typioparam);
+
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typinput));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cannot to find function oid %d", typinput);
+
+ proc = (Form_pg_proc) GETSTRUCT(proctup);
+ if (proc->prolang != INTERNALlanguageId)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("%s is not a internal type",
+ format_type_be(typeid))));
+
+ ReleaseSysCache(proctup);
+
+ 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;
+
+ 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)));
+
+ (void) hash_search(sessionvars,
+ varname,
+ HASH_REMOVE,
+ NULL);
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c3a0a354a9c..0a5efd733dc 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -290,13 +290,13 @@ 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
CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
DropOpClassStmt DropOpFamilyStmt DropStmt
- DropCastStmt DropRoleStmt
+ DropCastStmt DropRoleStmt DropSessionVarStmt
DropdbStmt DropTableSpaceStmt
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
@@ -789,8 +789,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 VIEW VIEWS VIRTUAL VOLATILE
+ VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARIABLE
+ VARYING VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
WAIT WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -1056,6 +1056,7 @@ stmt:
| CreatePolicyStmt
| CreatePLangStmt
| CreateSchemaStmt
+ | CreateSessionVarStmt
| CreateSeqStmt
| CreateStmt
| CreateSubscriptionStmt
@@ -1083,6 +1084,7 @@ stmt:
| DropTableSpaceStmt
| DropTransformStmt
| DropRoleStmt
+ | DropSessionVarStmt
| DropUserMappingStmt
| DropdbStmt
| ExecuteStmt
@@ -5336,6 +5338,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 ]
@@ -18154,6 +18197,7 @@ unreserved_keyword:
| VALIDATE
| VALIDATOR
| VALUE_P
+ | VARIABLE
| VARYING
| VERSION_P
| VIEW
@@ -18812,6 +18856,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 d18a3a60a46..7dc9c0a6c80 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -48,6 +48,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"
@@ -183,6 +184,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:
@@ -201,6 +203,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:
@@ -1063,6 +1066,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,
@@ -1388,6 +1400,7 @@ ProcessUtilitySlow(ParseState *pstate,
}
break;
+
/*
* ************* object creation / destruction **************
*/
@@ -3235,6 +3248,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));
@@ -3773,6 +3794,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 20d7a65c614..d257b4fc37a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1363,6 +1363,7 @@ static const pgsql_thing_t words_after_create[] = {
* TABLE ... */
{"USER", Query_for_list_of_roles, NULL, NULL, Keywords_for_user_thing},
{"USER MAPPING FOR", NULL, NULL, NULL},
+ {"VARIABLE", NULL, NULL, NULL, NULL, THING_NO_ALTER},
{"VIEW", NULL, NULL, &Query_for_list_of_views},
{NULL} /* end of list */
};
@@ -3723,7 +3724,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");
@@ -4076,6 +4077,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 d14294a4ece..567e5970e50 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3555,6 +3555,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 5d4fe27ef96..8b1f2bd3b68 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -489,6 +489,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 c4606d65043..7e59d0a5275 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -123,6 +123,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)
@@ -175,6 +176,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..cc6f59e8be5
--- /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 to fail
+CREATE TEMPORARY VARIABLE x AS int;
+ERROR: session variable "x" already exists
+-- should to 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 to fail
+CREATE VARIABLE x AS test_type;
+ERROR: only temporal session variables are supported
+DROP TYPE test_type;
+-- should to fail
+CREATE VARIABLE x AS int[];
+ERROR: only temporal session variables are supported
+CREATE DOMAIN test_domain AS int;
+-- should to fail
+CREATE VARIABLE x AS test_domain;
+ERROR: only temporal session variables are supported
+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 to 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 cc6d799bcea..9da7c6ab928 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..92e97ea2ab0
--- /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 to fail
+CREATE TEMPORARY VARIABLE x AS int;
+
+-- should to fail
+DROP VARIABLE y;
+
+-- should be ok
+DROP VARIABLE x;
+
+CREATE TYPE test_type AS (x int, y int);
+
+-- should to fail
+CREATE VARIABLE x AS test_type;
+
+DROP TYPE test_type;
+
+-- should to fail
+CREATE VARIABLE x AS int[];
+
+CREATE DOMAIN test_domain AS int;
+
+-- should to fail
+CREATE 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 to 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 cf3f6a7dafd..0885e55fdec 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -562,6 +562,7 @@ CreateRoleStmt
CreateSchemaStmt
CreateSchemaStmtContext
CreateSeqStmt
+CreateSessionVarStmt
CreateStatsStmt
CreateStmt
CreateStmtContext
@@ -666,6 +667,7 @@ DropBehavior
DropOwnedStmt
DropReplicationSlotCmd
DropRoleStmt
+DropSessionVarStmt
DropStmt
DropSubscriptionStmt
DropTableSpaceStmt
@@ -2670,6 +2672,8 @@ SSL_CTX
STARTUPINFO
STRLEN
SV
+SVariableData
+SVariable
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
SampleScan
--
2.52.0
view thread (439+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: proposal: schema variables
In-Reply-To: <CAFj8pRDisL7v_t9-QYqxYOLvdQJCbZBY8hjzXNHMWxqEoVun4g@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