v20240315-0019-transactional-variables.patch

From 6ebbb8324c7dad93af9056c8c8f160ccc47bf93c Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <okbob@github.com>
Date: Tue, 20 Feb 2024 13:14:34 +0100
Subject: [PATCH 19/19] transactional variables

This commit implements transactional variables. The content of transactional
session variables is sensitive to transactions and subtransactions. Any
transactional variable holds a history of values necessary for revert. This history
is cleaned (purged) when a) we read the variable, b) when the transaction is finished.

We don't try to purge, when the last modification of a variable is from
current subtransaction, or when purge was processed in current subtransaction.

This patch is based on my work from Feb 2020 and it is related to discussion
about features related to session variables. Now, when the all features are
separated to isolated patches I can revitalize this patch, because it doesn't
increase complexity of basic patches.

Unlike other patches, this patch is without any review at this moment (Feb 2024),
but the feature should be fully functional for people who are interested about
this feature (for testing).
---
 doc/src/sgml/catalogs.sgml                    |  11 +
 doc/src/sgml/ref/create_variable.sgml         |  10 +-
 src/backend/catalog/pg_variable.c             |   4 +
 src/backend/commands/session_variable.c       | 301 +++++++++++++++++-
 src/backend/parser/gram.y                     |  50 +--
 src/bin/pg_dump/pg_dump.c                     |  10 +-
 src/bin/pg_dump/pg_dump.h                     |   1 +
 src/bin/pg_dump/t/002_pg_dump.pl              |  36 +++
 src/bin/psql/describe.c                       |   4 +-
 src/bin/psql/tab-complete.c                   |   5 +
 src/include/catalog/pg_variable.h             |   3 +
 src/include/nodes/parsenodes.h                |   1 +
 src/include/parser/kwlist.h                   |   1 +
 src/test/regress/expected/psql.out            |  36 +--
 .../regress/expected/session_variables.out    | 110 ++++++-
 src/test/regress/sql/session_variables.sql    |  58 ++++
 16 files changed, 591 insertions(+), 50 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 47b91eaa5d..c111ed20ab 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9841,6 +9841,17 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       </para></entry>
      </row>
 
+     <row>
+      <entry><structfield>varistransact</structfield></entry>
+      <entry><type>boolean</type></entry>
+      <entry></entry>
+      <entry>
+       True, when the variable is "transactional". In case of transaction
+       rollback, transactional variables are reset to their content at the
+       transaction start. The default value is false.
+      </entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>vareoxaction</structfield> <type>char</type>
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
index ef695d6637..edf01ccdf5 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 [ { TEMPORARY | TEMP } ] [ IMMUTABLE ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ]
+CREATE [ { TEMPORARY | TEMP } ] [ { TRANSACTIONAL | TRANSACTION } ] [ IMMUTABLE ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ]
     [ NOT NULL ] [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]
 </synopsis>
  </refsynopsisdiv>
@@ -48,6 +48,14 @@ CREATE [ { TEMPORARY | TEMP } ] [ IMMUTABLE ] VARIABLE [ IF NOT EXISTS ] <replac
    same as regular variables in PL languages.
   </para>
 
+  <para>
+   When a schema variable is created with a
+   <command>CREATE TRANSACTIONAL VARIABLE</command> command, the variables
+   content changes are transactional: in case of rollback, they are reset to
+   their value at the beginning of the transaction or the latest subtransaction.
+   The variable content is only hold in memory, and thus is not persistent.
+  </para>
+
   <para>
    Session variables are retrieved by the <command>SELECT</command> SQL
    command.  Their value is set with the <command>LET</command> SQL command.
diff --git a/src/backend/catalog/pg_variable.c b/src/backend/catalog/pg_variable.c
index 4a1d12e5ef..8e84fcaf23 100644
--- a/src/backend/catalog/pg_variable.c
+++ b/src/backend/catalog/pg_variable.c
@@ -42,6 +42,7 @@ static ObjectAddress create_variable(const char *varName,
 									 bool if_not_exists,
 									 bool is_not_null,
 									 bool is_immutable,
+									 bool is_transact,
 									 Node *varDefexpr,
 									 VariableEOXAction eoxaction);
 
@@ -59,6 +60,7 @@ create_variable(const char *varName,
 				bool if_not_exists,
 				bool is_not_null,
 				bool is_immutable,
+				bool is_transact,
 				Node *varDefexpr,
 				VariableEOXAction eoxaction)
 {
@@ -123,6 +125,7 @@ create_variable(const char *varName,
 	values[Anum_pg_variable_varcollation - 1] = ObjectIdGetDatum(varCollation);
 	values[Anum_pg_variable_varisnotnull - 1] = BoolGetDatum(is_not_null);
 	values[Anum_pg_variable_varisimmutable - 1] = BoolGetDatum(is_immutable);
+	values[Anum_pg_variable_varistransact - 1] = BoolGetDatum(is_transact);
 	values[Anum_pg_variable_vareoxaction - 1] = CharGetDatum(eoxaction);
 
 	if (varDefexpr)
@@ -267,6 +270,7 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
 							   stmt->if_not_exists,
 							   stmt->is_not_null,
 							   stmt->is_immutable,
+							   stmt->is_transact,
 							   cooked_default,
 							   stmt->eoxaction);
 
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 21560695dc..e9494300b9 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -47,6 +47,19 @@ typedef struct SVariableXActDropItem
 	SubTransactionId deleting_subid;
 } SVariableXActDropItem;
 
