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: Thu, 12 Feb 2026 20:25:03 +0100
Message-ID: <CAFj8pRDLL-srue=m+je26vFrMzwETWwOfsEVNwr7=_KhuWA1bQ@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRCJkgYNnWfOnhzeh_MucAp0V4j_fhoSLSiD+-1ZTpzizQ@mail.gmail.com>
References: <CAFj8pRDJ9cq00VYSHxs6LsoHNWjhYXyWWBtV6UgeWwhs0AHa9A@mail.gmail.com>
<CAFj8pRAe1BN1iLoVaD4CLtMY4pg2XROVqmU8HngqBaFRhF4zCg@mail.gmail.com>
<CAFj8pRBryCY4LqtrPu9E_mtZYkNfZb64-MetWE0232whdOLgjA@mail.gmail.com>
<CAFj8pRCPjHT8xfnMHRroc_wx4wBT4ocqzjH--jqo0xHqec4qhA@mail.gmail.com>
<CAFj8pRBTmSPUPET-Jo=REsLLx15kmHqYXmXwL2BKLeZjZqUUEg@mail.gmail.com>
<CAFj8pRAOTQ5BkKvCFHkUE=HWzirZhVzxE5iugbcOsz=z5R0ogA@mail.gmail.com>
<CAFj8pRDmCF05tVDbzkwL45WHLhdZnbkQLn9HA3RA-5LGu=wEXg@mail.gmail.com>
<CAFj8pRCACo9_fwfe3ixgrRfThon+Nu26b1phd-ge7v5XCx4=9A@mail.gmail.com>
<CAFj8pRDNBHEf2nrU3Xx3S-Vmtj+YUiGxc0CV78u7uwbxXP_acw@mail.gmail.com>
<CAFj8pRAnWR5TV=kRu9L-xUUkxoeoWUXDKS_Qixfqj4YptjGUsA@mail.gmail.com>
<CAFj8pRBHjSEE3A500MLyOPQogqaYGu=XVKvq4LnSnuHsFSxeJg@mail.gmail.com>
<CAFj8pRDisL7v_t9-QYqxYOLvdQJCbZBY8hjzXNHMWxqEoVun4g@mail.gmail.com>
<[email protected]>
<CAFj8pRAfQt13pacZgL_tQw8amczvLYEVDqKgjUNHDcQkwJT2_g@mail.gmail.com>
<[email protected]>
<CAFj8pRDZX=3zbQZhiKsN5ocSmjpcGtZxhac+zW7e=cDM6vJG=Q@mail.gmail.com>
<CAFj8pRC6Efuqc0xsPzhynpN2TXtJNsx6Oh3ZVOpk3rdCUNf4Bg@mail.gmail.com>
<CAFj8pRC5DawEgZTOo1o4HH98znrCh6r72gMTgXLLpuNLCVTMvA@mail.gmail.com>
<CAFj8pRDoetKkQGqwPtku5hpomPUAvLKihhH6DGmPgVQmaK+UDg@mail.gmail.com>
<CAFj8pRBsbg7pEFN3GC8hj_Y3DK08QONseteeZZXyaXk_G8QnyA@mail.gmail.com>
<CAFj8pRCBmUYWz9jQrMTb63h96X5TVD919XBUG3rrZcDAbYYn6A@mail.gmail.com>
<CAFj8pRCJkgYNnWfOnhzeh_MucAp0V4j_fhoSLSiD+-1ZTpzizQ@mail.gmail.com>
Hi
fresh rebase cf74558feb8f41b2bc459f59ed3f991024d04893
regards
Pavel
Attachments:
[text/x-patch] v20260212-0011-subtransaction-support-for-session-variables-DDL-CRE.patch (8.0K, 3-v20260212-0011-subtransaction-support-for-session-variables-DDL-CRE.patch)
download | inline diff:
From 4e731612e59e110bc4eb224d3943b846fcc4e58f Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 8 Dec 2025 05:00:12 +0100
Subject: [PATCH 11/11] subtransaction support for session variables DDL
(CREATE, DROP)
If we support transactional DDL for CREATE, DROP session variables, we should to
support subtransactions too. Implementation is simple. Any value has two new flags:
created_subid and dropped_subid. At the subtransaction end for rollback we
clean entries from the stack related to subtransactions. When commit we update
created_subid and dropped_subid for parent subtransaction.
---
src/backend/access/transam/xact.c | 4 +
src/backend/commands/session_variable.c | 109 ++++++++++++++++++
src/include/commands/session_variable.h | 3 +
.../expected/session_variables_ddl.out | 21 ++++
.../regress/sql/session_variables_ddl.sql | 12 ++
5 files changed, 149 insertions(+)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index da5170861b8..d32ee493a48 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -5212,6 +5212,8 @@ CommitSubTransaction(void)
AtEOSubXact_SPI(true, s->subTransactionId);
AtEOSubXact_on_commit_actions(true, s->subTransactionId,
s->parent->subTransactionId);
+ AtEOSubXact_SessionVariables(true, s->subTransactionId,
+ s->parent->subTransactionId);
AtEOSubXact_Namespace(true, s->subTransactionId,
s->parent->subTransactionId);
AtEOSubXact_Files(true, s->subTransactionId,
@@ -5381,6 +5383,8 @@ AbortSubTransaction(void)
AtEOSubXact_SPI(false, s->subTransactionId);
AtEOSubXact_on_commit_actions(false, s->subTransactionId,
s->parent->subTransactionId);
+ AtEOSubXact_SessionVariables(false, s->subTransactionId,
+ s->parent->subTransactionId);
AtEOSubXact_Namespace(false, s->subTransactionId,
s->parent->subTransactionId);
AtEOSubXact_Files(false, s->subTransactionId,
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 8f249d515d5..4a311d2498b 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "access/xact.h"
#include "catalog/pg_language.h"
#include "commands/session_variable.h"
#include "executor/executor.h"
@@ -59,6 +60,8 @@ typedef struct SVariableData
bool stacked;
LocalTransactionId created_lxid;
LocalTransactionId dropped_lxid;
+ SubTransactionId created_subid;
+ SubTransactionId dropped_subid;
} SVariableData;
typedef SVariableData *SVariable;
@@ -349,6 +352,8 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
svar->stacked = false;
svar->dropped_lxid = InvalidLocalTransactionId;
svar->created_lxid = MyProc->vxid.lxid;
+ svar->dropped_subid = InvalidSubTransactionId;
+ svar->created_subid = GetCurrentSubTransactionId();
created_or_dropped_lxid = MyProc->vxid.lxid;
}
@@ -385,6 +390,7 @@ DropVariableByName(DropSessionVarStmt *stmt)
stmt->name)));
svar->dropped_lxid = MyProc->vxid.lxid;
+ svar->dropped_subid = GetCurrentSubTransactionId();
created_or_dropped_lxid = MyProc->vxid.lxid;
}
@@ -454,6 +460,7 @@ AtPreEOXact_SessionVariables(bool isCommit)
free_stacked_svars(svar->prev);
svar->prev = NULL;
svar->created_lxid = InvalidLocalTransactionId;
+ svar->created_subid = InvalidSubTransactionId;
}
}
else
@@ -500,6 +507,7 @@ AtPreEOXact_SessionVariables(bool isCommit)
/* revert dropped flag */
svar->dropped_lxid = InvalidLocalTransactionId;
+ svar->dropped_subid = InvalidSubTransactionId;
}
}
}
@@ -509,6 +517,107 @@ AtPreEOXact_SessionVariables(bool isCommit)
}
}
+/*
+ * Post-subcommit or post-subabort cleanup
+ *
+ * During subabort, we can immediately remove entries created during this
+ * subtransaction. During subcommit, just transfer entries marked during
+ * this subtransaction as being the parent's responsibility.
+ */
+void
+AtEOSubXact_SessionVariables(bool isCommit,
+ SubTransactionId mySubid,
+ SubTransactionId parentSubid)
+{
+ if (created_or_dropped_lxid != InvalidLocalTransactionId)
+ {
+ HASH_SEQ_STATUS status;
+ SVariable svar;
+
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(sessionvars);
+
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ if ((svar->dropped_lxid != InvalidLocalTransactionId) ||
+ (svar->created_lxid != InvalidLocalTransactionId))
+ {
+ if (!isCommit)
+ {
+ SVariable iterator = svar;
+ SVariable last = NULL;
+ SVariable first = NULL;
+
+ /* remove entries or flags by current subtransactions */
+ while (iterator)
+ {
+ SVariable current = iterator;
+
+ iterator = current->prev;
+
+ if (current->dropped_subid == mySubid)
+ {
+ current->dropped_lxid = InvalidLocalTransactionId;
+ current->dropped_subid = InvalidSubTransactionId;
+ }
+
+ if (current->created_subid == mySubid)
+ {
+ free_svar_value(current);
+ if (current->stacked)
+ pfree(current);
+ }
+ else
+ {
+ /* remember first not deleted svar */
+ if (first == NULL)
+ first = current;
+
+ if (last)
+ last->prev = current;
+
+ last = current;
+ }
+ }
+
+ /* Some svars was removed - set hashtab entry or remove it */
+ if (!first)
+ {
+ /* we have to remove entry from hash table */
+ (void) hash_search(sessionvars,
+ NameStr(svar->varname),
+ HASH_REMOVE,
+ NULL);
+ }
+ else if (first->stacked)
+ {
+ memcpy(svar, first, sizeof(SVariableData));
+ svar->stacked = false;
+ pfree(first);
+ }
+ }
+ else
+ {
+ SVariable iterator = svar;
+
+ /* transfer responsibility to parent */
+ while (iterator)
+ {
+ if (iterator->dropped_subid == mySubid)
+ iterator->dropped_subid = parentSubid;
+ if (iterator->created_subid == mySubid)
+ iterator->created_subid = parentSubid;
+
+ iterator = iterator->prev;
+ }
+ }
+ }
+ }
+ }
+}
+
/*
* Assign the result of the evaluated expression to the session variable
*/
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 1218c566767..45ccbe2f046 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -40,5 +40,8 @@ extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo para
extern void ResetSessionVariables(void);
extern void AtPreEOXact_SessionVariables(bool isCommit);
+extern void AtEOSubXact_SessionVariables(bool isCommit,
+ SubTransactionId mySubid,
+ SubTransactionId parentSubid);
#endif
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 2d00471da96..9031b5c384c 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -107,4 +107,25 @@ SELECT VARIABLE(x);
Hi
(1 row)
+BEGIN;
+SAVEPOINT s1;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+ROLLBACK TO s1;
+SELECT VARIABLE(x);
+ x
+----
+ Hi
+(1 row)
+
+COMMIT;
DROP VARIABLE x;
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 7335f15ed39..5ec412ad7c9 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -98,4 +98,16 @@ SELECT VARIABLE(x);
ROLLBACK;
SELECT VARIABLE(x);
+BEGIN;
+SAVEPOINT s1;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ROLLBACK TO s1;
+SELECT VARIABLE(x);
+COMMIT;
+
DROP VARIABLE x;
--
2.53.0
[text/x-patch] v20260212-0010-transactional-DDL-CREATE-VARIABLE-DROP-VARIABLE.patch (11.4K, 4-v20260212-0010-transactional-DDL-CREATE-VARIABLE-DROP-VARIABLE.patch)
download | inline diff:
From 30b53b5ed6940c92ab3005096385d4af8674ca94 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sat, 6 Dec 2025 07:35:30 +0100
Subject: [PATCH 10/11] transactional DDL - CREATE VARIABLE, DROP VARIABLE
Generally PostgreSQL DDL statements are transactional. We can implement transactional behave
for ( CREATE | DROP ) VARIABLE too. Implementation is almost simple. DROP just set dropped_lxid,
CREATE set created_lxid and moves previous entry of related sessionvars hashtab to stack. At the
end of transaction for commit we just clean this stack. For rollback we do copy the bootom value
from the stack and again clean stack.
---
src/backend/access/transam/xact.c | 5 +
src/backend/commands/session_variable.c | 217 ++++++++++++++++--
src/include/commands/session_variable.h | 2 +
.../expected/session_variables_ddl.out | 38 +++
.../regress/sql/session_variables_ddl.sql | 21 ++
5 files changed, 258 insertions(+), 25 deletions(-)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index eba4f063168..da5170861b8 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -39,6 +39,7 @@
#include "commands/async.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
+#include "commands/session_variable.h"
#include "common/pg_prng.h"
#include "executor/spi.h"
#include "libpq/be-fsstubs.h"
@@ -2333,6 +2334,9 @@ CommitTransaction(void)
/* close large objects before lower-level cleanup */
AtEOXact_LargeObject(true);
+ /* remove stacked session variables */
+ AtPreEOXact_SessionVariables(true);
+
/*
* Insert notifications sent by NOTIFY commands into the queue. This
* should be late in the pre-commit sequence to minimize time spent
@@ -2939,6 +2943,7 @@ AbortTransaction(void)
AtAbort_Portals();
smgrDoPendingSyncs(false, is_parallel_worker);
AtEOXact_LargeObject(false);
+ AtPreEOXact_SessionVariables(false);
AtAbort_Notify();
AtEOXact_RelationMap(false, is_parallel_worker);
AtAbort_Twophase();
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 6430455c0a6..8f249d515d5 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -54,6 +54,11 @@ typedef struct SVariableData
int16 typlen;
bool typbyval;
+
+ struct SVariableData *prev;
+ bool stacked;
+ LocalTransactionId created_lxid;
+ LocalTransactionId dropped_lxid;
} SVariableData;
typedef SVariableData *SVariable;
@@ -62,6 +67,14 @@ static HTAB *sessionvars = NULL; /* hash table for session variables */
static MemoryContext SVariableMemoryContext = NULL;
+/*
+ * When we to remove committed dropped variables or uncommitted
+ * created variables from sessionvars tab. created_or_dropped_lxid
+ * is transaction id of transaction when some of DROP or CREATE variable
+ * was executed.
+ */
+static LocalTransactionId created_or_dropped_lxid = InvalidLocalTransactionId;
+
/*
* Create the hash table for storing session variables.
*/
@@ -103,6 +116,14 @@ search_variable(char *varname, bool missing_ok)
svar = (SVariable) hash_search(sessionvars, varname,
HASH_FIND, NULL);
+ /* Session variable can be dropped inside current transaction */
+ if (svar && svar->dropped_lxid != InvalidLocalTransactionId)
+ {
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(svar->dropped_lxid == MyProc->vxid.lxid);
+ svar = NULL;
+ }
+
if (!svar && !missing_ok)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
@@ -235,6 +256,7 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
Oid typcollation;
Oid varowner = GetUserId();
SVariable svar;
+ SVariable prev_svar = NULL;
bool found;
int16 typlen;
bool typbyval;
@@ -279,19 +301,37 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
if (found)
{
- if (stmt->if_not_exists)
+ if (svar->dropped_lxid == InvalidLocalTransactionId)
{
- ereport(NOTICE,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("session variable \"%s\" already exists, skipping",
- stmt->name)));
- return;
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists, skipping",
+ stmt->name)));
+ return;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
}
else
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("session variable \"%s\" already exists",
- stmt->name)));
+ {
+ MemoryContext oldcxt;
+
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(svar->dropped_lxid == MyProc->vxid.lxid);
+
+ oldcxt = MemoryContextSwitchTo(SVariableMemoryContext);
+ prev_svar = palloc_object(SVariableData);
+ memcpy(prev_svar, svar, sizeof(SVariableData));
+ prev_svar->stacked = true;
+ memset(svar, 0, sizeof(SVariableData));
+
+ MemoryContextSwitchTo(oldcxt);
+ }
}
namestrcpy(&svar->varname, stmt->name);
@@ -304,6 +344,12 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
svar->value = (Datum) 0;
svar->isnull = true;
+
+ svar->prev = prev_svar;
+ svar->stacked = false;
+ svar->dropped_lxid = InvalidLocalTransactionId;
+ svar->created_lxid = MyProc->vxid.lxid;
+ created_or_dropped_lxid = MyProc->vxid.lxid;
}
/*
@@ -338,14 +384,129 @@ DropVariableByName(DropSessionVarStmt *stmt)
errmsg("must be owner of session variable %s",
stmt->name)));
- if (!svar->typbyval && !svar->isnull)
+ svar->dropped_lxid = MyProc->vxid.lxid;
+ created_or_dropped_lxid = MyProc->vxid.lxid;
+}
+
+static void
+free_svar_value(SVariable svar)
+{
+ if (!svar->isnull && !svar->typbyval)
pfree(DatumGetPointer(svar->value));
+}
+
+static void
+free_stacked_svars(SVariable svar)
+{
+ while (svar)
+ {
+ SVariable current = svar;
+
+ free_svar_value(current);
+ svar = current->prev;
+ pfree(current);
+ }
+}
+
+/*
+ * remove dropped committed entries or created uncommitted entries
+ * from hash table.
+ */
+void
+AtPreEOXact_SessionVariables(bool isCommit)
+{
+ if (created_or_dropped_lxid != InvalidLocalTransactionId)
+ {
+ HASH_SEQ_STATUS status;
+ SVariable svar;
- if (hash_search(sessionvars,
- stmt->name,
- HASH_REMOVE,
- NULL) == NULL)
- elog(ERROR, "hash table corrupted");
+ Assert(created_or_dropped_lxid == MyProc->vxid.lxid);
+ Assert(sessionvars);
+
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ if ((svar->dropped_lxid != InvalidLocalTransactionId) ||
+ (svar->created_lxid != InvalidLocalTransactionId))
+ {
+ Assert((svar->dropped_lxid == InvalidLocalTransactionId) ||
+ (svar->dropped_lxid == MyProc->vxid.lxid));
+
+ Assert((svar->created_lxid == InvalidLocalTransactionId) ||
+ (svar->created_lxid == MyProc->vxid.lxid));
+
+ if (isCommit)
+ {
+ if (svar->dropped_lxid == MyProc->vxid.lxid)
+ {
+ free_stacked_svars(svar->prev);
+ free_svar_value(svar);
+
+ (void) hash_search(sessionvars,
+ NameStr(svar->varname),
+ HASH_REMOVE,
+ NULL);
+ svar = NULL;
+ }
+ else
+ {
+ free_stacked_svars(svar->prev);
+ svar->prev = NULL;
+ svar->created_lxid = InvalidLocalTransactionId;
+ }
+ }
+ else
+ {
+ SVariable iter;
+
+ /*
+ * We have to search value the oldest svar in the stack. If it is just dropped,
+ * then we revert dropped flag. If it is created in current transaction, then
+ * we remove this svar too.
+ */
+ iter = svar;
+ while (iter->prev)
+ {
+ SVariable current = iter;
+
+ free_svar_value(current);
+
+ iter = current->prev;
+
+ if (current->stacked)
+ pfree(current);
+ }
+
+ if (iter->created_lxid == MyProc->vxid.lxid)
+ {
+ free_svar_value(iter);
+ if (iter->stacked)
+ pfree(iter);
+
+ (void) hash_search(sessionvars,
+ NameStr(svar->varname),
+ HASH_REMOVE,
+ NULL);
+ }
+ else
+ {
+ if (iter->stacked)
+ {
+ memcpy(svar, iter, sizeof(SVariableData));
+ svar->stacked = false;
+ pfree(iter);
+ }
+
+ /* revert dropped flag */
+ svar->dropped_lxid = InvalidLocalTransactionId;
+ }
+ }
+ }
+ }
+
+ created_or_dropped_lxid = InvalidLocalTransactionId;
+ }
}
/*
@@ -430,23 +591,29 @@ ExecuteLetStmt(ParseState *pstate,
}
/*
- * Fast drop of the complete content of the session variables hash table, and
- * cleanup of any list that wouldn't be relevant anymore.
* This is used by the DISCARD TEMP.
*/
void
ResetSessionVariables(void)
{
- /* destroy hash table and reset related memory context */
+ /* mark all session variables as dropped */
if (sessionvars)
{
- hash_destroy(sessionvars);
- sessionvars = NULL;
- }
+ HASH_SEQ_STATUS status;
+ SVariable svar;
+ bool found = false;
- /* release memory allocated by session variables */
- if (SVariableMemoryContext != NULL)
- MemoryContextReset(SVariableMemoryContext);
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ svar->dropped_lxid = MyProc->vxid.lxid;
+ found = true;
+ }
+
+ if (found)
+ created_or_dropped_lxid = MyProc->vxid.lxid;
+ }
}
/*
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 3f07ae55aac..1218c566767 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -39,4 +39,6 @@ extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo para
extern void ResetSessionVariables(void);
+extern void AtPreEOXact_SessionVariables(bool isCommit);
+
#endif
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 758ce582fca..2d00471da96 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -70,3 +70,41 @@ SELECT * FROM pg_get_temporary_session_variables_names();
------------------------------------------
(0 rows)
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+COMMIT;
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ x
+-------
+ Hello
+(1 row)
+
+ROLLBACK;
+SELECT VARIABLE(x);
+ x
+----
+ Hi
+(1 row)
+
+DROP VARIABLE x;
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index c4cbfd17169..7335f15ed39 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -78,3 +78,24 @@ SELECT * FROM pg_get_temporary_session_variables_names();
DROP VARIABLE x;
DROP VARIABLE y;
SELECT * FROM pg_get_temporary_session_variables_names();
+
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+COMMIT;
+SELECT VARIABLE(x);
+
+LET x = 'Hi';
+BEGIN;
+DROP VARIABLE x;
+CREATE TEMP VARIABLE x AS varchar;
+LET x = 'Hello';
+SELECT VARIABLE(x);
+ROLLBACK;
+SELECT VARIABLE(x);
+
+DROP VARIABLE x;
--
2.53.0
[text/x-patch] v20260212-0009-use-names-of-currently-used-temp-variables-for-tab-c.patch (5.9K, 5-v20260212-0009-use-names-of-currently-used-temp-variables-for-tab-c.patch)
download | inline diff:
From 0bf9fb9efaa969f28312753c6db15787cf26ecd2 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Thu, 4 Dec 2025 18:49:06 +0100
Subject: [PATCH 09/11] use names of currently used temp variables for tab
complete of DROP VARIABLE, LET and VARIABLE()
---
src/backend/commands/session_variable.c | 36 +++++++++++++++++++
src/bin/psql/tab-complete.in.c | 17 +++++++++
src/include/catalog/pg_proc.dat | 5 +++
.../expected/session_variables_ddl.out | 16 +++++++++
.../regress/sql/session_variables_ddl.sql | 8 +++++
5 files changed, 82 insertions(+)
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 2e3ce5c4017..6430455c0a6 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -18,6 +18,7 @@
#include "commands/session_variable.h"
#include "executor/executor.h"
#include "executor/svariableReceiver.h"
+#include "funcapi.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
#include "rewrite/rewriteHandler.h"
@@ -447,3 +448,38 @@ ResetSessionVariables(void)
if (SVariableMemoryContext != NULL)
MemoryContextReset(SVariableMemoryContext);
}
+
+/*
+ * pg_get_temporary_session_variables_names
+ *
+ * Returns list of temporary session variables. It is used by psql's
+ * tab complete for DROP VARIABLE and LET commands.
+ */
+Datum
+pg_get_temporary_session_variables_names(PG_FUNCTION_ARGS)
+{
+ InitMaterializedSRF(fcinfo, MAT_SRF_USE_EXPECTED_DESC);
+
+ if (sessionvars)
+ {
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HASH_SEQ_STATUS status;
+ SVariable svar;
+
+ hash_seq_init(&status, sessionvars);
+
+ while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+ {
+ Datum values[1];
+ bool nulls[1];
+
+ values[0] = CStringGetTextDatum((NameStr(svar->varname)));
+ nulls[0] = false;
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+ }
+
+ return (Datum) 0;
+}
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 94311183636..60e2c15ce03 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1200,6 +1200,11 @@ Keywords_for_list_of_owner_roles, "PUBLIC"
" FROM pg_catalog.pg_timezone_names() "\
" WHERE pg_catalog.quote_literal(pg_catalog.lower(name)) LIKE pg_catalog.lower('%s')"
+#define Query_for_list_of_temporary_session_variables \
+"SELECT varname "\
+" FROM pg_catalog.pg_get_temporary_session_variables_names() AS varname "\
+" WHERE varname LIKE '%s'"
+
/* Privilege options shared between GRANT and REVOKE */
#define Privilege_options_of_grant_and_revoke \
"SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", \
@@ -4406,6 +4411,10 @@ match_previous_words(int pattern_id,
else if (Matches("DROP", "TRANSFORM", "FOR", MatchAny, "LANGUAGE", MatchAny))
COMPLETE_WITH("CASCADE", "RESTRICT");
+ /* DROP VARIABLE */
+ else if (Matches("DROP", "VARIABLE"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_temporary_session_variables);
+
/* EXECUTE */
else if (Matches("EXECUTE"))
COMPLETE_WITH_QUERY(Query_for_list_of_prepared_statements);
@@ -4846,6 +4855,8 @@ match_previous_words(int pattern_id,
/* LET */
/* Complete LET <variable> with "=" */
+ else if (Matches("LET"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_temporary_session_variables);
else if (TailMatches("LET", MatchAny))
COMPLETE_WITH("=");
@@ -5414,6 +5425,12 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("'standby_replay'", "'standby_write'", "'standby_flush'", "'primary_flush'");
}
+/*
+ * VARIABLE fence
+ */
+ else if (TailMatches("VARIABLE", "("))
+ COMPLETE_WITH_QUERY(Query_for_list_of_temporary_session_variables);
+
/* WITH [RECURSIVE] */
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 83f6501df38..2a5f2ebffb9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12695,4 +12695,9 @@
proname => 'hashoid8extended', prorettype => 'int8',
proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
+# Session variables support
+{ oid => '8068', descr => 'returns names of temporary session variables',
+ proname => 'pg_get_temporary_session_variables_names', prorows => '1000', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'text', proargtypes => '',
+ prosrc => 'pg_get_temporary_session_variables_names' },
]
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 9f5b088de72..758ce582fca 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -54,3 +54,19 @@ NOTICE: session variable "x" already exists, skipping
DROP VARIABLE x;
DROP VARIABLE IF EXISTS x;
NOTICE: session variable "x" does not exists, skipping
+CREATE TEMP VARIABLE x AS int;
+CREATE TEMP VARIABLE y AS int;
+SELECT * FROM pg_get_temporary_session_variables_names();
+ pg_get_temporary_session_variables_names
+------------------------------------------
+ y
+ x
+(2 rows)
+
+DROP VARIABLE x;
+DROP VARIABLE y;
+SELECT * FROM pg_get_temporary_session_variables_names();
+ pg_get_temporary_session_variables_names
+------------------------------------------
+(0 rows)
+
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 60f78671e3b..c4cbfd17169 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -70,3 +70,11 @@ CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
DROP VARIABLE x;
DROP VARIABLE IF EXISTS x;
+
+CREATE TEMP VARIABLE x AS int;
+CREATE TEMP VARIABLE y AS int;
+SELECT * FROM pg_get_temporary_session_variables_names();
+
+DROP VARIABLE x;
+DROP VARIABLE y;
+SELECT * FROM pg_get_temporary_session_variables_names();
--
2.53.0
[text/x-patch] v20260212-0008-support-CREATE-IF-NOT-EXISTS-and-DROP-IF-EXISTS.patch (11.2K, 6-v20260212-0008-support-CREATE-IF-NOT-EXISTS-and-DROP-IF-EXISTS.patch)
download | inline diff:
From d8f40663e1a9212466512b97f6098dd9087a47d4 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Thu, 4 Dec 2025 06:17:54 +0100
Subject: [PATCH 08/11] support CREATE IF NOT EXISTS and DROP IF EXISTS
---
doc/src/sgml/ref/create_variable.sgml | 12 ++++-
doc/src/sgml/ref/drop_variable.sgml | 12 ++++-
src/backend/commands/session_variable.c | 46 +++++++++++++------
src/backend/parser/gram.y | 31 ++++++++++++-
src/backend/tcop/utility.c | 2 +-
src/include/commands/session_variable.h | 2 +-
src/include/nodes/parsenodes.h | 4 +-
.../expected/session_variables_ddl.out | 6 +++
.../regress/sql/session_variables_ddl.sql | 6 +++
9 files changed, 100 insertions(+), 21 deletions(-)
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
index 1315b1248c7..def368fc237 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
+CREATE { TEMP | TEMPORARY } VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
</synopsis>
</refsynopsisdiv>
<refsect1>
@@ -69,6 +69,16 @@ CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replac
<variablelist>
+ <varlistentry id="sql-createvariable-if-not-exists">
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the name already exists. A notice is issued in
+ this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createvariable-name">
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
index dede42e4ffb..5de6a737493 100644
--- a/doc/src/sgml/ref/drop_variable.sgml
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-DROP VARIABLE <replaceable class="parameter">name</replaceable>
+DROP VARIABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -42,6 +42,16 @@ DROP VARIABLE <replaceable class="parameter">name</replaceable>
<refsect1>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the session variable does not exist. A notice is
+ issued in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 5cc19216f6e..2e3ce5c4017 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -92,7 +92,7 @@ create_sessionvars_hashtables(void)
* Returns entry of session variable specified by name
*/
static SVariable
-search_variable(char *varname)
+search_variable(char *varname, bool missing_ok)
{
SVariable svar;
@@ -102,7 +102,7 @@ search_variable(char *varname)
svar = (SVariable) hash_search(sessionvars, varname,
HASH_FIND, NULL);
- if (!svar)
+ if (!svar && !missing_ok)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("session variable \"%s\" doesn't exist",
@@ -124,7 +124,7 @@ get_session_variable_type_typmod_collid(char *varname,
{
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
/* only owner can set content of variable */
*typid = svar->vartype;
@@ -144,7 +144,7 @@ GetSessionVariableWithTypecheck(char *varname,
SVariable svar;
Datum result;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
if (svar->vartype != typid || svar->vartypmod != typmod)
ereport(ERROR,
@@ -181,7 +181,7 @@ SetSessionVariableWithTypecheck(char *varname,
{
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
if (svar->vartype != typid || svar->vartypmod != typmod)
ereport(ERROR,
@@ -277,10 +277,21 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
HASH_ENTER, &found);
if (found)
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("session variable \"%s\" already exists",
- stmt->name)));
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists, skipping",
+ stmt->name)));
+ return;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
+ }
namestrcpy(&svar->varname, stmt->name);
svar->vartype = typeid;
@@ -298,7 +309,7 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
* Drop variable by name
*/
void
-DropVariableByName(char *varname)
+DropVariableByName(DropSessionVarStmt *stmt)
{
SVariable svar;
@@ -310,20 +321,27 @@ DropVariableByName(char *varname)
PreventCommandIfParallelMode("DROP VARIABLE");
PreventCommandDuringRecovery("DROP VARIABLE");
- svar = search_variable(varname);
+ svar = search_variable(stmt->name, stmt->missing_ok);
+ if (!svar)
+ {
+ ereport(NOTICE,
+ (errmsg("session variable \"%s\" does not exists, skipping",
+ stmt->name)));
+ return;
+ }
/* only owner can get content of variable */
if (svar->varowner != GetUserId() && !superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be owner of session variable %s",
- varname)));
+ stmt->name)));
if (!svar->typbyval && !svar->isnull)
pfree(DatumGetPointer(svar->value));
if (hash_search(sessionvars,
- varname,
+ stmt->name,
HASH_REMOVE,
NULL) == NULL)
elog(ERROR, "hash table corrupted");
@@ -347,7 +365,7 @@ ExecuteLetStmt(ParseState *pstate,
char *varname = query->resultVariable;
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
/* only owner can set content of variable */
if (svar->varowner != GetUserId() && !superuser())
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8fa3af60881..80c1f5a08da 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5394,7 +5394,7 @@ create_extension_opt_item:
/*****************************************************************************
*
* QUERY :
- * CREATE { TEMP | TEMPORARY } VARIABLE varname [AS] type
+ * CREATE { TEMP | TEMPORARY } VARIABLE [IF NOT EXISTS ] varname [AS] type
*
*****************************************************************************/
@@ -5411,14 +5411,31 @@ CreateSessionVarStmt:
n->name = $4;
n->typeName = $6;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE OptTemp VARIABLE IF_P NOT EXISTS ColId opt_as Typename
+ {
+ CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
+
+ if ($2 != RELPERSISTENCE_TEMP)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only temporal session variables are supported"),
+ parser_errposition(@2)));
+
+ n->name = $7;
+ n->typeName = $9;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
+
;
/*****************************************************************************
*
* QUERY :
- * DROP VARIABLE varname
+ * DROP VARIABLE [ IF EXISTS ] varname
*
*****************************************************************************/
@@ -5428,8 +5445,18 @@ DropSessionVarStmt:
DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
n->name = $3;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
+ | DROP VARIABLE IF_P EXISTS ColId
+ {
+ DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
+
+ n->name = $5;
+ n->missing_ok = true;
$$ = (Node *) n;
}
+
;
/*****************************************************************************
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 063759df396..77e85d6f2b1 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1073,7 +1073,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_DropSessionVarStmt:
/* No event triggers for catalog less session variables */
- DropVariableByName(((DropSessionVarStmt *) parsetree)->name);
+ DropVariableByName((DropSessionVarStmt *) parsetree);
break;
case T_LetStmt:
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index cc1aa7ce23b..3f07ae55aac 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -22,7 +22,7 @@
#include "tcop/cmdtag.h"
extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
-extern void DropVariableByName(char *varname);
+extern void DropVariableByName(DropSessionVarStmt *stmt);
extern Datum GetSessionVariableWithTypecheck(char *varname, Oid typid, int32 typmod, bool *isnull);
extern void SetSessionVariableWithTypecheck(char *varname,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ba4516f5f2e..903ab15a9c6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3623,6 +3623,7 @@ typedef struct CreateSessionVarStmt
NodeTag type;
char *name; /* the variable to create */
TypeName *typeName; /* the type of variable */
+ bool if_not_exists; /* just do nothing if variable already exists? */
} CreateSessionVarStmt;
/* ----------------------
@@ -3632,7 +3633,8 @@ typedef struct CreateSessionVarStmt
typedef struct DropSessionVarStmt
{
NodeTag type;
- char *name;
+ char *name; /* the variable name to drop */
+ bool missing_ok; /* skip error of variable is missing */
} DropSessionVarStmt;
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index c36febd894e..9f5b088de72 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -48,3 +48,9 @@ ERROR: session variable "x" already exists
DISCARD TEMP;
-- should be ok
CREATE TEMP VARIABLE x AS int;
+-- should be ok
+CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
+NOTICE: session variable "x" already exists, skipping
+DROP VARIABLE x;
+DROP VARIABLE IF EXISTS x;
+NOTICE: session variable "x" does not exists, skipping
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 7fd739d6677..60f78671e3b 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -64,3 +64,9 @@ DISCARD TEMP;
-- should be ok
CREATE TEMP VARIABLE x AS int;
+
+-- should be ok
+CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
+
+DROP VARIABLE x;
+DROP VARIABLE IF EXISTS x;
--
2.53.0
[text/x-patch] v20260212-0007-DISCARD-TEMP.patch (4.3K, 7-v20260212-0007-DISCARD-TEMP.patch)
download | inline diff:
From 39135e8f65b16dae24cee97752182b9bf281ca49 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 24 Nov 2025 20:04:16 +0100
Subject: [PATCH 07/11] DISCARD TEMP
---
doc/src/sgml/ref/discard.sgml | 3 ++-
src/backend/commands/discard.c | 3 +++
src/backend/commands/session_variable.c | 20 +++++++++++++++++++
src/include/commands/session_variable.h | 2 ++
.../expected/session_variables_ddl.out | 7 +++++++
.../regress/sql/session_variables_ddl.sql | 10 ++++++++++
6 files changed, 44 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index bf44c523cac..2700f7b7cd0 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -70,7 +70,8 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
<term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
<listitem>
<para>
- Drops all temporary tables created in the current session.
+ Drops all temporary tables and temporary session variables created in
+ the current session.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c
index 7b5520b9abe..de6d18fcc5e 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 bfc4191d954..5cc19216f6e 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -409,3 +409,23 @@ ExecuteLetStmt(ParseState *pstate,
PopActiveSnapshot();
}
+
+/*
+ * Fast drop of the complete content of the session variables hash table, and
+ * cleanup of any list that wouldn't be relevant anymore.
+ * This is used by the DISCARD TEMP.
+ */
+void
+ResetSessionVariables(void)
+{
+ /* destroy hash table and reset related memory context */
+ if (sessionvars)
+ {
+ hash_destroy(sessionvars);
+ sessionvars = NULL;
+ }
+
+ /* release memory allocated by session variables */
+ if (SVariableMemoryContext != NULL)
+ MemoryContextReset(SVariableMemoryContext);
+}
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index c4b4d9e6832..cc1aa7ce23b 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -37,4 +37,6 @@ extern void get_session_variable_type_typmod_collid(char *varname,
extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo params,
QueryEnvironment *queryEnv, QueryCompletion *qc);
+extern void ResetSessionVariables(void);
+
#endif
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index 45c2d27ab44..c36febd894e 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -41,3 +41,10 @@ DROP VARIABLE x;
SET ROLE TO DEFAULT;
DROP ROLE regress_session_variable_test_role_01;
DROP ROLE regress_session_variable_test_role_02;
+CREATE TEMP VARIABLE x AS int;
+-- should fail
+CREATE TEMP VARIABLE x AS int;
+ERROR: session variable "x" already exists
+DISCARD TEMP;
+-- should be ok
+CREATE TEMP VARIABLE x AS int;
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 34f34dd898f..7fd739d6677 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -54,3 +54,13 @@ DROP VARIABLE x;
SET ROLE TO DEFAULT;
DROP ROLE regress_session_variable_test_role_01;
DROP ROLE regress_session_variable_test_role_02;
+
+CREATE TEMP VARIABLE x AS int;
+
+-- should fail
+CREATE TEMP VARIABLE x AS int;
+
+DISCARD TEMP;
+
+-- should be ok
+CREATE TEMP VARIABLE x AS int;
--
2.53.0
[text/x-patch] v20260212-0005-svariableReceiver.patch (10.8K, 8-v20260212-0005-svariableReceiver.patch)
download | inline diff:
From db5f7bd330be4a46e9c49338799bf3573c485610 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sun, 1 Jun 2025 21:20:16 +0200
Subject: [PATCH 05/11] svariableReceiver
allows to store result of the query to session variable
Check correct format of result - one column, one row.
---
src/backend/commands/session_variable.c | 50 ++++++++
src/backend/executor/Makefile | 1 +
src/backend/executor/meson.build | 1 +
src/backend/executor/svariableReceiver.c | 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 f8ea8526e1d..deb5d7e80f9 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -166,6 +166,56 @@ GetSessionVariableWithTypecheck(char *varname,
return result;
}
+/*
+ * Store the given value in a session variable in the cache.
+ */
+void
+SetSessionVariableWithTypecheck(char *varname,
+ Oid typid, int32 typmod,
+ Datum value, bool isnull)
+{
+ SVariable svar;
+
+ svar = search_variable(varname);
+
+ if (svar->vartype != typid || svar->vartypmod != typmod)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("session variable %s is not of a type %s but type %s",
+ varname,
+ format_type_with_typemod(typid, typmod),
+ format_type_with_typemod(svar->vartype, svar->vartypmod))));
+
+ /* only owner can set content of variable */
+ if (svar->varowner != GetUserId() && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for session variable %s",
+ varname)));
+
+ if (!svar->typbyval)
+ {
+ if (!isnull)
+ {
+ MemoryContext oldcxt;
+
+ /*
+ * Do copy of value in session variables context. This operation
+ * can fail, so do it before releasing the old content.
+ */
+ oldcxt = MemoryContextSwitchTo(SVariableMemoryContext);
+ value = datumCopy(value, svar->typbyval, svar->typlen);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ if (!svar->isnull)
+ pfree(DatumGetPointer(svar->value));
+ }
+
+ svar->value = value;
+ svar->isnull = isnull;
+}
+
/*
* Creates a new variable - does new entry in sessionvars
*
diff --git a/src/backend/executor/Makefile b/src/backend/executor/Makefile
index 11118d0ce02..71248a34f26 100644
--- a/src/backend/executor/Makefile
+++ b/src/backend/executor/Makefile
@@ -76,6 +76,7 @@ OBJS = \
nodeWindowAgg.o \
nodeWorktablescan.o \
spi.o \
+ svariableReceiver.o \
tqueue.o \
tstoreReceiver.o
diff --git a/src/backend/executor/meson.build b/src/backend/executor/meson.build
index dc45be0b2ce..a572b6dab7c 100644
--- a/src/backend/executor/meson.build
+++ b/src/backend/executor/meson.build
@@ -64,6 +64,7 @@ backend_sources += files(
'nodeWindowAgg.c',
'nodeWorktablescan.c',
'spi.c',
+ 'svariableReceiver.c',
'tqueue.c',
'tstoreReceiver.c',
)
diff --git a/src/backend/executor/svariableReceiver.c b/src/backend/executor/svariableReceiver.c
new file mode 100644
index 00000000000..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 931cc563ba6..b72f8769b30 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 4e4f532d8cc..1e7043dc7f7 100644
--- a/src/include/tcop/dest.h
+++ b/src/include/tcop/dest.h
@@ -97,6 +97,7 @@ typedef enum
DestTransientRel, /* results sent to transient relation */
DestTupleQueue, /* results sent to tuple queue */
DestExplainSerialize, /* results are serialized and discarded */
+ DestVariable, /* results sent to session variable */
} CommandDest;
/* ----------------
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 2adea269fc5..6f80c130798 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2702,6 +2702,7 @@ STRLEN
SV
SVariableData
SVariable
+SVariableState
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
SampleScan
--
2.53.0
[text/x-patch] v20260212-0004-fill-an-auxiliary-buffer-with-values-of-session-vari.patch (14.3K, 9-v20260212-0004-fill-an-auxiliary-buffer-with-values-of-session-vari.patch)
download | inline diff:
From 905e3a126f019f6886142859f89d6e653df268fb Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Sat, 22 Nov 2025 06:40:46 +0100
Subject: [PATCH 04/11] fill an auxiliary buffer with values of session
variables used in query
and locks variables used in query. Now we can read the content of any
session variable. Direct reading from expression executor is not allowed,
so we cannot to use session variables inside CALL or EXECUTE commands
(can be supported with direct access to session variables (from expression
executor) - postponed).
Using session variables blocks parallel query execution. It is not
technical problem (it just needs a serialization/deserialization of
es_session_varibles buffer), but it increases a size of patch (and then
it is postponed).
---
src/backend/executor/execExpr.c | 29 ++++
src/backend/executor/execMain.c | 49 +++++++
src/include/nodes/execnodes.h | 14 ++
.../expected/session_variables_dml.out | 135 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +
.../regress/sql/session_variables_dml.sql | 120 ++++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
7 files changed, 353 insertions(+)
create mode 100644 src/test/regress/expected/session_variables_dml.out
create mode 100644 src/test/regress/sql/session_variables_dml.sql
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 088eca24021..e722c723649 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 bfd3ebc601e..a80be1388ba 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 = palloc_array(SessionVariableValue,
+ nSessionVariables);
+
+ /* fill the array */
+ foreach_node(Param, param, queryDesc->plannedstmt->sessionVariables)
+ {
+ estate->es_session_variables[i].value =
+ GetSessionVariableWithTypecheck(param->paramvarname,
+ param->paramtype,
+ param->paramtypmod,
+ &estate->es_session_variables[i].isnull);
+
+ i++;
+ }
+
+ estate->es_num_session_variables = nSessionVariables;
+ }
+
/*
* Fill in the query environment, if any, from queryDesc.
*/
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 63c067d5aae..b3af6032c92 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -648,6 +648,16 @@ typedef struct AsyncRequest
* tuples) */
} AsyncRequest;
+/* ----------------
+ * SessionVariableValue
+ * ----------------
+ */
+typedef struct SessionVariableValue
+{
+ bool isnull;
+ Datum value;
+} SessionVariableValue;
+
/* ----------------
* EState information
*
@@ -707,6 +717,10 @@ typedef struct EState
ParamListInfo es_param_list_info; /* values of external params */
ParamExecData *es_param_exec_vals; /* values of internal params */
+ /* Session variables info: */
+ int es_num_session_variables; /* number of used variables */
+ SessionVariableValue *es_session_variables; /* array of copies of values */
+
QueryEnvironment *es_queryEnv; /* query environment */
/* Other working state: */
diff --git a/src/test/regress/expected/session_variables_dml.out b/src/test/regress/expected/session_variables_dml.out
new file mode 100644
index 00000000000..1519bf723e0
--- /dev/null
+++ b/src/test/regress/expected/session_variables_dml.out
@@ -0,0 +1,135 @@
+CREATE TEMP VARIABLE temp_var01 AS int;
+-- should not be accessible without variable's fence
+-- should fail
+SELECT temp_var01;
+ERROR: column "temp_var01" does not exist
+LINE 1: SELECT temp_var01;
+ ^
+-- should be ok
+SELECT VARIABLE(temp_var01);
+ temp_var01
+------------
+
+(1 row)
+
+-- should not crash
+DO $$
+BEGIN
+ RAISE NOTICE '%', VARIABLE(temp_var01);
+END;
+$$;
+NOTICE: <NULL>
+-- variables cannot be used by persistent objects
+-- that checks dependency
+-- should fail
+CREATE TEMP VIEW tempv AS SELECT VARIABLE(temp_var01);
+ERROR: session variable "temp_var01" cannot be referenced in a catalog object
+CREATE OR REPLACE FUNCTION testvar_sql()
+RETURNS int AS $$
+SELECT VARIABLE(temp_var01);
+$$ LANGUAGE sql;
+SELECT testvar_sql();
+ testvar_sql
+-------------
+
+(1 row)
+
+-- session variable cannot be used as parameter of CALL or EXECUTE
+CREATE OR REPLACE PROCEDURE testvar_proc(int)
+AS $$
+BEGIN
+ RAISE NOTICE '%', $1;
+END;
+$$ LANGUAGE plpgsql;
+-- should not crash
+CALL testvar_proc(VARIABLE(temp_var01));
+ERROR: session variable reference is not supported here
+LINE 1: CALL testvar_proc(VARIABLE(temp_var01));
+ ^
+PREPARE prepstmt(int) AS SELECT $1;
+-- should not crash
+EXECUTE prepstmt(VARIABLE(temp_var01));
+ERROR: session variable reference is not supported here
+LINE 1: EXECUTE prepstmt(VARIABLE(temp_var01));
+ ^
+DROP PROCEDURE testvar_proc;
+DEALLOCATE prepstmt;
+CREATE ROLE regress_session_variable_test_role_03;
+CREATE OR REPLACE FUNCTION testvar_sd()
+RETURNS void AS $$
+BEGIN
+ RAISE NOTICE '%', VARIABLE(temp_var01);
+END;
+$$ LANGUAGE plpgsql;
+-- only owner can read data
+SET ROLE TO regress_session_variable_test_role_03;
+-- should fail
+SELECT VARIABLE(temp_var01);
+ERROR: permission denied for session variable temp_var01
+-- fx with security definer should be ok
+SELECT testvar_sd();
+ERROR: permission denied for session variable temp_var01
+CONTEXT: PL/pgSQL expression "VARIABLE(temp_var01)"
+PL/pgSQL function testvar_sd() line 3 at RAISE
+SET ROLE TO default;
+DROP VARIABLE temp_var01;
+-- there is not plan cache invalidation
+-- but still functions that uses dropped variables
+-- should not to crash
+SELECT testvar_sd();
+ERROR: session variable "temp_var01" doesn't exist
+CONTEXT: PL/pgSQL expression "VARIABLE(temp_var01)"
+PL/pgSQL function testvar_sd() line 3 at RAISE
+SELECT testvar_sql();
+ERROR: session variable "temp_var01" doesn't exist
+CONTEXT: SQL function "testvar_sql" during inlining
+DROP FUNCTION testvar_sql();
+DROP FUNCTION testvar_sd();
+DROP ROLE regress_session_variable_test_role_03;
+CREATE TABLE testvar_testtab(a int);
+CREATE TEMP VARIABLE temp_var02 AS int;
+INSERT INTO testvar_testtab SELECT * FROM generate_series(1,1000);
+CREATE INDEX testvar_testtab_a ON testvar_testtab(a);
+ANALYZE testvar_testtab;
+-- force index
+SET enable_seqscan TO OFF;
+-- index scan should be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = VARIABLE(temp_var02);
+ QUERY PLAN
+------------------------------------------------------------
+ Index Only Scan using testvar_testtab_a on testvar_testtab
+ Index Cond: (a = VARIABLE(temp_var02))
+(2 rows)
+
+DROP INDEX testvar_testtab_a;
+SET enable_seqscan TO DEFAULT;
+-- parallel execution should be blocked
+-- Encourage use of parallel plans
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+SET min_parallel_table_scan_size = 0;
+SET max_parallel_workers_per_gather = 2;
+-- parallel plan should be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = 100;
+ QUERY PLAN
+--------------------------------------------
+ Gather
+ Workers Planned: 2
+ -> Parallel Seq Scan on testvar_testtab
+ Filter: (a = 100)
+(4 rows)
+
+-- parallel plan should not be used
+EXPLAIN (COSTS OFF) SELECT * FROM testvar_testtab WHERE a = VARIABLE(temp_var02);
+ QUERY PLAN
+--------------------------------------
+ Seq Scan on testvar_testtab
+ Filter: (a = VARIABLE(temp_var02))
+(2 rows)
+
+RESET parallel_setup_cost;
+RESET parallel_tuple_cost;
+RESET min_parallel_table_scan_size;
+RESET max_parallel_workers_per_gather;
+DROP TABLE testvar_testtab;
+DROP VARIABLE temp_var02;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8f9988954f9..f889ac26b08 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 5d38d12b7c4..2adea269fc5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2760,6 +2760,7 @@ SerializedTransactionState
Session
SessionBackupState
SessionEndType
+SessionVariableValue
SetConstraintState
SetConstraintStateData
SetConstraintTriggerData
--
2.53.0
[text/x-patch] v20260212-0006-LET-command-assign-a-result-of-expression-to-the-ses.patch (39.0K, 10-v20260212-0006-LET-command-assign-a-result-of-expression-to-the-ses.patch)
download | inline diff:
From b1af6cf1f7e2cc5e5c42a7f2abfae331cdc1a6c0 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Mon, 24 Nov 2025 18:05:03 +0100
Subject: [PATCH 06/11] LET command - assign a result of expression to the
session variable
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
The value is assigned to session variables usually by SET command. Unfortunately
there are two reasons why SET should not be used for this purpose in Postgres.
1. Using a_expr inside generic_set ram rule produces reduce conflicts, so it needs
  total reimplementation of related gram rules.
