v20240801-0013-Implementation-of-NOT-NULL-and-IMMUTABLE-clauses.patch

From fb4ba830c38ced35f37692997cc3a56fe138df29 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <okbob@github.com>
Date: Thu, 6 Jul 2023 14:40:37 +0200
Subject: [PATCH 13/20] Implementation of NOT NULL and IMMUTABLE clauses

Almost trivial patch - psql, pg_dump support
---
 doc/src/sgml/catalogs.sgml                    |  20 +++
 doc/src/sgml/plpgsql.sgml                     |   3 +-
 doc/src/sgml/ref/create_variable.sgml         |  32 +++-
 src/backend/catalog/pg_variable.c             |   9 +-
 src/backend/commands/session_variable.c       |  69 +++++++-
 src/backend/parser/gram.y                     |  41 +++--
 src/bin/pg_dump/pg_dump.c                     |  19 ++-
 src/bin/pg_dump/pg_dump.h                     |   2 +
 src/bin/pg_dump/t/002_pg_dump.pl              |  36 ++++
 src/bin/psql/describe.c                       |   6 +-
 src/bin/psql/tab-complete.c                   |  12 +-
 src/include/catalog/pg_variable.h             |   6 +
 src/include/nodes/parsenodes.h                |   2 +
 src/test/regress/expected/psql.out            |  36 ++--
 .../regress/expected/session_variables.out    | 155 +++++++++++++++++-
 src/test/regress/sql/session_variables.sql    | 122 ++++++++++++++
 16 files changed, 523 insertions(+), 47 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 9d3942bdb4b..4512c4525bf 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9834,6 +9834,26 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
 
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>varnotnull</structfield> <type>boolean</type>
+      </para>
+      <para>
+       True if the session variable doesn't allow null values. The default value is false.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>varisimmutable</structfield> <type>boolean</type>
+      </para>
+      <para>
+       True if the variable is <link linkend="sql-createvariable-immutable">immutable</link> (cannot be modified).
+       The default value is false.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>vareoxaction</structfield> <type>char</type>
        <structfield>varxactendaction</structfield> <type>char</type>
       </para>
       <para>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index c1196c7107d..d646e13fb23 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -6044,7 +6044,8 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE;
      You can consider translating an Oracle package into a schema in
      <productname>PostgreSQL</productname>.  Package functions and procedures
      would then become functions and procedures in that schema, and package
-     variables could be translated to session variables in that schema.
+     variables could be translated to session variables or immutable session
+     variables in that schema.
      (see <xref linkend="ddl-session-variables"\>).
     </para>
    </sect3>
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
index 8187c18e28b..00938743c0d 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -26,8 +26,8 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CREATE [ { TEMPORARY | TEMP } ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ]
-    [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]
+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> ]
+    [ NOT NULL ] [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]
 </synopsis>
  </refsynopsisdiv>
  <refsect1>
@@ -65,6 +65,22 @@ CREATE [ { TEMPORARY | TEMP } ] VARIABLE [ IF NOT EXISTS ] <replaceable class="p
 
   <variablelist>
 
+   <varlistentry id="sql-createvariable-immutable">
+    <term><literal>IMMUTABLE</literal></term>
+    <listitem>
+     <para>
+      The assigned value of the session variable can not be changed.
+      Only if the session variable doesn't have a default value, a single
+      initialization is allowed using the <command>LET</command> command. Once
+      done, no further change is allowed until end of transaction
+      if the session variable was created with clause <literal>ON TRANSACTION
+      END RESET</literal>, or until reset of all session variables by
+      <command>DISCARD VARIABLES</command>, or until reset of all session
+      objects by command <command>DISCARD ALL</command>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry  id="sql-createvariable-if-not-exists">
     <term><literal>IF NOT EXISTS</literal></term>
     <listitem>
@@ -105,6 +121,18 @@ CREATE [ { TEMPORARY | TEMP } ] VARIABLE [ IF NOT EXISTS ] <replaceable class="p
     </listitem>
    </varlistentry>
 
