v20250604-0013-DISCARD-VARIABLES.patch

From d4366c77ecf252fe75d74490a2a334de9faf8f2e Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Mon, 2 Jun 2025 20:41:57 +0200
Subject: [PATCH 13/15] DISCARD VARIABLES

Implementation of DISCARD VARIABLES commands by removing hash table with session variables
and resetting related memory context.
---
 doc/src/sgml/ref/discard.sgml                 | 13 ++++-
 src/backend/commands/discard.c                |  6 +++
 src/backend/commands/session_variable.c       | 28 ++++++++++-
 src/backend/parser/gram.y                     |  6 +++
 src/backend/tcop/utility.c                    |  3 ++
 src/bin/psql/tab-complete.in.c                |  2 +-
 src/include/commands/session_variable.h       |  2 +
 src/include/nodes/parsenodes.h                |  1 +
 src/include/tcop/cmdtaglist.h                 |  1 +
 .../expected/session_variables_dml.out        | 50 +++++++++++++++++++
 .../regress/sql/session_variables_dml.sql     | 24 +++++++++
 11 files changed, 133 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index bf44c523cac..61b967f9c9b 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
+DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP | VARIABLES }
 </synopsis>
  </refsynopsisdiv>
 
@@ -66,6 +66,16 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>VARIABLES</literal></term>
+    <listitem>
+     <para>
+      Resets the value of all session variables. If a variable
+      is later reused, it is re-initialized to <literal>NULL</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
     <listitem>
@@ -93,6 +103,7 @@ SELECT pg_advisory_unlock_all();
 DISCARD PLANS;
 DISCARD TEMP;
 DISCARD SEQUENCES;
+DISCARD VARIABLES;
 </programlisting></para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c
index 81339a75a52..5904a6c4917 100644
--- a/src/backend/commands/discard.c
+++ b/src/backend/commands/discard.c
@@ -18,6 +18,7 @@
 #include "commands/async.h"
 #include "commands/discard.h"
 #include "commands/prepare.h"
+#include "commands/session_variable.h"
 #include "commands/sequence.h"
 #include "utils/guc.h"
 #include "utils/portal.h"
@@ -48,6 +49,10 @@ DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
 			ResetTempTableNamespace();
 			break;
 
+		case DISCARD_VARIABLES:
+			ResetSessionVariables();
+			break;
+
 		default:
 			elog(ERROR, "unrecognized DISCARD target: %d", stmt->target);
 	}
@@ -75,4 +80,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 41f470caa86..4339dc043e8 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -102,7 +102,13 @@ pg_variable_cache_callback(Datum arg, int cacheid, uint32 hashvalue)
 
 	elog(DEBUG1, "pg_variable_cache_callback %u %u", cacheid, hashvalue);
 
-	Assert(sessionvars);
+	/*
+	 * There is no guarantee of session variables being initialized, even when
+	 * receiving an invalidation callback, as DISCARD [ ALL | VARIABLES ]
+	 * destroys the hash table entirely.
+	 */
+	if (!sessionvars)
+		return;
 
 	/*
 	 * If the hashvalue is not specified, we have to recheck all currently
@@ -698,3 +704,23 @@ pg_session_variables(PG_FUNCTION_ARGS)
 
 	return (Datum) 0;
 }
+
+/*
+ * 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 VARIABLES (and DISCARD ALL) command.
+ */
+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/backend/parser/gram.y b/src/backend/parser/gram.y
index 750cf5171d8..207a2779df5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2084,7 +2084,13 @@ DiscardStmt:
 					n->target = DISCARD_SEQUENCES;
 					$$ = (Node *) n;
 				}
+			| DISCARD VARIABLES
+				{
+					DiscardStmt *n = makeNode(DiscardStmt);
 
+					n->target = DISCARD_VARIABLES;
+					$$ = (Node *) n;
+				}
 		;
 
 
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 20e4d43576b..5ffcee5aa83 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -2961,6 +2961,9 @@ CreateCommandTag(Node *parsetree)
 				case DISCARD_SEQUENCES:
 					tag = CMDTAG_DISCARD_SEQUENCES;
 					break;