+/*
+ * Used for transactional variables. Holds prev version.
+ */
+typedef struct PrevValue
+{
+	Datum		value;
+	bool		isnull;
+
+	SubTransactionId modify_subid;
+
+	struct PrevValue *prev_value;
+} PrevValue;
+
 /*
  * Values of session variables are stored in the backend local memory
  * inside sessionvars hash table in binary format inside a dedicated memory
@@ -75,6 +88,25 @@ typedef struct SVariableData
 	bool		isnull;
 	Datum		value;
 
+	/*
+	 * We don't need stack versions modified in same subtransaction.
+	 * Used by transactional variables only. The value of transactional
+	 * variable can be returned immediately when modify_subid is same
+	 * like current subid.
+	 */
+	SubTransactionId modify_subid;
+
+	/*
+	 * When the modify_subid is different than current subid, then
+	 * we need to recheck versions and throw versions related to
+	 * reverted transactions. When purge_subid is same like current subid
+	 * we can return the value of transaction variable without this
+	 * recheck.
+	 */
+	SubTransactionId purge_subid;
+
+	PrevValue  *prev_value;
+
 	Oid			typid;
 	int16		typlen;
 	bool		typbyval;
@@ -94,6 +126,7 @@ typedef struct SVariableData
 
 	bool		is_not_null;
 	bool		is_immutable;
+	bool		is_transact;
 
 	bool		reset_at_eox;
 
@@ -133,6 +166,9 @@ static bool needs_validation = false;
  */
 static bool has_session_variables_with_reset_at_eox = false;
 
+/* true, when transactional variables was modified */
+static bool has_modified_transactional_variables = false;
+
 /*
  * The content of session variables is not removed immediately. When it
  * is possible we do this at the transaction end. But when the transaction failed,
@@ -148,6 +184,7 @@ static List *xact_drop_items = NIL;
 
 static void register_session_variable_xact_drop(Oid varid);
 static void unregister_session_variable_xact_drop(Oid varid);
+static bool purge_session_variable(SVariable svar);
 
 /*
  * Callback function for session variable invalidation.
@@ -303,8 +340,25 @@ unregister_session_variable_xact_drop(Oid varid)
  * Release stored value, free memory
  */
 static void