+   <varlistentry id="sql-createvariable-not-null">
+    <term><literal>NOT NULL</literal></term>
+    <listitem>
+     <para>
+      The <literal>NOT NULL</literal> clause forbids setting the session
+      variable to a null value. A session variable created as NOT NULL
+      should not to have a declared default value, and if the variable
+      has not assigned value, then the reading of this variable fails.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="sql-createvariable-default">
     <term><literal>DEFAULT <replaceable>default_expr</replaceable></literal></term>
     <listitem>
diff --git a/src/backend/catalog/pg_variable.c b/src/backend/catalog/pg_variable.c
index 94dfeac4f47..a89c0eccd6a 100644
--- a/src/backend/catalog/pg_variable.c
+++ b/src/backend/catalog/pg_variable.c
@@ -40,6 +40,8 @@ static ObjectAddress create_variable(const char *varName,
 									 Oid varOwner,
 									 Oid varCollation,
 									 bool if_not_exists,
+									 bool not_null,
+									 bool is_immutable,
 									 Node *varDefexpr,
 									 VariableXactEndAction varXactEndAction);
 
@@ -55,6 +57,8 @@ create_variable(const char *varName,
 				Oid varOwner,
 				Oid varCollation,
 				bool if_not_exists,
+				bool not_null,
+				bool is_immutable,
 				Node *varDefexpr,
 				VariableXactEndAction varXactEndAction)
 {
@@ -117,6 +121,8 @@ create_variable(const char *varName,
 	values[Anum_pg_variable_vartypmod - 1] = Int32GetDatum(varTypmod);
 	values[Anum_pg_variable_varowner - 1] = ObjectIdGetDatum(varOwner);
 	values[Anum_pg_variable_varcollation - 1] = ObjectIdGetDatum(varCollation);
+	values[Anum_pg_variable_varnotnull - 1] = BoolGetDatum(not_null);
+	values[Anum_pg_variable_varisimmutable - 1] = BoolGetDatum(is_immutable);
 	values[Anum_pg_variable_varxactendaction - 1] = CharGetDatum(varXactEndAction);
 
 	if (varDefexpr)
@@ -202,7 +208,6 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
 	ObjectAddress variable;
 	Node	   *cooked_default = NULL;
 
-	/* Check consistency of arguments */
 	if (stmt->XactEndAction == VARIABLE_XACTEND_DROP
 		&& stmt->variable->relpersistence != RELPERSISTENCE_TEMP)
 		ereport(ERROR,
@@ -259,6 +264,8 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
 							   varowner,
 							   collation,
 							   stmt->if_not_exists,
+							   stmt->not_null,
+							   stmt->is_immutable,
 							   cooked_default,
 							   stmt->XactEndAction);
 
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 58a6d21f9aa..fa5adf631b8 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -84,6 +84,9 @@ typedef struct SVariableData
 	void	   *domain_check_extra;
 	LocalTransactionId domain_check_extra_lxid;
 
+	bool		not_null;
+	bool		is_immutable;
+
 	bool		reset_at_eox;
 
 	/*
@@ -102,6 +105,9 @@ typedef struct SVariableData
 	bool		is_valid;
 
 	uint32		hashvalue;		/* used for pairing sinval message */
+
+	/* true, when the value is already set, and cannot be changed more */
+	bool		protect_value;
 } SVariableData;
 
 typedef SVariableData *SVariable;
@@ -567,6 +573,23 @@ eval_assign_defexpr(SVariable svar, HeapTuple tup)
 
 			MemoryContextSwitchTo(oldcxt);
 		}
+		else
+		{
+			/*
+			 * Raise an error if this is a NOT NULL variable but the result of
+			 * DEFAULT expression is NULL.
+			 */
+			if (svar->not_null)
+				ereport(ERROR,
+						(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+						 errmsg("null value is not allowed for NOT NULL session variable \"%s.%s\"",
+								get_namespace_name(get_session_variable_namespace(svar->varid)),
+								get_session_variable_name(svar->varid)),
+						 errdetail("The result of DEFAULT expression is NULL.")));
+		}
+
+		if (svar->is_immutable)
+			svar->protect_value = true;
 
 		FreeExecutorState(estate);
 	}
@@ -597,6 +620,9 @@ setup_session_variable(SVariable svar, Oid varid, bool is_write)
 
 	get_typlenbyval(svar->typid, &svar->typlen, &svar->typbyval);
 