2. SET is no plan command - so it doesn't support usage of parameters.
3. Excepting implementation issues, there is fact, so if we use SET command
  for assigning values to session variables, then there can be collisions
  between session variables and GUC, and then we need some concepts, how
  these collisions should be solved, or how to protect self against these
  collisions. With the dedicated command, the collisions between GUC and session
  variables are not possible.
The command LET is executed as usual query execution. The result is stored
to the target session variable (resultVariable) by using VariableDestReceiver.
Implementations of EXPLAIN LET and PREPARE LET statements are not supported
now. Postponed to next step due reducing patch size.
---
doc/src/sgml/ddl.sgml | 22 ++++
doc/src/sgml/ref/allfiles.sgml | 1 +
doc/src/sgml/ref/create_variable.sgml | 3 +
doc/src/sgml/ref/drop_variable.sgml | 1 +
doc/src/sgml/ref/let.sgml | 95 ++++++++++++++
doc/src/sgml/reference.sgml | 1 +
src/backend/commands/session_variable.c | 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, 619 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 8c0ae715869..19d2afc2201 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5717,10 +5717,32 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
<literal>VARIABLE(varname)</literal> syntax. This avoids any risk of
collision between variable names and column names.
</para>
+
+ <para>
+ You set the value of a session variable with the <command>LET</command>
+ statement and retrieve it with <command>SELECT</command>:
+<programlisting>
+CREATE TEMP VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT VARIABLE(var1);
+</programlisting>
+
+ or
+
<programlisting>
+CREATE TEMP VARIABLE current_user_id AS integer;
+LET current_user_id = (SELECT id FROM users WHERE usename = session_user);
SELECT VARIABLE(current_user_id);
</programlisting>
</para>
+
+ <para>
+ By default, retrieving a session variable returns
+ <literal>NULL</literal> unless it has been set in the current session
+ using the <command>LET</command> command. Session variables are not
+ transactional: changes to their values persist even if the transaction
+ is rolled back, similar to variables in procedural languages
+ </para>
</sect1>
<sect1 id="ddl-others">
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 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 4e8c1940252..1315b1248c7 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -108,6 +108,8 @@ CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replac
Create an date session variable <literal>var1</literal>:
<programlisting>
CREATE TEMPORARY VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT VARIABLE(var1);
</programlisting>
</para>
@@ -127,6 +129,7 @@ CREATE TEMPORARY VARIABLE var1 AS date;
<simplelist type="inline">
<member><xref linkend="sql-dropvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
</simplelist>
</refsect1>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
index e8517a78200..dede42e4ffb 100644
--- a/doc/src/sgml/ref/drop_variable.sgml
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -78,6 +78,7 @@ DROP VARIABLE var1;
<simplelist type="inline">
<member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
</simplelist>
</refsect1>
diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml
new file mode 100644
index 00000000000..33ee42d3f20
--- /dev/null
+++ b/doc/src/sgml/ref/let.sgml
@@ -0,0 +1,95 @@
+<!--
+doc/src/sgml/ref/let.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-let">
+ <indexterm zone="sql-let">
+ <primary>LET</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>changing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>LET</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>LET</refname>
+ <refpurpose>change a session variable's value</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+LET <replaceable class="parameter">session_variable</replaceable> = <replaceable class="parameter">sql_expression</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>LET</command> command assigns a value to the specified session
+ variable.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">session_variable</replaceable></term>
+ <listitem>
+ <para>
+ The name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">sql_expression</replaceable></term>
+ <listitem>
+ <para>
+ An arbitrary SQL expression. The result must be of a data type that can
+ be cast to the type of the session variable in an assignment.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+<programlisting>
+CREATE TEMPORARY VARIABLE myvar AS integer;
+LET myvar = 10;
+LET myvar = (SELECT sum(val) FROM tab);
+</programlisting>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>LET</command> is a <productname>PostgreSQL</productname>
+ extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 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 deb5d7e80f9..bfc4191d954 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -16,13 +16,18 @@
#include "catalog/pg_language.h"
#include "commands/session_variable.h"
+#include "executor/executor.h"
+#include "executor/svariableReceiver.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
+#include "rewrite/rewriteHandler.h"
#include "storage/proc.h"
+#include "tcop/tcopprot.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/snapmgr.h"
/*
* The session variables are stored in the backend's private memory (data,
@@ -323,3 +328,84 @@ DropVariableByName(char *varname)
NULL) == NULL)
elog(ERROR, "hash table corrupted");
}
+
+/*
+ * Assign the result of the evaluated expression to the session variable
+ */
+void
+ExecuteLetStmt(ParseState *pstate,
+ LetStmt *stmt,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv,
+ QueryCompletion *qc)
+{
+ Query *query = castNode(Query, stmt->query);
+ List *rewritten;
+ DestReceiver *dest;
+ PlannedStmt *plan;
+ QueryDesc *queryDesc;
+ char *varname = query->resultVariable;
+ SVariable svar;
+
+ svar = search_variable(varname);
+
+ /* only owner can set content of variable */
+ if (svar->varowner != GetUserId() && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for session variable %s",
+ varname)));
+
+ /* create a dest receiver for LET */
+ dest = CreateVariableDestReceiver(varname);
+
+ /* run the query rewriter */
+ query = copyObject(query);
+
+ rewritten = QueryRewrite(query);
+
+ Assert(list_length(rewritten) == 1);
+
+ query = linitial_node(Query, rewritten);
+ Assert(query->commandType == CMD_SELECT);
+
+ /* plan the query */
+ plan = pg_plan_query(query, pstate->p_sourcetext,
+ CURSOR_OPT_PARALLEL_OK, params, NULL);
+
+ /*
+ * Use a snapshot with an updated command ID to ensure this query sees the
+ * results of any previously executed queries. (This could only matter if
+ * the planner executed an allegedly-stable function that changed the
+ * database contents, but let's do it anyway to be parallel to the EXPLAIN
+ * code path.)
+ */
+ PushCopiedSnapshot(GetActiveSnapshot());
+ UpdateActiveSnapshotCommandId();
+
+ /* create a QueryDesc, redirecting output to our tuple receiver */
+ queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext,
+ GetActiveSnapshot(), InvalidSnapshot,
+ dest, params, queryEnv, 0);
+
+ /* call ExecutorStart to prepare the plan for execution */
+ ExecutorStart(queryDesc, 0);
+
+ /*
+ * Run the plan to completion. The result should be only one row. To
+ * check if there are too many result rows, we try to fetch two.
+ */
+ ExecutorRun(queryDesc, ForwardScanDirection, 2L);
+
+ /* save the rowcount if we're given a QueryCompletion to fill */
+ if (qc)
+ SetQueryCompletion(qc, CMDTAG_LET, queryDesc->estate->es_processed);
+
+ /* and clean up */
+ ExecutorFinish(queryDesc);
+ ExecutorEnd(queryDesc);
+
+ FreeQueryDesc(queryDesc);
+
+ PopActiveSnapshot();
+}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 4e261a49128..de1e1c6421f 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4377,6 +4377,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 f82bebf6a83..6e64a68b9a0 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 e8ed16bb6fd..f883ffba5a4 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:
@@ -1400,7 +1413,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.
@@ -1453,8 +1466,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);
@@ -2850,9 +2863,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
@@ -2875,18 +2890,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;
@@ -2921,7 +2939,7 @@ transformPLAssignStmtTarget(ParseState *pstate, List *tlist,
tle->expr = (Expr *)
transformAssignmentIndirection(pstate,
target,
- stmt->name,
+ target_name,
false,
targettype,
targettypmod,
@@ -2929,10 +2947,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)))
{
@@ -2955,7 +2973,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 */
@@ -2964,7 +2982,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."),
@@ -3332,6 +3350,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 47d9c66b2b0..8fa3af60881 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -302,7 +302,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
@@ -750,7 +750,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
@@ -1098,6 +1098,7 @@ stmt:
| ImportForeignSchemaStmt
| IndexStmt
| InsertStmt
+ | LetStmt
| ListenStmt
| RefreshMatViewStmt
| LoadStmt
@@ -13002,6 +13003,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:
@@ -18112,6 +18144,7 @@ unreserved_keyword:
| LARGE_P
| LAST_P
| LEAKPROOF
+ | LET
| LEVEL
| LISTEN
| LOAD
@@ -18730,6 +18763,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 25ee0f87d93..2d7c40f7bbb 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 aa110e80b06..2a75e6ee6c4 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -592,6 +592,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
@@ -962,6 +965,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;
@@ -1990,6 +1994,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
@@ -3349,6 +3356,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 24f6745923b..2e63a56bee1 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 41a7edfe53d..063759df396 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,
@@ -2220,6 +2226,10 @@ UtilityContainsQuery(Node *parsetree)
return UtilityContainsQuery(qry->utilityStmt);
return qry;
+ case T_LetStmt:
+ qry = castNode(Query, ((LetStmt *) parsetree)->query);
+ return qry;
+
default:
return NULL;
}
@@ -2415,6 +2425,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:
{
@@ -3305,6 +3319,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 b829eed2ab0..94311183636 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",
@@ -4844,6 +4844,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 b7fa522e57f..ba4516f5f2e 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 */
@@ -2199,6 +2202,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 7acc9e51f0e..8407f8affe1 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -274,6 +274,9 @@ typedef struct PlannerGlobal
/* list of used session variables */
List *sessionVariables;
+
+ /* name of session variable used like target of LET command */
+ char *resultVariable;
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index d4b1223a33e..ca61e18d8ab 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 278598a90f7..3aa874e4910 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 4b964d0fb5a..945a3a0f24b 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 1519bf723e0..b87967bd7d5 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 6f80c130798..66ddea98c22 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1569,6 +1569,7 @@ LargeObjectDesc
Latch
LauncherLastStartTimesEntry
LerpFunc
+LetStmt
LexDescr
LexemeEntry
LexemeHashKey
--
2.53.0
[text/x-patch] v20260212-0003-collect-session-variables-used-in-plan-and-assign-pa.patch (15.9K, 11-v20260212-0003-collect-session-variables-used-in-plan-and-assign-pa.patch)
download | inline diff:
From 3552042a99f2c6960ce2d734fab64efa2b8843c9 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Fri, 21 Nov 2025 20:42:56 +0100
Subject: [PATCH 03/11] collect session variables used in plan and assign
paramid
In the plan stage we need to collect used session variables. On the
order of this list, the param nodes gets paramid (fix_param_node).
This number is used (later) as index to buffer of values of the
used session variables. The buffer is prepared and filled by executor.
Some unsupported optimizations are disabled:
* parallel execution
* simple expression execution in PL/pgSQL
* SQL functions inlining
Before execution of query with session variables we need to collect
used session variables. This list is used for loading variables to
executed query.
plan
---
doc/src/sgml/parallel.sgml | 6 ++
src/backend/catalog/dependency.c | 10 +++
src/backend/commands/session_variable.c | 39 ++++++++++
src/backend/optimizer/plan/planner.c | 11 +++
src/backend/optimizer/plan/setrefs.c | 94 ++++++++++++++++++++++-
src/backend/optimizer/prep/prepjointree.c | 3 +
src/backend/optimizer/util/clauses.c | 35 ++++++++-
src/backend/utils/fmgr/fmgr.c | 10 ++-
src/include/commands/session_variable.h | 2 +
src/include/nodes/pathnodes.h | 5 ++
src/include/nodes/plannodes.h | 3 +
src/include/optimizer/planmain.h | 2 +
12 files changed, 214 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/parallel.sgml b/doc/src/sgml/parallel.sgml
index af43484703e..843e2c3f663 100644
--- a/doc/src/sgml/parallel.sgml
+++ b/doc/src/sgml/parallel.sgml
@@ -524,6 +524,12 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
Plan nodes that reference a correlated <literal>SubPlan</literal>.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ Usage of a session variable.
+ </para>
+ </listitem>
</itemizedlist>
<sect2 id="parallel-labeling">
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index f89267f0342..a3e0a477094 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1951,6 +1951,16 @@ find_expr_references_walker(Node *node,
{
Param *param = (Param *) node;
+ /*
+ * catalog less session variable variable cannot be used in persistent
+ * catalog based object.
+ */
+ if (param->paramkind == PARAM_VARIABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("session variable \"%s\" cannot be referenced in a catalog object",
+ param->paramvarname)));
+
/* A parameter must depend on the parameter's datatype */
add_object_address(TypeRelationId, param->paramtype, 0,
context->addrs);
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 75c62baeca2..f8ea8526e1d 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -127,6 +127,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 006b3281969..f82bebf6a83 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
@@ -667,6 +668,9 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
/* utilityStmt should be null, but we might as well copy it */
result->utilityStmt = parse->utilityStmt;
result->elidedNodes = glob->elidedNodes;
+
+ result->sessionVariables = glob->sessionVariables;
+
result->stmt_location = parse->stmt_location;
result->stmt_len = parse->stmt_len;
@@ -855,6 +859,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 1b5b9b5ed9c..c98d96c8692 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -210,6 +210,8 @@ static List *set_returning_clause_references(PlannerInfo *root,
static List *set_windowagg_runcondition_references(PlannerInfo *root,
List *runcondition,
Plan *plan);
+static bool pull_up_has_session_variables_walker(Node *node,
+ PlannerInfo *root);
static void record_elided_node(PlannerGlobal *glob, int plan_node_id,
NodeTag elided_type, Bitmapset *relids);
@@ -1365,6 +1367,50 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
return plan;
}
+/*
+ * Search usage of session variables in subqueries
+ */
+void
+pull_up_has_session_variables(PlannerInfo *root)
+{
+ Query *query = root->parse;
+
+ if (query->hasSessionVariables)
+ {
+ root->hasSessionVariables = true;
+ }
+ else
+ {
+ (void) query_tree_walker(query,
+ pull_up_has_session_variables_walker,
+ (void *) root, 0);
+ }
+}
+
+static bool
+pull_up_has_session_variables_walker(Node *node, PlannerInfo *root)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Query))
+ {
+ Query *query = (Query *) node;
+
+ if (query->hasSessionVariables)
+ {
+ root->hasSessionVariables = true;
+ return false;
+ }
+
+ /* recurse into subselects */
+ return query_tree_walker((Query *) node,
+ pull_up_has_session_variables_walker,
+ (void *) root, 0);
+ }
+ return expression_tree_walker(node, pull_up_has_session_variables_walker,
+ (void *) root);
+}
+
/*
* set_indexonlyscan_references
* Do set_plan_references processing on an IndexOnlyScan
@@ -2192,6 +2238,10 @@ fix_expr_common(PlannerInfo *root, Node *node)
* If it's a PARAM_MULTIEXPR, replace it with the appropriate Param from
* root->multiexpr_params; otherwise no change is needed.
* Just for paranoia's sake, we make a copy of the node in either case.
+ *
+ * If it's a PARAM_VARIABLE, then we collect used session variables in
+ * the list root->glob->sessionVariable. Also, assign the parameter's
+ * "paramid" to the parameter's position in that list.
*/
static Node *
fix_param_node(PlannerInfo *root, Param *p)
@@ -2210,6 +2260,43 @@ fix_param_node(PlannerInfo *root, Param *p)
elog(ERROR, "unexpected PARAM_MULTIEXPR ID: %d", p->paramid);
return copyObject(list_nth(params, colno - 1));
}
+
+ if (p->paramkind == PARAM_VARIABLE)
+ {
+ int n = 0;
+
+ /* we will modify object */
+ p = (Param *) copyObject(p);
+
+ /*
+ * Now, we can actualize list of session variables, and we can
+ * complete paramid parameter.
+ */
+ foreach_node(Param, paramvar, root->glob->sessionVariables)
+ {
+ if (strcmp(paramvar->paramvarname, p->paramvarname) == 0)
+ {
+ p->paramid = paramvar->paramid;
+
+ return (Node *) p;
+ }
+
+ n += 1;
+ }
+
+ p->paramid = n;
+
+ /*
+ * Because session variables are catalogless, we cannot to use plan
+ * invalidation. Then we need to check type, typmod, collid any time,
+ * when we load values of session variables to parameter's buffer.
+ * For this purpose it is more easy to save complete Param node.
+ */
+ root->glob->sessionVariables = lappend(root->glob->sessionVariables, p);
+
+ return (Node *) p;
+ }
+
return (Node *) copyObject(p);
}
@@ -2271,7 +2358,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
@@ -2293,7 +2382,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 c90f4b32733..2fc2b5b3897 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1653,6 +1653,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 504a30d8836..36f1abfccd1 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"
@@ -944,6 +945,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))
{
@@ -2402,6 +2410,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 *
@@ -2528,6 +2537,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
@@ -5141,7 +5173,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 4e26df7c63a..83bc57a4d79 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 c175ee95b68..7acc9e51f0e 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -271,6 +271,9 @@ typedef struct PlannerGlobal
/* extension state */
void **extension_state pg_node_attr(read_write_ignore);
int extension_state_allocated;
+
+ /* list of used session variables */
+ List *sessionVariables;
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
@@ -629,6 +632,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 8c9321aab8c..d99d84e6db9 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -164,6 +164,9 @@ typedef struct PlannedStmt
*/
List *extension_state;
+ /* PARAM_VARIABLE Params */
+ List *sessionVariables;
+
/* statement location in source string (copied from Query) */
/* start location, or -1 if unknown */
ParseLoc stmt_location;
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index d0dc3761b13..46069ef2e9e 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -132,4 +132,6 @@ extern void record_plan_function_dependency(PlannerInfo *root, Oid funcid);
extern void record_plan_type_dependency(PlannerInfo *root, Oid typid);
extern bool extract_query_dependencies_walker(Node *node, PlannerInfo *context);
+extern void pull_up_has_session_variables(PlannerInfo *root);
+
#endif /* PLANMAIN_H */
--
2.53.0
[text/x-patch] v20260212-0002-parsing-session-variable-fences.patch (18.4K, 12-v20260212-0002-parsing-session-variable-fences.patch)
download | inline diff:
From 9b47c47a901a5fed70669007472feb944f1ce68f Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Fri, 21 Nov 2025 15:28:59 +0100
Subject: [PATCH 02/11] parsing session variable fences
The session variables can be used in query only inside the variable fence.
This is special syntax VARIABLE(varname), that eliminates a risk of
collision between variable and column identifier.
The session variables cannot be used as parameters of CALL or EXECUTE
commands. These commands evaluates arguments by direct call of expression
executor, and direct access to session variables from expression executor
will be implemented later (in next step)
---
doc/src/sgml/ddl.sgml | 10 ++
src/backend/commands/prepare.c | 8 ++
src/backend/commands/session_variable.c | 21 +++++
src/backend/nodes/nodeFuncs.c | 6 ++
src/backend/parser/analyze.c | 7 ++
src/backend/parser/gram.y | 17 +++-
src/backend/parser/parse_expr.c | 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, 228 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 35a6b322cd9..8c0ae715869 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5711,6 +5711,16 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
variable is stored in session memory and is private to each session. It is
automatically released when the session ends.
</para>
+
+ <para>
+ In a query, a session variable can only be referenced using the special
+ <literal>VARIABLE(varname)</literal> syntax. This avoids any risk of
+ collision between variable names and column names.
+ </para>
+<programlisting>
+SELECT VARIABLE(current_user_id);
+</programlisting>
+ </para>
</sect1>
<sect1 id="ddl-others">
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 5b86a727587..452b2498716 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 cd61df4a370..75c62baeca2 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -106,6 +106,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 199ed27995f..4e261a49128 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;
@@ -4704,6 +4707,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 539c16c4f79..e8ed16bb6fd 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);
@@ -1058,6 +1059,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);
@@ -1537,6 +1539,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)
{
@@ -1763,6 +1766,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);
@@ -2014,6 +2018,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)
{
@@ -2486,6 +2491,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);
@@ -2552,6 +2558,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 e968260ec89..47d9c66b2b0 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -532,7 +532,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
@@ -890,7 +890,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 '*' '/' '%'
@@ -15794,6 +15794,8 @@ c_expr: columnref { $$ = $1; }
else
$$ = $2;
}
+ | variable_fence
+ { $$ = $1; }
| case_expr
{ $$ = $1; }
| func_expr
@@ -17196,6 +17198,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 dcfe1acc4c3..aa110e80b06 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"
@@ -78,6 +79,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,
@@ -372,6 +374,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));
@@ -905,6 +911,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 0a70d48fd4c..12f89aa2a5c 100644
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -400,6 +400,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
qry->hasTargetSRFs = false;
qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasSessionVariables = pstate->p_hasSessionVariables;
assign_query_collations(pstate, qry);
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index dbf5b2b5c01..62745d309ac 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -2035,6 +2035,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 89cbdd3b1e7..f896610769d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8835,6 +8835,14 @@ get_parameter(Param *param, deparse_context *context)
}
}
+ /* Note: can be be used by EXPLAIN */
+ if (param->paramkind == PARAM_VARIABLE)
+ {
+ appendStringInfo(context->buf, "VARIABLE(%s)",
+ quote_identifier(param->paramvarname));
+ return;
+ }
+
/*
* Not PARAM_EXEC, or couldn't find referent: just print $N.
*
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 1ed40d87a38..96be968c3d4 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -22,4 +22,9 @@
extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
extern void DropVariableByName(char *varname);
+extern void get_session_variable_type_typmod_collid(char *varname,
+ Oid *typid,
+ int32 *typmod,
+ Oid *collid);
+
#endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2102aafe76c..b7fa522e57f 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 384df50c80a..9b2a277e9f6 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -379,6 +379,8 @@ typedef struct Const
* of the `paramid' field contain the SubLink's subLinkId, and
* the low-order 16 bits contain the column number. (This type
* of Param is also converted to PARAM_EXEC during planning.)
+ * PARAM_VARIABLE: The parameter is a reference to a session variable
+ * (paramvarname holds the variable's name).
*/
typedef enum ParamKind
{
@@ -386,6 +388,7 @@ typedef enum ParamKind
PARAM_EXEC,
PARAM_SUBLINK,
PARAM_MULTIEXPR,
+ PARAM_VARIABLE,
} ParamKind;
typedef struct Param
@@ -400,6 +403,8 @@ typedef struct Param
int32 paramtypmod;
/* OID of collation, or InvalidOid if none */
Oid paramcollid;
+ /* OID of used session variable or InvalidOid if none */
+ char *paramvarname pg_node_attr(query_jumble_ignore);
/* token location, or -1 if unknown */
ParseLoc location;
} Param;
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f23e21f318b..278598a90f7 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -223,6 +223,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 723048ab833..4e4f1198e82 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8253,7 +8253,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 c17bf76a6ea..5d38d12b7c4 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3263,6 +3263,7 @@ ValidatorValidateCB
ValuesScan
ValuesScanState
Var
+VariableFence
VarBit
VarChar
VarParamState
--
2.53.0
[text/x-patch] v20260212-0001-CREATE-VARIABLE-DROP-VARIABLE.patch (33.1K, 13-v20260212-0001-CREATE-VARIABLE-DROP-VARIABLE.patch)
download | inline diff:
From 87120ee59e4c1a3079cd5dc1645c58c067a5a6c7 Mon Sep 17 00:00:00 2001
From: "[email protected]" <[email protected]>
Date: Wed, 19 Nov 2025 19:36:07 +0100
Subject: [PATCH 01/11] CREATE VARIABLE, DROP VARIABLE
---
doc/src/sgml/ddl.sgml | 20 ++
doc/src/sgml/glossary.sgml | 15 ++
doc/src/sgml/ref/allfiles.sgml | 2 +
doc/src/sgml/ref/create_variable.sgml | 133 +++++++++++
doc/src/sgml/ref/drop_variable.sgml | 84 +++++++
doc/src/sgml/reference.sgml | 2 +
src/backend/commands/Makefile | 1 +
src/backend/commands/dropcmds.c | 1 +
src/backend/commands/meson.build | 1 +
src/backend/commands/session_variable.c | 215 ++++++++++++++++++
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, 714 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 9070aaa5a7c..35a6b322cd9 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5693,6 +5693,26 @@ 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 temporary database objects that can hold a value.
+ A session variable can be created by the <command>CREATE VARIABLE</command>
+ command and can only be accessed by its owner. The value of a session
+ variable is stored in session memory and is private to each session. It is
+ automatically released when the session ends.
+ </para>
+ </sect1>
+
<sect1 id="ddl-others">
<title>Other Database Objects</title>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index e2db5bcc78c..eab22f11a64 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1743,6 +1743,21 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-session-variable">
+ <glossterm>Session variable</glossterm>
+ <glossdef>
+ <para>
+ A temporal database object that holds a value in session memory. This
+ value is private to each session and is released when the session ends.
+ The default value of the session variable is null. Read or write access
+ to session variables is allowed only to owner (creator).
+ </para>
+ <para>
+ For more information, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-shared-memory">
<glossterm>Shared memory</glossterm>
<glossdef>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 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..4e8c1940252
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,133 @@
+<!--
+doc/src/sgml/ref/create_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createvariable">
+ <indexterm zone="sql-createvariable">
+ <primary>CREATE VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>defining</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>CREATE VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE VARIABLE</refname>
+ <refpurpose>define a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command creates a session
+ variable. Currently only temporary session variables are supported,
+ and then the keyword <literal>TEMPORARY</literal> is required.
+ </para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a session variable's value returns NULL, unless its value is set to
+ something else in the current session with a <command>LET</command> command.
+ The content of a session variable is not transactional. This is the same as
+ regular variables in procedural languages.
+ </para>
+
+ <para>
+ Session variables are retrieved by the <command>SELECT</command>
+ command. Their value is set with the <command>LET</command> command.
+ </para>
+
+ <para>
+ Session variables cannot be used in views or in SQL functions using
+ SQL-conforming style syntax.
+ </para>
+
+ <note>
+ <para>
+ Session variables can be <quote>shadowed</quote> by other identifiers.
+ For details, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </note>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry id="sql-createvariable-name">
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createvariable-data_type">
+ <term><replaceable class="parameter">data_type</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the data type of the session
+ variable. Only buildin scalar data types are allowed. Arrays or composite
+ types are not allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Use the <command>DROP VARIABLE</command> command to remove a session
+ variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Create an date session variable <literal>var1</literal>:
+<programlisting>
+CREATE TEMPORARY VARIABLE var1 AS date;
+</programlisting>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 00000000000..e8517a78200
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,84 @@
+<!--
+doc/src/sgml/ref/drop_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-dropvariable">
+ <indexterm zone="sql-dropvariable">
+ <primary>DROP VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>removing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP VARIABLE</refname>
+ <refpurpose>remove a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP VARIABLE <replaceable class="parameter">name</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DROP VARIABLE</command> removes a session variable.
+ A session variable can only be removed by its owner or a superuser.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To remove the session variable <literal>var1</literal>:
+
+<programlisting>
+DROP VARIABLE var1;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>DROP VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 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 92526012d2a..77075f9051d 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 ca3f53c6213..e88e3b9c058 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..cd61df4a370
--- /dev/null
+++ b/src/backend/commands/session_variable.c
@@ -0,0 +1,215 @@
+/*-------------------------------------------------------------------------
+ *
+ * session_variable.c
+ * session variable creation/manipulation commands
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/commands/session_variable.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "catalog/pg_language.h"
+#include "commands/session_variable.h"
+#include "miscadmin.h"
+#include "parser/parse_type.h"
+#include "storage/proc.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+
+/*
+ * The session variables are stored in the backend's private memory (data,
+ * metadata) in the dedicated memory context SVariableMemoryContext in binary
+ * format. They are stored in the "sessionvars" hash table, whose key is the
+ * name of the variable.
+ *
+ * Only owner (creator) can access the session variables. Because there is
+ * not catalog support, there is not possibility to track dependecies, and
+ * then only buildin types.
+ */
+typedef struct SVariableData
+{
+ NameData varname;
+
+ Oid varowner;
+ Oid vartype;
+ int32 vartypmod;
+ Oid varcollation;
+
+ bool isnull;
+ Datum value;
+
+ int16 typlen;
+ bool typbyval;
+} SVariableData;
+
+typedef SVariableData *SVariable;
+
+static HTAB *sessionvars = NULL; /* hash table for session variables */
+
+static MemoryContext SVariableMemoryContext = NULL;
+
+/*
+ * Create the hash table for storing session variables.
+ */
+static void
+create_sessionvars_hashtables(void)
+{
+ HASHCTL vars_ctl;
+
+ Assert(!sessionvars);
+
+ if (!SVariableMemoryContext)
+ {
+ /* we need our own long-lived memory context */
+ SVariableMemoryContext =
+ AllocSetContextCreate(TopMemoryContext,
+ "session variables",
+ ALLOCSET_START_SMALL_SIZES);
+ }
+
+ vars_ctl.keysize = NAMEDATALEN;
+ vars_ctl.entrysize = sizeof(SVariableData);
+ vars_ctl.hcxt = SVariableMemoryContext;
+
+ sessionvars = hash_create("Session variables", 64, &vars_ctl,
+ HASH_ELEM | HASH_STRINGS | HASH_CONTEXT);
+}
+
+/*
+ * Returns entry of session variable specified by name
+ */
+static SVariable
+search_variable(char *varname)
+{
+ SVariable svar;
+
+ if (!sessionvars)
+ create_sessionvars_hashtables();
+
+ svar = (SVariable) hash_search(sessionvars, varname,
+ HASH_FIND, NULL);
+
+ if (!svar)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("session variable \"%s\" doesn't exist",
+ varname)));
+
+ return svar;
+}
+
+/*
+ * Creates a new variable - does new entry in sessionvars
+ *
+ * Used by CREATE VARIABLE command
+ */
+void
+CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
+{
+ Oid typeid;
+ int32 typmod;
+ Oid typcollation;
+ Oid varowner = GetUserId();
+ SVariable svar;
+ bool found;
+ int16 typlen;
+ bool typbyval;
+
+ /*
+ * Current implementation is not catalog based, but we expect catalog
+ * based implementation for future, so we force same limits.
+ */
+ PreventCommandIfReadOnly("CREATE VARIABLE");
+ PreventCommandIfParallelMode("CREATE VARIABLE");
+ PreventCommandDuringRecovery("CREATE VARIABLE");
+
+ typenameTypeIdAndMod(pstate, stmt->typeName, &typeid, &typmod);
+
+ if (get_typtype(typeid) != TYPTYPE_BASE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s is not a base type",
+ format_type_be(typeid))));
+
+ if (OidIsValid(get_element_type(typeid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s type is an array",
+ format_type_be(typeid))));
+
+ /* allow only buildin types */
+ if (typeid >= FirstUnpinnedObjectId)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("session variable cannot have a user-defined type"),
+ errdetail("Session variables that make use of user-defined types are not yet supported."));
+
+ get_typlenbyval(typeid, &typlen, &typbyval);
+ typcollation = get_typcollation(typeid);
+
+ if (!sessionvars)
+ create_sessionvars_hashtables();
+
+ svar = hash_search(sessionvars, stmt->name,
+ HASH_ENTER, &found);
+
+ if (found)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
+
+ namestrcpy(&svar->varname, stmt->name);
+ svar->vartype = typeid;
+ svar->vartypmod = typmod;
+ svar->varcollation = typcollation;
+ svar->varowner = varowner;
+ svar->typlen = typlen;
+ svar->typbyval = typbyval;
+
+ svar->value = (Datum) 0;
+ svar->isnull = true;
+}
+
+/*
+ * Drop variable by name
+ */
+void
+DropVariableByName(char *varname)
+{
+ SVariable svar;
+
+ /*
+ * Current implementation is not catalog based, but we expect catalog
+ * based implementation for future, so we force same limits.
+ */
+ PreventCommandIfReadOnly("DROP VARIABLE");
+ PreventCommandIfParallelMode("DROP VARIABLE");
+ PreventCommandDuringRecovery("DROP VARIABLE");
+
+ svar = search_variable(varname);
+
+ /* only owner can get content of variable */
+ if (svar->varowner != GetUserId() && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be owner of session variable %s",
+ varname)));
+
+ if (!svar->typbyval && !svar->isnull)
+ pfree(DatumGetPointer(svar->value));
+
+ if (hash_search(sessionvars,
+ varname,
+ HASH_REMOVE,
+ NULL) == NULL)
+ elog(ERROR, "hash table corrupted");
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c567252acc4..e968260ec89 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -291,13 +291,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
@@ -792,8 +792,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
@@ -1059,6 +1059,7 @@ stmt:
| CreatePolicyStmt
| CreatePLangStmt
| CreateSchemaStmt
+ | CreateSessionVarStmt
| CreateSeqStmt
| CreateStmt
| CreateSubscriptionStmt
@@ -1086,6 +1087,7 @@ stmt:
| DropTableSpaceStmt
| DropTransformStmt
| DropRoleStmt
+ | DropSessionVarStmt
| DropUserMappingStmt
| DropdbStmt
| ExecuteStmt
@@ -5388,6 +5390,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 ]
@@ -18231,6 +18274,7 @@ unreserved_keyword:
| VALIDATE
| VALIDATOR
| VALUE_P
+ | VARIABLE
| VARYING
| VERSION_P
| VIEW
@@ -18891,6 +18935,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 34dd6e18df5..41a7edfe53d 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 **************
*/
@@ -3236,6 +3249,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));
@@ -3774,6 +3795,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 8b91bc00062..b829eed2ab0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1364,6 +1364,7 @@ static const pgsql_thing_t words_after_create[] = {
{"USER", Query_for_list_of_roles, NULL, NULL, Keywords_for_user_thing},
{"USER MAPPING FOR", NULL, NULL, NULL},
{"VIEW", NULL, NULL, &Query_for_list_of_views},
+ {"VARIABLE", NULL, NULL, NULL, NULL, THING_NO_CREATE},
{NULL} /* end of list */
};
@@ -3769,7 +3770,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");
@@ -4122,6 +4123,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 0aec49bdd22..2102aafe76c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3586,6 +3586,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 f7753c5c8a8..d4b1223a33e 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -491,6 +491,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 1290c9bab68..4b964d0fb5a 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..45c2d27ab44
--- /dev/null
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -0,0 +1,43 @@
+SET log_statement TO ddl;
+-- should to fail
+CREATE VARIABLE x AS int;
+ERROR: only temporal session variables are supported
+-- should be ok
+CREATE TEMPORARY VARIABLE x AS int;
+-- should fail
+CREATE TEMPORARY VARIABLE x AS int;
+ERROR: session variable "x" already exists
+-- should fail
+DROP VARIABLE y;
+ERROR: session variable "y" doesn't exist
+-- should be ok
+DROP VARIABLE x;
+CREATE TYPE test_type AS (x int, y int);
+-- should fail
+CREATE VARIABLE x AS test_type;
+ERROR: only temporal session variables are supported
+DROP TYPE test_type;
+-- should fail
+CREATE VARIABLE x AS int[];
+ERROR: only temporal session variables are supported
+CREATE DOMAIN test_domain AS int;
+-- should fail
+CREATE TEMP VARIABLE x AS test_domain;
+ERROR: test_domain is not a base type
+DROP DOMAIN test_domain;
+CREATE ROLE regress_session_variable_test_role_01;
+CREATE ROLE regress_session_variable_test_role_02;
+SET ROLE TO regress_session_variable_test_role_01;
+CREATE TEMP VARIABLE x AS int;
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_02;
+-- should fail
+DROP VARIABLE x;
+ERROR: must be owner of session variable x
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_01;
+-- should be ok
+DROP VARIABLE x;
+SET ROLE TO DEFAULT;
+DROP ROLE regress_session_variable_test_role_01;
+DROP ROLE regress_session_variable_test_role_02;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 549e9b2d7be..8f9988954f9 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -115,7 +115,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
# NB: temp.sql does reconnects which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml session_variables_ddl
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
new file mode 100644
index 00000000000..34f34dd898f
--- /dev/null
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -0,0 +1,56 @@
+SET log_statement TO ddl;
+
+-- should to fail
+CREATE VARIABLE x AS int;
+
+-- should be ok
+CREATE TEMPORARY VARIABLE x AS int;
+
+-- should fail
+CREATE TEMPORARY VARIABLE x AS int;
+
+-- should fail
+DROP VARIABLE y;
+
+-- should be ok
+DROP VARIABLE x;
+
+CREATE TYPE test_type AS (x int, y int);
+
+-- should fail
+CREATE VARIABLE x AS test_type;
+
+DROP TYPE test_type;
+
+-- should fail
+CREATE VARIABLE x AS int[];
+
+CREATE DOMAIN test_domain AS int;
+
+-- should fail
+CREATE TEMP VARIABLE x AS test_domain;
+
+DROP DOMAIN test_domain;
+
+CREATE ROLE regress_session_variable_test_role_01;
+CREATE ROLE regress_session_variable_test_role_02;
+
+SET ROLE TO regress_session_variable_test_role_01;
+
+CREATE TEMP VARIABLE x AS int;
+
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_02;
+
+-- should fail
+DROP VARIABLE x;
+
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_01;
+
+-- should be ok
+DROP VARIABLE x;
+
+SET ROLE TO DEFAULT;
+DROP ROLE regress_session_variable_test_role_01;
+DROP ROLE regress_session_variable_test_role_02;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 6e2d876a40f..c17bf76a6ea 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -574,6 +574,7 @@ CreateRoleStmt
CreateSchemaStmt
CreateSchemaStmtContext
CreateSeqStmt
+CreateSessionVarStmt
CreateStatsStmt
CreateStmt
CreateStmtContext
@@ -677,6 +678,7 @@ DropBehavior
DropOwnedStmt
DropReplicationSlotCmd
DropRoleStmt
+DropSessionVarStmt
DropStmt
DropSubscriptionStmt
DropTableSpaceStmt
@@ -2698,6 +2700,8 @@ SSL_CTX
STARTUPINFO
STRLEN
SV
+SVariableData
+SVariable
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
SampleScan
--
2.53.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: <CAFj8pRDLL-srue=m+je26vFrMzwETWwOfsEVNwr7=_KhuWA1bQ@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