-free_session_variable_value(SVariable svar)
+free_session_variable_value(SVariable svar, bool deep_free)
 {
+	if (deep_free)
+	{
+		PrevValue *prev_value = svar->prev_value;
+		PrevValue *next_value;
+
+		while (prev_value)
+		{
+			if (!svar->typbyval)
+				pfree(DatumGetPointer(prev_value->value));
+
+			next_value = prev_value->prev_value;
+			pfree(prev_value);
+
+			prev_value = next_value;
+		}
+	}
+
 	/* Clean current value */
 	if (!svar->isnull)
 	{
@@ -403,7 +457,7 @@ remove_invalid_session_variables(bool atEOX)
 			{
 				Oid			varid = svar->varid;
 
-				free_session_variable_value(svar);
+				free_session_variable_value(svar, true);
 				hash_search(sessionvars, &varid, HASH_REMOVE, NULL);
 				svar = NULL;
 			}
@@ -439,6 +493,94 @@ remove_session_variables_with_reset_at_eox(void)
 	has_session_variables_with_reset_at_eox = false;
 }
 
+/*
+ * remove prev values at eox
+ */
+static void
+remove_prev_values_at_eox(bool isCommit)
+{
+	HASH_SEQ_STATUS status;
+	SVariable	svar;
+
+	if (!sessionvars)
+		return;
+
+	/* leave quckly, when there are not that variables */
+	if (!has_modified_transactional_variables)
+		return;
+
+	hash_seq_init(&status, sessionvars);
+	while ((svar = (SVariable) hash_seq_search(&status)) != NULL)
+	{
+		if (svar->is_transact && svar->modify_subid != InvalidSubTransactionId)
+		{
+			if (isCommit)
+			{
+				if (purge_session_variable(svar))
+				{
+					PrevValue *prev_value = svar->prev_value;
+
+					while (prev_value)
+					{
+						PrevValue *current_pv = prev_value;
+
+						if (!svar->typbyval && !current_pv->isnull)
+							pfree(DatumGetPointer(current_pv->value));
+
+						prev_value = current_pv->prev_value;
+						pfree(current_pv);
+					}
+				}
+				else
+				{
+					hash_search(sessionvars, &svar->varid, HASH_REMOVE, NULL);
+					svar = NULL;
+				}
+			}
+			else
+			{
+				PrevValue *prev_value = svar->prev_value;
+
+				while (prev_value)
+				{
+					PrevValue *current_pv = prev_value;
+
+					if (current_pv->modify_subid == InvalidSubTransactionId)
+						break;
+
+					if (!svar->typbyval && !current_pv->isnull)
+						pfree(DatumGetPointer(current_pv->value));
+
+					prev_value = current_pv->prev_value;
+					pfree(current_pv);
+				}
+
+				if (prev_value)
+				{
+					svar->value = prev_value->value;
+					svar->isnull = prev_value->isnull;
+
+					pfree(prev_value);
+				}
+				else
+				{
+					hash_search(sessionvars, &svar->varid, HASH_REMOVE, NULL);
+					svar = NULL;
+				}
+			}
+
+			/* When svar is still valid (not removed from sessionvars */
+			if (svar)
+			{
+				svar->modify_subid = InvalidSubTransactionId;
+				svar->prev_value = NULL;
+			}
+		}
+	}
+
+	has_modified_transactional_variables = false;
+}
+
 /*
   * Perform ON COMMIT DROP for temporary session variables,
   * and remove all dropped variables from memory.
@@ -486,6 +628,8 @@ AtPreEOXact_SessionVariables(bool isCommit)
 		remove_invalid_session_variables(true);
 	}
 
+	remove_prev_values_at_eox(isCommit);
+
 	/*
 	 * We have to clean xact_drop_items. All related variables are dropped
 	 * now, or lost inside aborted transaction.
@@ -631,6 +775,7 @@ setup_session_variable(SVariable svar, Oid varid, bool is_write)
 
 	svar->is_not_null = varform->varisnotnull;
 	svar->is_immutable = varform->varisimmutable;
+	svar->is_transact = varform->varistransact;
 
 	svar->is_domain = (get_typtype(varform->vartype) == TYPTYPE_DOMAIN);
 	svar->domain_check_extra = NULL;
@@ -656,6 +801,17 @@ setup_session_variable(SVariable svar, Oid varid, bool is_write)
 	svar->isnull = true;
 	svar->value = (Datum) 0;
 
+	if (svar->is_transact)
+	{
+		svar->modify_subid = GetCurrentSubTransactionId();
+		has_modified_transactional_variables = true;
+	}
+	else
+		svar->modify_subid = InvalidSubTransactionId;
+
+	svar->purge_subid = InvalidSubTransactionId;
+	svar->prev_value = NULL;
+
 	svar->is_valid = true;
 
 	svar->hashvalue = GetSysCacheHashValue1(VARIABLEOID,
@@ -689,6 +845,69 @@ setup_session_variable(SVariable svar, Oid varid, bool is_write)
 	ReleaseSysCache(tup);
 }
 
+/*
+ * Try to remove all previous versions related to reverted transactions.
+ * Returns true, when valid version was found.
+ */
+static bool
+purge_session_variable(SVariable svar)
+{
+	SubTransactionId current_subid;
+	PrevValue	   *prev_value;
+	bool			found = true;
+
+	Assert(svar->is_transact);
+
+	if (svar->modify_subid == InvalidSubTransactionId)
+		return true;
+
+	current_subid = GetCurrentSubTransactionId();
+
+	if (svar->modify_subid == current_subid)
+		return true;
+
+	if (svar->purge_subid == current_subid)
+		return true;
+
+	if (SubTransactionIsActive(svar->modify_subid))
+	{
+		svar->purge_subid = current_subid;
+		return true;
+	}
+
+	prev_value = svar->prev_value;
+
+	while (prev_value)
+	{
+		PrevValue *current_pv = prev_value;
+
+		if (current_pv->modify_subid == InvalidSubTransactionId ||
+			SubTransactionIsActive(current_pv->modify_subid))
+		{
+			svar->value = current_pv->value;
+			svar->isnull = current_pv->isnull;
+			svar->modify_subid = current_pv->modify_subid;
+
+			prev_value = current_pv->prev_value;
+			pfree(current_pv);
+
+			found = true;
+			break;
+		}
+
+		if (!svar->typbyval && !current_pv->isnull)
+			pfree(DatumGetPointer(current_pv->value));
+
+		prev_value = current_pv->prev_value;
+		pfree(current_pv);
+	}
+
+	svar->prev_value = prev_value;
+	svar->purge_subid = current_subid;
+
+	return found;
+}
+
 /*
  * Assign some content to the session variable. It's copied to
  * SVariableMemoryContext if necessary.
@@ -701,6 +920,10 @@ set_session_variable(SVariable svar, Datum value, bool isnull)
 	Datum		newval;
 	SVariableData locsvar,
 			   *_svar;
+	SubTransactionId current_subid = GetCurrentSubTransactionId();
+	SubTransactionId prev_purge_subid = InvalidSubTransactionId;
+	bool		save_prev_value;
+	PrevValue  *prev_value;
 
 	Assert(svar);
 	Assert(!isnull || value == (Datum) 0);
@@ -736,6 +959,21 @@ set_session_variable(SVariable svar, Datum value, bool isnull)
 	else
 		_svar = svar;
 
+	if (_svar->is_transact && _svar->create_lsn == svar->create_lsn)
+	{
+		Assert(svar->typid == _svar->typid);
+		Assert(svar->typbyval == _svar->typbyval);
+		Assert(svar->typlen == _svar->typlen);
+
+		save_prev_value = svar->modify_subid != current_subid;
+		prev_value = svar->prev_value;
+	}
+	else
+	{
+		save_prev_value = false;
+		prev_value = NULL;
+	}
+
 	if (!isnull)
 	{
 		MemoryContext oldcxt = MemoryContextSwitchTo(SVariableMemoryContext);
@@ -747,7 +985,37 @@ set_session_variable(SVariable svar, Datum value, bool isnull)
 	else
 		newval = value;
 
-	free_session_variable_value(svar);
+	if (save_prev_value)
+	{
+		volatile PrevValue *new_prev_value;
+
+		PG_TRY();
+		{
+			new_prev_value = MemoryContextAlloc(SVariableMemoryContext,
+												sizeof(PrevValue));
+		}
+		PG_CATCH();
+		{
+			/* release mem from persistent content */
+			if (newval != value)
+				pfree(DatumGetPointer(newval));
+			PG_RE_THROW();
+		}
+		PG_END_TRY();
+
+		new_prev_value->value = svar->value;
+		new_prev_value->isnull = svar->isnull;
+		new_prev_value->modify_subid = svar->modify_subid;
+		new_prev_value->prev_value = prev_value;
+
+		prev_value = (PrevValue *) new_prev_value;
+		prev_purge_subid = svar->purge_subid;
+
+		has_modified_transactional_variables = true;
+
+	}
+	else
+		free_session_variable_value(svar, prev_value == NULL);
 
 	/* We can overwrite old variable now. No error expected */
 	if (svar != _svar)