+	svar->not_null = varform->varnotnull;
+	svar->is_immutable = varform->varisimmutable;
+
 	svar->is_domain = (get_typtype(varform->vartype) == TYPTYPE_DOMAIN);
 	svar->domain_check_extra = NULL;
 	svar->domain_check_extra_lxid = InvalidLocalTransactionId;
@@ -626,9 +652,31 @@ setup_session_variable(SVariable svar, Oid varid, bool is_write)
 	svar->hashvalue = GetSysCacheHashValue1(VARIABLEOID,
 											ObjectIdGetDatum(varid));
 
-	if (!is_write)
+	svar->protect_value = false;
+
+	/*
+	 * When the variable is marked as IMMUTABLE, we prefer to evaluate
+	 * possible DEFAULT before write op. In this case we want to protect
+	 * default value against any overwrite.
+	 */
+	if (!is_write ||
+		svar->is_immutable)
+	{
 		eval_assign_defexpr(svar, tup);
 
+		/*
+		 * Raise an error if this is a NOT NULL variable without default
+		 * expression.
+		 */
+		if (svar->isnull && svar->not_null)
+			ereport(ERROR,
+					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+					 errmsg("null value is not allowed for NOT NULL session variable \"%s.%s\"",
+							get_namespace_name(get_session_variable_namespace(varid)),
+							get_session_variable_name(varid)),
+					 errdetail("The session variable was not initialized yet.")));
+	}
+
 	ReleaseSysCache(tup);
 }
 
@@ -648,6 +696,21 @@ set_session_variable(SVariable svar, Datum value, bool isnull)
 	Assert(svar);
 	Assert(!isnull || value == (Datum) 0);
 
+	if (svar->protect_value)
+		ereport(ERROR,
+				(errcode(ERRCODE_ERROR_IN_ASSIGNMENT),
+				 errmsg("session variable \"%s.%s\" is declared IMMUTABLE",
+						get_namespace_name(get_session_variable_namespace(svar->varid)),
+						get_session_variable_name(svar->varid))));
+
+	/* don't allow assignment of null to NOT NULL variable */
+	if (isnull && svar->not_null)
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("null value is not allowed for NOT NULL session variable \"%s.%s\"",
+						get_namespace_name(get_session_variable_namespace(svar->varid)),
+						get_session_variable_name(svar->varid))));
+
 	/*
 	 * Use typbyval, typbylen from session variable only when they are
 	 * trustworthy (the invalidation message was not accepted for this
@@ -688,6 +751,10 @@ set_session_variable(SVariable svar, Datum value, bool isnull)
 
 	svar->value = newval;
 	svar->isnull = isnull;
+
+	/* don't allow more changes of value when variable is IMMUTABLE */
+	if (svar->is_immutable)
+		svar->protect_value = true;
 }
 
 /*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9865c651f67..aae22b45673 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -683,6 +683,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
 
 /*
  * Non-keyword token types.  These are hard-wired into the "flex" lexer.
@@ -5223,27 +5224,31 @@ create_extension_opt_item:
  *****************************************************************************/
 
 CreateSessionVarStmt:
-			CREATE OptTemp VARIABLE qualified_name opt_as Typename opt_collate_clause OptSessionVarDefExpr XactEndActionOption
+			CREATE OptTemp OptImmutable VARIABLE qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr XactEndActionOption
 				{
 					CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
-					$4->relpersistence = $2;
-					n->variable = $4;
-					n->typeName = $6;
-					n->collClause = (CollateClause *) $7;
-					n->defexpr = $8;
-					n->XactEndAction = $9;
+					$5->relpersistence = $2;
+					n->is_immutable = $3;
+					n->variable = $5;
+					n->typeName = $7;
+					n->collClause = (CollateClause *) $8;
+					n->not_null = $9;
+					n->defexpr = $10;
+					n->XactEndAction = $11;
 					n->if_not_exists = false;
 					$$ = (Node *) n;
 				}