+				case DISCARD_VARIABLES:
+					tag = CMDTAG_DISCARD_VARIABLES;
+					break;
 				default:
 					tag = CMDTAG_UNKNOWN;
 			}
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 39d1b6aa610..06cbac40ca0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -4152,7 +4152,7 @@ match_previous_words(int pattern_id,
 
 /* DISCARD */
 	else if (Matches("DISCARD"))
-		COMPLETE_WITH("ALL", "PLANS", "SEQUENCES", "TEMP");
+		COMPLETE_WITH("ALL", "PLANS", "SEQUENCES", "TEMP", "VARIABLES");
 
 /* DO */
 	else if (Matches("DO"))
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index 2ebe8477789..ac36dfcc19b 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -29,4 +29,6 @@ extern ObjectAddress CreateVariable(ParseState *pstate, CreateSessionVarStmt *st
 extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo params,
 						   QueryEnvironment *queryEnv, QueryCompletion *qc);
 
+extern void ResetSessionVariables(void);
+
 #endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8c508b851da..895d20511b9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4071,6 +4071,7 @@ typedef enum DiscardMode
 	DISCARD_PLANS,
 	DISCARD_SEQUENCES,
 	DISCARD_TEMP,
+	DISCARD_VARIABLES,
 } DiscardMode;
 
 typedef struct DiscardStmt
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 22082c30008..bef0ac25331 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -135,6 +135,7 @@ PG_CMDTAG(CMDTAG_DISCARD_ALL, "DISCARD ALL", false, false, false)
 PG_CMDTAG(CMDTAG_DISCARD_PLANS, "DISCARD PLANS", false, false, false)
 PG_CMDTAG(CMDTAG_DISCARD_SEQUENCES, "DISCARD SEQUENCES", false, false, false)
 PG_CMDTAG(CMDTAG_DISCARD_TEMP, "DISCARD TEMP", false, false, false)
+PG_CMDTAG(CMDTAG_DISCARD_VARIABLES, "DISCARD VARIABLES", false, false, false)
 PG_CMDTAG(CMDTAG_DO, "DO", false, false, false)
 PG_CMDTAG(CMDTAG_DROP_ACCESS_METHOD, "DROP ACCESS METHOD", true, false, false)
 PG_CMDTAG(CMDTAG_DROP_AGGREGATE, "DROP AGGREGATE", true, false, false)
diff --git a/src/test/regress/expected/session_variables_dml.out b/src/test/regress/expected/session_variables_dml.out
index 0fdf0fdc68a..447c10bc4a3 100644
--- a/src/test/regress/expected/session_variables_dml.out
+++ b/src/test/regress/expected/session_variables_dml.out
@@ -447,3 +447,53 @@ drop cascades to session variable svartest_dml.sesvar46
 drop cascades to session variable svartest_dml.sesvar47
 drop cascades to session variable svartest_dml.sesvar48
 DROP ROLE regress_svartest_dml_write_only_role;
+CREATE SCHEMA svartest_dml_discard;
+CREATE VARIABLE svartest_dml_discard.sesvar50 AS varchar;
+LET svartest_dml_discard.sesvar50 = 'Hello';
+SELECT VARIABLE(svartest_dml_discard.sesvar50);
+ sesvar50 
+----------
+ Hello
+(1 row)
+
+SELECT count(*) FROM pg_session_variables() WHERE schema = 'svartest_dml_discard';
+ count 
+-------
+     1
+(1 row)
+
+DISCARD ALL;
+SELECT count(*) FROM pg_session_variables();
+ count 
+-------
+     0
+(1 row)
+
+SELECT VARIABLE(svartest_dml_discard.sesvar50);
+ sesvar50 
+----------
+ 
+(1 row)
+
+LET svartest_dml_discard.sesvar50 = 'Hello';
+SELECT count(*) FROM pg_session_variables();
+ count 
+-------
+     1
+(1 row)
+
+DISCARD VARIABLES;
+SELECT count(*) FROM pg_session_variables();
+ count 
+-------
+     0
+(1 row)
+
+SELECT VARIABLE(svartest_dml_discard.sesvar50);
+ sesvar50 
+----------
+ 
+(1 row)
+
+DROP SCHEMA svartest_dml_discard CASCADE;
+NOTICE:  drop cascades to session variable svartest_dml_discard.sesvar50
diff --git a/src/test/regress/sql/session_variables_dml.sql b/src/test/regress/sql/session_variables_dml.sql
index 1250e7ef062..340ae6f76ab 100644
--- a/src/test/regress/sql/session_variables_dml.sql
+++ b/src/test/regress/sql/session_variables_dml.sql
@@ -336,3 +336,27 @@ SELECT VARIABLE(svartest_dml.sesvar48);
 
 DROP SCHEMA svartest_dml CASCADE;
 DROP ROLE regress_svartest_dml_write_only_role;
+
+CREATE SCHEMA svartest_dml_discard;
+
+CREATE VARIABLE svartest_dml_discard.sesvar50 AS varchar;
+LET svartest_dml_discard.sesvar50 = 'Hello';
+SELECT VARIABLE(svartest_dml_discard.sesvar50);
+
+SELECT count(*) FROM pg_session_variables() WHERE schema = 'svartest_dml_discard';
+
+DISCARD ALL;
+
+SELECT count(*) FROM pg_session_variables();
+
+SELECT VARIABLE(svartest_dml_discard.sesvar50);
+LET svartest_dml_discard.sesvar50 = 'Hello';
+
+SELECT count(*) FROM pg_session_variables();
+
+DISCARD VARIABLES;
+
+SELECT count(*) FROM pg_session_variables();
+
+SELECT VARIABLE(svartest_dml_discard.sesvar50);
+DROP SCHEMA svartest_dml_discard CASCADE;
-- 
2.49.0