@@ -755,6 +1023,9 @@ set_session_variable(SVariable svar, Datum value, bool isnull)
 
 	svar->value = newval;
 	svar->isnull = isnull;
+	svar->modify_subid = current_subid;
+	svar->purge_subid = prev_purge_subid;
+	svar->prev_value = prev_value;
 
 	/* don't allow more changes of value when variable is IMMUTABLE */
 	if (svar->is_immutable)
@@ -870,6 +1141,8 @@ get_session_variable(Oid varid)
 	else
 		svar->is_valid = false;
 
+reinit:
+
 	/*
 	 * Force setup for not yet initialized variables or variables that cannot
 	 * be validated.
@@ -902,6 +1175,28 @@ get_session_variable(Oid varid)
 			 varid);
 	}
 
+	/*
+	 * Transactional variables should be purged before (remove
+	 * versions created by possibly reverted subtransactions).
+	 */
+	if (svar->is_transact &&
+		svar->modify_subid != GetCurrentSubTransactionId() &&
+		svar->modify_subid != InvalidSubTransactionId)
+	{
+		if (!purge_session_variable(svar))
+		{
+			/* force reinit */
+			svar->is_valid = false;
+
+			/*
+			 * In next iteration modify_subid should be
+			 * InvalidSubTransactionId or current subid,
+			 * so there is not risk of infinity cycle.
+			 */
+			goto reinit;
+		}
+	}
+
 	/* Ensure so returned data is still correct domain */
 	if (svar->is_domain)
 	{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index be6e964d7f..2f6ff4840d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -665,7 +665,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				json_object_constructor_null_clause_opt
 				json_array_constructor_null_clause_opt
 
-%type <boolean>		OptNotNull OptImmutable
+%type <boolean>		OptNotNull OptImmutable OptTransactional
 
 /*
  * Non-keyword token types.  These are hard-wired into the "flex" lexer.
@@ -770,7 +770,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
 
 	TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
-	TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
+	TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSACTIONAL TRANSFORM
 	TREAT TRIGGER TRIM TRUE_P
 	TRUNCATE TRUSTED TYPE_P TYPES_P
 
@@ -5131,31 +5131,33 @@ create_extension_opt_item:
  *****************************************************************************/
 
 CreateSessionVarStmt:
-			CREATE OptTemp OptImmutable VARIABLE qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr OnEOXActionOption
+			CREATE OptTemp OptTransactional OptImmutable VARIABLE qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr OnEOXActionOption
 				{
 					CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
-					$5->relpersistence = $2;
-					n->is_immutable = $3;
-					n->variable = $5;
-					n->typeName = $7;
-					n->collClause = (CollateClause *) $8;
-					n->is_not_null = $9;
-					n->defexpr = $10;
-					n->eoxaction = $11;
+					$6->relpersistence = $2;
+					n->is_immutable = $4;
+					n->is_transact = $3;
+					n->variable = $6;
+					n->typeName = $8;
+					n->collClause = (CollateClause *) $9;
+					n->is_not_null = $10;
+					n->defexpr = $11;
+					n->eoxaction = $12;
 					n->if_not_exists = false;
 					$$ = (Node *) n;
 				}
-			| CREATE OptTemp OptImmutable VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr OnEOXActionOption
+			| CREATE OptTemp OptTransactional OptImmutable VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr OnEOXActionOption
 				{
 					CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
-					$8->relpersistence = $2;
-					n->is_immutable = $3;
-					n->variable = $8;
-					n->typeName = $10;
-					n->collClause = (CollateClause *) $11;
-					n->is_not_null = $12;
-					n->defexpr = $13;
-					n->eoxaction = $14;
+					$9->relpersistence = $2;
+					n->is_immutable = $4;
+					n->is_transact = $3;
+					n->variable = $9;
+					n->typeName = $11;
+					n->collClause = (CollateClause *) $12;
+					n->is_not_null = $13;
+					n->defexpr = $14;
+					n->eoxaction = $15;
 					n->if_not_exists = true;
 					$$ = (Node *) n;
 				}
@@ -5183,6 +5185,12 @@ OptImmutable: IMMUTABLE								{ $$ = true; }
 			| /* EMPTY */							{ $$ = false; }
 		;
 
+OptTransactional:
+			TRANSACTION								{ $$ = true; }
+			| TRANSACTIONAL							{ $$ = true; }
+			| /* EMPTY */							{ $$ = false; }
+		;
+
 /*****************************************************************************
  *
  * ALTER EXTENSION name UPDATE [ TO version ]
@@ -17606,6 +17614,7 @@ unreserved_keyword:
 			| TEXT_P
 			| TIES
 			| TRANSACTION
+			| TRANSACTIONAL
 			| TRANSFORM
 			| TRIGGER
 			| TRUNCATE
@@ -18231,6 +18240,7 @@ bare_label_keyword:
 			| TIMESTAMP
 			| TRAILING
 			| TRANSACTION
+			| TRANSACTIONAL
 			| TRANSFORM
 			| TREAT
 			| TRIGGER
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 0de83a4a57..0dd7e0ca99 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5232,6 +5232,7 @@ getVariables(Archive *fout)
 	int			i_varcollation;
 	int			i_varisnotnull;
 	int			i_varisimmutable;
+	int			i_varistransact;
 	int			i_varacl;
 	int			i_acldefault;
 	int			i,
@@ -5255,6 +5256,7 @@ getVariables(Archive *fout)
 					  "THEN v.varcollation ELSE 0 END AS varcollation,\n"
 					  "v.varisnotnull,\n"
 					  "v.varisimmutable,\n"
+					  "v.varistransact,\n"
 					  "pg_catalog.pg_get_expr(v.vardefexpr,0) as vardefexpr,\n"
 					  "v.varowner,\n"
 					  "v.varacl,\n"
@@ -5278,6 +5280,7 @@ getVariables(Archive *fout)
 	i_varcollation = PQfnumber(res, "varcollation");
 	i_varisnotnull = PQfnumber(res, "varisnotnull");
 	i_varisimmutable = PQfnumber(res, "varisimmutable");
+	i_varistransact = PQfnumber(res, "varistransact");
 
 	i_varowner = PQfnumber(res, "varowner");
 	i_varacl = PQfnumber(res, "varacl");
@@ -5304,6 +5307,7 @@ getVariables(Archive *fout)
 		varinfo[i].varcollation = atooid(PQgetvalue(res, i, i_varcollation));
 		varinfo[i].varisnotnull = *(PQgetvalue(res, i, i_varisnotnull)) == 't';
 		varinfo[i].varisimmutable = *(PQgetvalue(res, i, i_varisimmutable)) == 't';
+		varinfo[i].varistransact = *(PQgetvalue(res, i, i_varistransact)) == 't';
 
 		varinfo[i].dacl.acl = pg_strdup(PQgetvalue(res, i, i_varacl));
 		varinfo[i].dacl.acldefault = pg_strdup(PQgetvalue(res, i, i_acldefault));
@@ -5354,6 +5358,7 @@ dumpVariable(Archive *fout, const VariableInfo *varinfo)
 	const char *vardefexpr;
 	const char *vareoxaction;
 	const char *varisimmutable;
+	const char *varistransact;
 	Oid			varcollation;
 	bool		varisnotnull;
 
@@ -5371,12 +5376,13 @@ dumpVariable(Archive *fout, const VariableInfo *varinfo)
 	varcollation = varinfo->varcollation;
 	varisnotnull = varinfo->varisnotnull;
 	varisimmutable = varinfo->varisimmutable ? "IMMUTABLE " : "";
+	varistransact = varinfo->varistransact ? "TRANSACTIONAL " : "";
 
 	appendPQExpBuffer(delq, "DROP VARIABLE %s;\n",
 					  qualvarname);
 
-	appendPQExpBuffer(query, "CREATE %sVARIABLE %s AS %s",
-					  varisimmutable, qualvarname, vartypname);
+	appendPQExpBuffer(query, "CREATE %s%sVARIABLE %s AS %s",
+					  varistransact, varisimmutable, qualvarname, vartypname);
 
 	if (OidIsValid(varcollation))
 	{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index dbe2a0afc4..09dcd13250 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -708,6 +708,7 @@ typedef struct _VariableInfo
 	const char *rolname;		/* name of owner, or empty string */
 	bool		varisnotnull;
 	bool		varisimmutable;
+	bool		varistransact;
 } VariableInfo;
 
 /*
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index e142e3de51..ddea0855c9 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4098,6 +4098,42 @@ my %tests = (
 		},
 	},
 
+	'CREATE TRANSACTIONAL VARIABLE test_variable' => {
+		all_runs     => 1,
+		catch_all    => 'CREATE ... commands',
+		create_order => 61,
+		create_sql   => 'CREATE TRANSACTIONAL VARIABLE dump_test.variable8 AS integer',
+		regexp => qr/^
+			\QCREATE TRANSACTIONAL VARIABLE dump_test.variable8 AS integer;\E/xm,
+		like => {
+			%full_runs,
+			%dump_test_schema_runs,
+			section_pre_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			only_dump_measurement    => 1,
+		},
+	},
+
+	'CREATE TRANSACTIONAL IMMUTABLE VARIABLE test_variable' => {
+		all_runs     => 1,
+		catch_all    => 'CREATE ... commands',
+		create_order => 61,
+		create_sql   => 'CREATE TRANSACTIONAL IMMUTABLE VARIABLE dump_test.variable9 AS integer',
+		regexp => qr/^
+			\QCREATE TRANSACTIONAL IMMUTABLE VARIABLE dump_test.variable9 AS integer;\E/xm,
+		like => {
+			%full_runs,
+			%dump_test_schema_runs,
+			section_pre_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			only_dump_measurement    => 1,
+		},
+	},
+
 	'CREATE VIEW test_view' => {
 		create_order => 61,
 		create_sql => 'CREATE VIEW dump_test.test_view
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f2a4ff56ec..a8380c1c87 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5200,7 +5200,7 @@ listVariables(const char *pattern, bool verbose)
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false, false};
+	static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false, false, false};
 
 	initPQExpBuffer(&buf);
 
@@ -5213,6 +5213,7 @@ listVariables(const char *pattern, bool verbose)
 					  "  pg_catalog.pg_get_userbyid(v.varowner) as \"%s\",\n"
 					  "  NOT v.varisnotnull as \"%s\",\n"
 					  "  NOT v.varisimmutable as \"%s\",\n"
+					  "  v.varistransact as \"%s\",\n"
 					  "  pg_catalog.pg_get_expr(v.vardefexpr, 0) as \"%s\",\n"
 					  "  CASE v.vareoxaction\n"
 					  "    WHEN 'd' THEN 'ON COMMIT DROP'\n"
@@ -5225,6 +5226,7 @@ listVariables(const char *pattern, bool verbose)
 					  gettext_noop("Owner"),
 					  gettext_noop("Nullable"),
 					  gettext_noop("Mutable"),
+					  gettext_noop("Transactional"),
 					  gettext_noop("Default"),
 					  gettext_noop("Transactional end action"));
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8fea55aee3..8dd3148291 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1284,6 +1284,8 @@ static const pgsql_thing_t words_after_create[] = {
 																			 * TABLE ... */
 	{"TEXT SEARCH", NULL, NULL, NULL},
 	{"TRANSFORM", NULL, NULL, NULL, NULL, THING_NO_ALTER},