-			| CREATE OptTemp VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename opt_collate_clause OptSessionVarDefExpr XactEndActionOption
+			| CREATE OptTemp OptImmutable VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr XactEndActionOption
 				{
 					CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
-					$7->relpersistence = $2;
-					n->variable = $7;
-					n->typeName = $9;
-					n->collClause = (CollateClause *) $10;
-					n->defexpr = $11;
-					n->XactEndAction = $12;
+					$8->relpersistence = $2;
+					n->is_immutable = $3;
+					n->variable = $8;
+					n->typeName = $10;
+					n->collClause = (CollateClause *) $11;
+					n->not_null = $12;
+					n->defexpr = $13;
+					n->XactEndAction = $14;
 					n->if_not_exists = true;
 					$$ = (Node *) n;
 				}
@@ -5263,6 +5268,14 @@ XactEndActionOption:  ON COMMIT DROP				{ $$ = VARIABLE_XACTEND_DROP; }
 		;
 
 
+OptNotNull: NOT NULL_P								{ $$ = true; }
+			| /* EMPTY */							{ $$ = false; }
+		;
+
+OptImmutable: IMMUTABLE								{ $$ = true; }
+			| /* EMPTY */							{ $$ = false; }
+		;
+
 /*****************************************************************************
  *
  * ALTER EXTENSION name UPDATE [ TO version ]
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6a71a7b0571..4c22ce9553b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5334,6 +5334,8 @@ getVariables(Archive *fout)
 	int			i_varxactendaction;
 	int			i_varowner;
 	int			i_varcollation;
+	int			i_varnotnull;
+	int			i_varisimmutable;
 	int			i_varacl;
 	int			i_acldefault;
 	int			i,
@@ -5356,6 +5358,8 @@ getVariables(Archive *fout)
 					  "            THEN v.varcollation\n"
 					  "            ELSE 0\n"
 					  "       END AS varcollation,\n"
+					  "       v.varnotnull,\n"
+					  "       v.varisimmutable,\n"
 					  "       pg_catalog.pg_get_expr(v.vardefexpr,0) as vardefexpr,\n"
 					  "       v.varowner, v.varacl,\n"
 					  "       acldefault('V', v.varowner) AS acldefault\n"
@@ -5376,6 +5380,8 @@ getVariables(Archive *fout)
 	i_vardefexpr = PQfnumber(res, "vardefexpr");
 	i_varxactendaction = PQfnumber(res, "varxactendaction");
 	i_varcollation = PQfnumber(res, "varcollation");
+	i_varnotnull = PQfnumber(res, "varnotnull");
+	i_varisimmutable = PQfnumber(res, "varisimmutable");
 
 	i_varowner = PQfnumber(res, "varowner");
 	i_varacl = PQfnumber(res, "varacl");
@@ -5402,6 +5408,8 @@ getVariables(Archive *fout)
 			pg_strdup(PQgetvalue(res, i, i_varxactendaction));
 
 		varinfo[i].varcollation = atooid(PQgetvalue(res, i, i_varcollation));
+		varinfo[i].varnotnull = *(PQgetvalue(res, i, i_varnotnull)) == 't';
+		varinfo[i].varisimmutable = *(PQgetvalue(res, i, i_varisimmutable)) == 't';
 
 		varinfo[i].dacl.acl = pg_strdup(PQgetvalue(res, i, i_varacl));
 		varinfo[i].dacl.acldefault = pg_strdup(PQgetvalue(res, i, i_acldefault));
@@ -5448,7 +5456,9 @@ dumpVariable(Archive *fout, const VariableInfo *varinfo)
 	const char *vartypname;
 	const char *vardefexpr;
 	const char *varxactendaction;
+	const char *varisimmutable;
 	Oid			varcollation;
+	bool		varnotnull;
 
 	/* Skip if not to be dumped */
 	if (!varinfo->dobj.dump || dopt->dataOnly)
@@ -5462,12 +5472,14 @@ dumpVariable(Archive *fout, const VariableInfo *varinfo)
 	vardefexpr = varinfo->vardefexpr;
 	varxactendaction = varinfo->varxactendaction;
 	varcollation = varinfo->varcollation;
+	varnotnull = varinfo->varnotnull;
+	varisimmutable = varinfo->varisimmutable ? "IMMUTABLE " : "";
 
 	appendPQExpBuffer(delq, "DROP VARIABLE %s;\n",
 					  qualvarname);
 