+	{"TRANSACTIONAL", NULL, NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER}, /* for CREATE TRANSACTIONAL
+																				* VARIABLE ... */
 	{"TRIGGER", "SELECT tgname FROM pg_catalog.pg_trigger WHERE tgname LIKE '%s' AND NOT tgisinternal"},
 	{"TYPE", NULL, NULL, &Query_for_list_of_datatypes},
 	{"UNIQUE", NULL, NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER}, /* for CREATE UNIQUE
@@ -3580,8 +3582,11 @@ psql_completion(const char *text, int start, int end)
 	}
 /* CREATE VARIABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
 	/* Complete CREATE VARIABLE <name> with AS */
+	else if (Matches("CREATE", "TRANSACTION|TRANSACTIONAL"))
+		COMPLETE_WITH("IMMUTABLE", "VARIABLE");
 	else if (TailMatches("CREATE", "VARIABLE", MatchAny) ||
 			 TailMatches("TEMP|TEMPORARY", "VARIABLE", MatchAny) ||
+			 TailMatches("TRANSACTION|TRANSACTIONAL", "VARIABLE", MatchAny) ||
 			 TailMatches("IMMUTABLE", "VARIABLE", MatchAny))
 		COMPLETE_WITH("AS");
 	else if (TailMatches("VARIABLE", MatchAny, "AS"))