-	appendPQExpBuffer(query, "CREATE VARIABLE %s AS %s",
-					  qualvarname, vartypname);
+	appendPQExpBuffer(query, "CREATE %sVARIABLE %s AS %s",
+					  varisimmutable, qualvarname, vartypname);
 
 	if (OidIsValid(varcollation))
 	{
@@ -5479,6 +5491,9 @@ dumpVariable(Archive *fout, const VariableInfo *varinfo)
 							  fmtQualifiedDumpable(coll));
 	}
 
+	if (varnotnull)
+		appendPQExpBuffer(query, " NOT NULL");
+
 	if (vardefexpr)
 		appendPQExpBuffer(query, " DEFAULT %s",
 						  vardefexpr);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index e2cb06cc883..cbb06b66f74 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -710,6 +710,8 @@ typedef struct _VariableInfo
 	char	   *initrvaracl;
 	Oid			varcollation;
 	const char *rolname;		/* name of owner, or empty string */
+	bool		varnotnull;
+	bool		varisimmutable;
 } VariableInfo;
 
 /*
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 04fe9843ae6..9ebfd6dba72 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3995,6 +3995,42 @@ my %tests = (
 		},
 	},
 
+	'CREATE IMMUTABLE VARIABLE test_variable' => {
+		all_runs     => 1,
+		catch_all    => 'CREATE ... commands',
+		create_order => 61,
+		create_sql   => 'CREATE IMMUTABLE VARIABLE dump_test.variable5 AS integer',
+		regexp => qr/^
+			\QCREATE IMMUTABLE VARIABLE dump_test.variable5 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 IMMUTABLE VARIABLE test_variable NOT NULL DEFAULT' => {
+		all_runs     => 1,
+		catch_all    => 'CREATE ... commands',
+		create_order => 61,
+		create_sql   => 'CREATE IMMUTABLE VARIABLE dump_test.variable7 AS integer NOT NULL DEFAULT 10',
+		regexp => qr/^
+			\QCREATE IMMUTABLE VARIABLE dump_test.variable7 AS integer NOT NULL DEFAULT 10;\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 566559316ee..15ca777996a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5159,7 +5159,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};
+	static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false, false};
 
 	if (pset.sversion < 180000)
 	{
@@ -5180,6 +5180,8 @@ listVariables(const char *pattern, bool verbose)
 					  "  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
 					  "   WHERE c.oid = v.varcollation AND bt.oid = v.vartype AND v.varcollation <> bt.typcollation) as \"%s\",\n"
 					  "  pg_catalog.pg_get_userbyid(v.varowner) as \"%s\",\n"
+					  "  NOT v.varnotnull as \"%s\",\n"
+					  "  NOT v.varisimmutable as \"%s\",\n"
 					  "  pg_catalog.pg_get_expr(v.vardefexpr, 0) as \"%s\",\n"
 					  "  CASE v.varxactendaction\n"
 					  "    WHEN 'd' THEN 'ON COMMIT DROP'\n"
@@ -5190,6 +5192,8 @@ listVariables(const char *pattern, bool verbose)
 					  gettext_noop("Type"),
 					  gettext_noop("Collation"),
 					  gettext_noop("Owner"),
+					  gettext_noop("Nullable"),
+					  gettext_noop("Mutable"),
 					  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 ec54abb9a73..4d6a943712f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1252,6 +1252,8 @@ static const pgsql_thing_t words_after_create[] = {
 	{"FOREIGN TABLE", NULL, NULL, NULL},
 	{"FUNCTION", NULL, NULL, Query_for_list_of_functions},
 	{"GROUP", Query_for_list_of_roles},
+	{"IMMUTABLE VARIABLE", NULL, NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER}, /* for CREATE IMMUTABLE
+																					 * VARIABLE ... */
 	{"INDEX", NULL, NULL, &Query_for_list_of_indexes},
 	{"LANGUAGE", Query_for_list_of_languages},
 	{"LARGE OBJECT", NULL, NULL, NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
@@ -3277,7 +3279,8 @@ psql_completion(const char *text, int start, int end)
 /* 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", "VARIABLE", "VIEW");
+		COMPLETE_WITH("IMMUTABLE VARIABLE", "SEQUENCE", "TABLE", "VARIABLE",
+					  "VIEW");
 	/* Complete "CREATE UNLOGGED" with TABLE or SEQUENCE */
 	else if (TailMatches("CREATE", "UNLOGGED"))
 		COMPLETE_WITH("TABLE", "SEQUENCE");
@@ -3594,7 +3597,8 @@ 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 (TailMatches("CREATE", "VARIABLE", MatchAny) ||
-			 TailMatches("TEMP|TEMPORARY", "VARIABLE", MatchAny))
+			 TailMatches("TEMP|TEMPORARY", "VARIABLE", MatchAny) ||
+			 TailMatches("IMMUTABLE", "VARIABLE", MatchAny))
 		COMPLETE_WITH("AS");
 	else if (TailMatches("VARIABLE", MatchAny, "AS"))
 		/* Complete CREATE VARIABLE <name> with AS types */