diff --git a/src/include/catalog/pg_variable.h b/src/include/catalog/pg_variable.h
index 12a3e38bc8..c7811c2734 100644
--- a/src/include/catalog/pg_variable.h
+++ b/src/include/catalog/pg_variable.h
@@ -60,6 +60,9 @@ CATALOG(pg_variable,9222,VariableRelationId)
 	/* don't allow changes */
 	bool		varisimmutable BKI_DEFAULT(f);
 
+	/* supports transactions */
+	bool		varistransact BKI_DEFAULT(f);
+
 	/* action on transaction end */
 	char		vareoxaction BKI_DEFAULT(n);
 
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9e2d39d1ac..ae3397acb4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3307,6 +3307,7 @@ typedef struct CreateSessionVarStmt
 	bool		if_not_exists;	/* do nothing if it already exists */
 	bool		is_not_null;	/* disallow nulls */
 	bool		is_immutable;	/* don't allow changes */
+	bool		is_transact;	/* supports transactions */
 	Node	   *defexpr;		/* default expression */
 	char		eoxaction;		/* on commit action */
 } CreateSessionVarStmt;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index fd846cf0f9..7152d007db 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -438,6 +438,7 @@ PG_KEYWORD("timestamp", TIMESTAMP, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("to", TO, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("trailing", TRAILING, RESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("transaction", TRANSACTION, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("transactional", TRANSACTIONAL, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("transform", TRANSFORM, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("treat", TREAT, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("trigger", TRIGGER, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index d35107f0a1..2dabffcb78 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5830,20 +5830,20 @@ CREATE ROLE regress_variable_owner;
 SET ROLE TO regress_variable_owner;
 CREATE VARIABLE var1 AS varchar COLLATE "C";
 \dV+ var1
-                                                                         List of variables
- Schema | Name |       Type        | Collation |         Owner          | Nullable | Mutable | Default | Transactional end action | Access privileges | Description 
---------+------+-------------------+-----------+------------------------+----------+---------+---------+--------------------------+-------------------+-------------
- public | var1 | character varying | C         | regress_variable_owner | t        | t       |         |                          |                   | 
+                                                                                 List of variables
+ Schema | Name |       Type        | Collation |         Owner          | Nullable | Mutable | Transactional | Default | Transactional end action | Access privileges | Description 
+--------+------+-------------------+-----------+------------------------+----------+---------+---------------+---------+--------------------------+-------------------+-------------
+ public | var1 | character varying | C         | regress_variable_owner | t        | t       | f             |         |                          |                   | 
 (1 row)
 
 GRANT SELECT ON VARIABLE var1 TO PUBLIC;
 COMMENT ON VARIABLE var1 IS 'some description';
 \dV+ var1
-                                                                                           List of variables
- Schema | Name |       Type        | Collation |         Owner          | Nullable | Mutable | Default | Transactional end action |                Access privileges                 |   Description    
---------+------+-------------------+-----------+------------------------+----------+---------+---------+--------------------------+--------------------------------------------------+------------------
- public | var1 | character varying | C         | regress_variable_owner | t        | t       |         |                          | regress_variable_owner=rw/regress_variable_owner+| some description
-        |      |                   |           |                        |          |         |         |                          | =r/regress_variable_owner                        | 
+                                                                                                   List of variables
+ Schema | Name |       Type        | Collation |         Owner          | Nullable | Mutable | Transactional | Default | Transactional end action |                Access privileges                 |   Description    
+--------+------+-------------------+-----------+------------------------+----------+---------+---------------+---------+--------------------------+--------------------------------------------------+------------------
+ public | var1 | character varying | C         | regress_variable_owner | t        | t       | f             |         |                          | regress_variable_owner=rw/regress_variable_owner+| some description
+        |      |                   |           |                        |          |         |               |         |                          | =r/regress_variable_owner                        | 
 (1 row)
 
 DROP VARIABLE var1;
@@ -6311,9 +6311,9 @@ List of schemas
 (0 rows)
 
 \dV "no.such.variable"
-                                         List of variables
- Schema | Name | Type | Collation | Owner | Nullable | Mutable | Default | Transactional end action 
---------+------+------+-----------+-------+----------+---------+---------+--------------------------
+                                                 List of variables
+ Schema | Name | Type | Collation | Owner | Nullable | Mutable | Transactional | Default | Transactional end action 
+--------+------+------+-----------+-------+----------+---------+---------------+---------+--------------------------
 (0 rows)
 
 -- again, but with dotted schema qualifications.
@@ -6486,9 +6486,9 @@ improper qualified name (too many dotted names): "no.such.schema"."no.such.insta
 \dy "no.such.schema"."no.such.event.trigger"
 improper qualified name (too many dotted names): "no.such.schema"."no.such.event.trigger"
 \dV "no.such.schema"."no.such.variable"
-                                         List of variables
- Schema | Name | Type | Collation | Owner | Nullable | Mutable | Default | Transactional end action 
---------+------+------+-----------+-------+----------+---------+---------+--------------------------
+                                                 List of variables
+ Schema | Name | Type | Collation | Owner | Nullable | Mutable | Transactional | Default | Transactional end action 
+--------+------+------+-----------+-------+----------+---------+---------------+---------+--------------------------
 (0 rows)
 
 -- again, but with current database and dotted schema qualifications.
@@ -6625,9 +6625,9 @@ List of text search templates
 (0 rows)
 
 \dV regression."no.such.schema"."no.such.variable"
-                                         List of variables
- Schema | Name | Type | Collation | Owner | Nullable | Mutable | Default | Transactional end action 
---------+------+------+-----------+-------+----------+---------+---------+--------------------------
+                                                 List of variables
+ Schema | Name | Type | Collation | Owner | Nullable | Mutable | Transactional | Default | Transactional end action 
+--------+------+------+-----------+-------+----------+---------+---------------+---------+--------------------------
 (0 rows)
 
 -- again, but with dotted database and dotted schema qualifications.
diff --git a/src/test/regress/expected/session_variables.out b/src/test/regress/expected/session_variables.out
index 21db37378b..0383132755 100644
--- a/src/test/regress/expected/session_variables.out
+++ b/src/test/regress/expected/session_variables.out
@@ -45,11 +45,11 @@ SET ROLE TO regress_variable_owner;
 CREATE VARIABLE svartest.var1 AS int;
 SET ROLE TO DEFAULT;
 \dV+ svartest.var1
-                                                                                     List of variables
-  Schema  | Name |  Type   | Collation |         Owner          | Nullable | Mutable | Default | Transactional end action |                Access privileges                 | Description 
-----------+------+---------+-----------+------------------------+----------+---------+---------+--------------------------+--------------------------------------------------+-------------
- svartest | var1 | integer |           | regress_variable_owner | t        | t       |         |                          | regress_variable_owner=rw/regress_variable_owner+| 
-          |      |         |           |                        |          |         |         |                          | regress_variable_reader=r/regress_variable_owner | 
+                                                                                             List of variables
+  Schema  | Name |  Type   | Collation |         Owner          | Nullable | Mutable | Transactional | Default | Transactional end action |                Access privileges                 | Description 
+----------+------+---------+-----------+------------------------+----------+---------+---------------+---------+--------------------------+--------------------------------------------------+-------------
+ svartest | var1 | integer |           | regress_variable_owner | t        | t       | f             |         |                          | regress_variable_owner=rw/regress_variable_owner+| 
+          |      |         |           |                        |          |         |               |         |                          | regress_variable_reader=r/regress_variable_owner | 
 (1 row)
 
 DROP VARIABLE svartest.var1;
@@ -1731,3 +1731,103 @@ SET session_variables_ambiguity_warning TO off;
 DROP TABLE public.xxtab;
 DROP SCHEMA xxtab CASCADE;
 NOTICE:  drop cascades to session variable xxtab.avar
+-- test transactional variables
+CREATE TRANSACTION VARIABLE tv AS int DEFAULT 0;
+BEGIN;
+  LET tv = 100;
+  SELECT tv;
+ tv  
+-----
+ 100
+(1 row)
+
+ROLLBACK;
+SELECT tv;
+ tv 
+----
+  0
+(1 row)
+
+LET tv = 100;
+BEGIN;
+  LET tv = 1000;
+COMMIT;
+SELECT tv;
+  tv  
+------
+ 1000
+(1 row)
+
+BEGIN;
+  LET tv = 0;
+  SELECT tv;
+ tv 
+----
+  0
+(1 row)
+
+ROLLBACK;
+SELECT tv;
+  tv  
+------
+ 1000
+(1 row)
+
+-- test subtransactions
+BEGIN;
+  LET tv = 1;
+SAVEPOINT x1;
+  LET tv = 2;
+SAVEPOINT x2;
+  LET tv = 3;
+ROLLBACK TO x2;
+  SELECT tv;
+ tv 
+----
+  2
+(1 row)
+
+  LET tv = 10;
+ROLLBACK TO x1;
+  SELECT tv;
+ tv 
+----
+  1
+(1 row)
+
+ROLLBACK;
+SELECT tv;
+  tv  
+------
+ 1000
+(1 row)
+
+BEGIN;
+  LET tv = 1;
+SAVEPOINT x1;
+  LET tv = 2;
+SAVEPOINT x2;
+  LET tv = 3;
+ROLLBACK TO x2;
+  SELECT tv;
+ tv 
+----
+  2
+(1 row)
+
+  LET tv = 10;
+ROLLBACK TO x1;
+  SELECT tv;
+ tv 
+----
+  1
+(1 row)
+
+COMMIT;
+SELECT tv;
+ tv 
+----
+  1
+(1 row)
+
+DROP VARIABLE tv;
diff --git a/src/test/regress/sql/session_variables.sql b/src/test/regress/sql/session_variables.sql
index e011bdb6ed..8e7548673b 100644
--- a/src/test/regress/sql/session_variables.sql
+++ b/src/test/regress/sql/session_variables.sql
@@ -1182,3 +1182,61 @@ SET session_variables_ambiguity_warning TO off;
 
 DROP TABLE public.xxtab;
 DROP SCHEMA xxtab CASCADE;
+
+-- test transactional variables
+CREATE TRANSACTION VARIABLE tv AS int DEFAULT 0;
+
+BEGIN;
+  LET tv = 100;
+  SELECT tv;
+ROLLBACK;
+
+SELECT tv;
+
+LET tv = 100;
+BEGIN;
+  LET tv = 1000;
+COMMIT;
+
+SELECT tv;
+
+BEGIN;
+  LET tv = 0;
+  SELECT tv;
+ROLLBACK;
+
+SELECT tv;
+
+-- test subtransactions
+
+BEGIN;
+  LET tv = 1;
+SAVEPOINT x1;
+  LET tv = 2;
+SAVEPOINT x2;
+  LET tv = 3;
+ROLLBACK TO x2;
+  SELECT tv;
+  LET tv = 10;
+ROLLBACK TO x1;
+  SELECT tv;
+ROLLBACK;
+
+SELECT tv;
+
+BEGIN;
+  LET tv = 1;
+SAVEPOINT x1;
+  LET tv = 2;
+SAVEPOINT x2;
+  LET tv = 3;
+ROLLBACK TO x2;
+  SELECT tv;
+  LET tv = 10;
+ROLLBACK TO x1;
+  SELECT tv;
+COMMIT;
+
+SELECT tv;
+
+DROP VARIABLE tv;
-- 
2.44.0