@@ -4242,6 +4246,10 @@ psql_completion(const char *text, int start, int end)
 	else if (TailMatches("FROM", "SERVER", MatchAny, "INTO", MatchAny))
 		COMPLETE_WITH("OPTIONS (");
 
+/* IMMUTABLE -- can be expend to IMMUTABLE VARIABLE */
+	else if (TailMatches("CREATE", "IMMUTABLE"))
+		COMPLETE_WITH("VARIABLE");
+
 /* INSERT --- can be inside EXPLAIN, RULE, etc */
 	/* Complete NOT MATCHED THEN INSERT */
 	else if (TailMatches("NOT", "MATCHED", "THEN", "INSERT"))
diff --git a/src/include/catalog/pg_variable.h b/src/include/catalog/pg_variable.h
index e8bfeebed11..68bc49a0e27 100644
--- a/src/include/catalog/pg_variable.h
+++ b/src/include/catalog/pg_variable.h
@@ -55,6 +55,12 @@ CATALOG(pg_variable,9222,VariableRelationId)
 	/* typmod for variable's type */
 	int32		vartypmod BKI_DEFAULT(-1);
 
+	/* don't allow NULL */
+	bool		varnotnull BKI_DEFAULT(f);
+
+	/* don't allow changes */
+	bool		varisimmutable BKI_DEFAULT(f);
+
 	/* action on transaction end */
 	char		varxactendaction BKI_DEFAULT(n);
 
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ed0a214ffe4..76d7656d7f7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3455,6 +3455,8 @@ typedef struct CreateSessionVarStmt
 	TypeName   *typeName;		/* the type of variable */
 	CollateClause *collClause;
 	bool		if_not_exists;	/* do nothing if it already exists */
+	bool		not_null;		/* disallow nulls */
+	bool		is_immutable;	/* don't allow changes */
 	Node	   *defexpr;		/* default expression */
 	char		XactEndAction;	/* on transaction end action */
 } CreateSessionVarStmt;
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index ca131e568eb..f7082639605 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5833,20 +5833,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          | Default | Transactional end action | Access privileges | Description 
---------+------+-------------------+-----------+------------------------+---------+--------------------------+-------------------+-------------
- public | var1 | character varying | C         | regress_variable_owner |         |                          |                   | 
+                                                                         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       |         |                          |                   | 
 (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          | Default | Transactional end action |                Access privileges                 |   Description    
---------+------+-------------------+-----------+------------------------+---------+--------------------------+--------------------------------------------------+------------------
- public | var1 | character varying | C         | regress_variable_owner |         |                          | regress_variable_owner=rw/regress_variable_owner+| some description
-        |      |                   |           |                        |         |                          | =r/regress_variable_owner                        | 
+                                                                                           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                        | 
 (1 row)
 
 DROP VARIABLE var1;
@@ -6314,9 +6314,9 @@ List of schemas
 (0 rows)
 
 \dV "no.such.variable"
-                               List of variables
- Schema | Name | Type | Collation | Owner | Default | Transactional end action 
---------+------+------+-----------+-------+---------+--------------------------
+                                         List of variables
+ Schema | Name | Type | Collation | Owner | Nullable | Mutable | Default | Transactional end action 
+--------+------+------+-----------+-------+----------+---------+---------+--------------------------
 (0 rows)
 
 -- again, but with dotted schema qualifications.
@@ -6489,9 +6489,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 | Default | Transactional end action 
---------+------+------+-----------+-------+---------+--------------------------
+                                         List of variables
+ Schema | Name | Type | Collation | Owner | Nullable | Mutable | Default | Transactional end action 
+--------+------+------+-----------+-------+----------+---------+---------+--------------------------
 (0 rows)
 
 -- again, but with current database and dotted schema qualifications.
@@ -6628,9 +6628,9 @@ List of text search templates
 (0 rows)
 
 \dV regression."no.such.schema"."no.such.variable"
-                               List of variables
- Schema | Name | Type | Collation | Owner | Default | Transactional end action 
---------+------+------+-----------+-------+---------+--------------------------
+                                         List of variables
+ Schema | Name | Type | Collation | Owner | Nullable | Mutable | 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 f291eaa156c..14d971ac27f 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          | Default | Transactional end action |                Access privileges                 | Description 
-----------+------+---------+-----------+------------------------+---------+--------------------------+--------------------------------------------------+-------------
- svartest | var1 | integer |           | regress_variable_owner |         |                          | regress_variable_owner=rw/regress_variable_owner+| 
-          |      |         |           |                        |         |                          | regress_variable_reader=r/regress_variable_owner | 
+                                                                                     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 | 
 (1 row)
 
 DROP VARIABLE svartest.var1;
@@ -1604,3 +1604,148 @@ SELECT var1;
 
 DROP VARIABLE var1;
 DROP FUNCTION vartest_fx();
+-- test NOT NULL
+-- should be ok
+CREATE VARIABLE var1 AS int NOT NULL;
+-- should to fail
+SELECT var1;
+ERROR:  null value is not allowed for NOT NULL session variable "public.var1"
+DETAIL:  The session variable was not initialized yet.
+--should be ok
+LET var1 = 10;
+SELECT var1;
+ var1 
+------
+   10
+(1 row)
+
+DROP VARIABLE var1;
+-- should be ok
+CREATE VARIABLE var1 AS int NOT NULL DEFAULT 0;
+--should be ok
+SELECT var1;
+ var1 
+------
+    0
+(1 row)
+
+-- should be ok
+LET var1 = 10;
+SELECT var1;
+ var1 
+------
+   10
+(1 row)
+
+DISCARD VARIABLES;
+-- should to fail
+LET var1 = NULL;
+ERROR:  null value is not allowed for NOT NULL session variable "public.var1"
+DROP VARIABLE var1;
+-- test NOT NULL
+CREATE OR REPLACE FUNCTION vartest_fx()
+RETURNS int AS $$
+BEGIN
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+CREATE VARIABLE var1 AS int NOT NULL DEFAULT vartest_fx();
+-- should to fail
+SELECT var1;
+ERROR:  null value is not allowed for NOT NULL session variable "public.var1"
+DETAIL:  The result of DEFAULT expression is NULL.
+DISCARD VARIABLES;
+-- should be ok
+LET var1 = 10;
+SELECT var1;
+ var1 
+------
+   10
+(1 row)
+
+CREATE OR REPLACE FUNCTION vartest_fx()
+RETURNS int AS $$
+BEGIN
+  RETURN 0;
+END;
+$$ LANGUAGE plpgsql;
+DISCARD VARIABLES;
+-- should be ok
+SELECT var1;
+ var1 
+------
+    0
+(1 row)
+
+DROP VARIABLE var1;
+DROP FUNCTION vartest_fx();
+-- test IMMUTBLE
+CREATE IMMUTABLE VARIABLE var1 AS int;
+-- should be ok
+SELECT var1;
+ var1 
+------
+     
+(1 row)
+
+-- first write should ok
+-- should be ok
+LET var1 = 10;
+-- should fail
+LET var1 = 20;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+DISCARD VARIABLES;
+-- should be ok
+LET var1 = 10;
+-- should fail
+LET var1 = 20;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+DISCARD VARIABLES;
+-- should be ok
+SELECT var1;
+ var1 
+------
+     
+(1 row)
+
+-- should be ok
+LET var1 = NULL;
+-- should fail
+LET var1 = 20;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+DROP VARIABLE var1;
+CREATE IMMUTABLE VARIABLE var1 AS int DEFAULT 10;
+-- don't allow change when variable has DEFAULT value
+-- should to fail
+LET var1 = 20;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+DISCARD VARIABLES;
+-- should be ok
+SELECT var1;
+ var1 
+------
+   10
+(1 row)
+
+-- should fail
+LET var1 = 20;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+DROP VARIABLE var1;
+-- should be ok
+CREATE IMMUTABLE VARIABLE var1 AS INT NOT NULL DEFAULT 10;
+-- should to fail
+LET var1 = 10;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+LET var1 = 20;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+-- should be ok
+SELECT var1;
+ var1 
+------
+   10
+(1 row)
+
+-- should to fail
+LET var1 = 30;
+ERROR:  session variable "public.var1" is declared IMMUTABLE
+DROP VARIABLE var1;
diff --git a/src/test/regress/sql/session_variables.sql b/src/test/regress/sql/session_variables.sql
index 521ae48eda2..5a3588ad368 100644
--- a/src/test/regress/sql/session_variables.sql
+++ b/src/test/regress/sql/session_variables.sql
@@ -1073,3 +1073,125 @@ SELECT var1;
 
 DROP VARIABLE var1;
 DROP FUNCTION vartest_fx();
+
+-- test NOT NULL
+-- should be ok
+CREATE VARIABLE var1 AS int NOT NULL;
+-- should to fail
+SELECT var1;
+
+--should be ok
+LET var1 = 10;
+SELECT var1;
+
+DROP VARIABLE var1;
+
+-- should be ok
+CREATE VARIABLE var1 AS int NOT NULL DEFAULT 0;
+
+--should be ok
+SELECT var1;
+
+-- should be ok
+LET var1 = 10;
+SELECT var1;
+
+DISCARD VARIABLES;
+
+-- should to fail
+LET var1 = NULL;
+
+DROP VARIABLE var1;
+
+-- test NOT NULL
+CREATE OR REPLACE FUNCTION vartest_fx()
+RETURNS int AS $$
+BEGIN
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE VARIABLE var1 AS int NOT NULL DEFAULT vartest_fx();
+
+-- should to fail
+SELECT var1;
+
+DISCARD VARIABLES;
+
+-- should be ok
+LET var1 = 10;
+SELECT var1;
+
+CREATE OR REPLACE FUNCTION vartest_fx()
+RETURNS int AS $$
+BEGIN
+  RETURN 0;
+END;
+$$ LANGUAGE plpgsql;
+
+DISCARD VARIABLES;
+
+-- should be ok
+SELECT var1;
+
+DROP VARIABLE var1;
+DROP FUNCTION vartest_fx();
+
+-- test IMMUTBLE
+CREATE IMMUTABLE VARIABLE var1 AS int;
+
+-- should be ok
+SELECT var1;
+-- first write should ok
+-- should be ok
+LET var1 = 10;
+-- should fail
+LET var1 = 20;
+
+DISCARD VARIABLES;
+
+-- should be ok
+LET var1 = 10;
+-- should fail
+LET var1 = 20;
+
+DISCARD VARIABLES;
+
+-- should be ok
+SELECT var1;
+-- should be ok
+LET var1 = NULL;
+-- should fail
+LET var1 = 20;
+
+DROP VARIABLE var1;
+
+CREATE IMMUTABLE VARIABLE var1 AS int DEFAULT 10;
+
+-- don't allow change when variable has DEFAULT value
+-- should to fail
+LET var1 = 20;
+
+DISCARD VARIABLES;
+
+-- should be ok
+SELECT var1;
+-- should fail
+LET var1 = 20;
+
+DROP VARIABLE var1;
+
+-- should be ok
+CREATE IMMUTABLE VARIABLE var1 AS INT NOT NULL DEFAULT 10;
+
+-- should to fail
+LET var1 = 10;
+LET var1 = 20;
+
+-- should be ok
+SELECT var1;
+
+-- should to fail
+LET var1 = 30;
+
+DROP VARIABLE var1;
-- 
2.45.2