v20250603-2-0005-support-of-session-variables-for-pg_dump.patch

From 3e2342f9d4191cdb1e6377dda1385c8c24e9238d Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Wed, 28 May 2025 22:54:09 +0200
Subject: [PATCH 05/15] support of session variables for pg_dump

This patch enhancing pg_dump to support session variables.
---
 src/bin/pg_dump/common.c             |   3 +
 src/bin/pg_dump/dumputils.c          |   6 +
 src/bin/pg_dump/pg_backup.h          |   2 +
 src/bin/pg_dump/pg_backup_archiver.c |   9 ++
 src/bin/pg_dump/pg_dump.c            | 190 +++++++++++++++++++++++++++
 src/bin/pg_dump/pg_dump.h            |  15 +++
 src/bin/pg_dump/pg_dump_sort.c       |   7 +
 src/bin/pg_dump/t/002_pg_dump.pl     |  64 +++++++++
 src/tools/pgindent/typedefs.list     |   1 +
 9 files changed, 297 insertions(+)

diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index aa1589e3331..86360bd6d95 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -246,6 +246,9 @@ getSchemaData(Archive *fout, int *numTablesPtr)
 	pg_log_info("reading subscription membership of tables");
 	getSubscriptionTables(fout);
 
+	pg_log_info("reading variables");
+	getVariables(fout);
+
 	free(inhinfo);				/* not needed any longer */
 
 	*numTablesPtr = numTables;
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 73ce34346b2..cbca7c343a1 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -514,6 +514,12 @@ do { \
 		CONVERT_PRIV('r', "SELECT");
 		CONVERT_PRIV('w', "UPDATE");
 	}
+	else if (strcmp(type, "VARIABLE") == 0 ||
+			 strcmp(type, "VARIABLES") == 0)
+	{
+		CONVERT_PRIV('r', "SELECT");
+		CONVERT_PRIV('w', "UPDATE");
+	}
 	else
 		abort();
 
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index af0007fb6d2..76356524af2 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -134,12 +134,14 @@ typedef struct _restoreOptions
 	int			selFunction;
 	int			selTrigger;
 	int			selTable;
+	int			selVariable;
 	SimpleStringList indexNames;
 	SimpleStringList functionNames;
 	SimpleStringList schemaNames;
 	SimpleStringList schemaExcludeNames;
 	SimpleStringList triggerNames;
 	SimpleStringList tableNames;
+	SimpleStringList variableNames;
 
 	int			useDB;
 	ConnParams	cparams;		/* parameters to use if useDB */
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 175fe9c4273..9bf77642702 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3179,6 +3179,14 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
 					!simple_string_list_member(&ropt->triggerNames, te->tag))
 					return 0;
 			}
+			else if (strcmp(te->desc, "VARIABLE") == 0)
+			{
+				if (!ropt->selVariable)
+					return 0;
+				if (ropt->variableNames.head != NULL &&
+					!simple_string_list_member(&ropt->variableNames, te->tag))
+					return 0;
+			}
 			else
 				return 0;
 		}
@@ -3742,6 +3750,7 @@ _getObjectDescription(PQExpBuffer buf, const TocEntry *te)
 		strcmp(type, "TEXT SEARCH DICTIONARY") == 0 ||
 		strcmp(type, "TEXT SEARCH CONFIGURATION") == 0 ||
 		strcmp(type, "TYPE") == 0 ||
+		strcmp(type, "VARIABLE") == 0 ||
 		strcmp(type, "VIEW") == 0 ||
 	/* non-schema-specified objects */
 		strcmp(type, "DATABASE") == 0 ||
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 37432e66efd..5d8762538bd 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -370,6 +370,7 @@ static void dumpPublication(Archive *fout, const PublicationInfo *pubinfo);
 static void dumpPublicationTable(Archive *fout, const PublicationRelInfo *pubrinfo);
 static void dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo);
 static void dumpSubscriptionTable(Archive *fout, const SubRelInfo *subrinfo);
+static void dumpVariable(Archive *fout, const VariableInfo *varinfo);
 static void dumpDatabase(Archive *fout);
 static void dumpDatabaseConfig(Archive *AH, PQExpBuffer outbuf,
 							   const char *dbname, Oid dboid);
@@ -5512,6 +5513,188 @@ get_next_possible_free_pg_type_oid(Archive *fout, PQExpBuffer upgrade_query)
 	return next_possible_free_oid;
 }
 
+/*
+ * getVariables
+ *	  get information about variables
+ */
+void
+getVariables(Archive *fout)
+{
+	PQExpBuffer query;
+	PGresult   *res;
+	VariableInfo *varinfo;
+	int			i_tableoid;
+	int			i_oid;
+	int			i_varname;
+	int			i_varnamespace;
+	int			i_vartype;
+	int			i_vartypname;
+	int			i_varowner;
+	int			i_varcollation;
+	int			i_varacl;
+	int			i_acldefault;
+	int			i,
+				ntups;
+
+	if (fout->remoteVersion < 180000)
+		return;
+
+	query = createPQExpBuffer();
+
+	/* get the variables in current database */
+	appendPQExpBuffer(query,
+					  "SELECT v.tableoid, v.oid, v.varname,\n"
+					  "       v.varnamespace, v.vartype,\n"
+					  "       pg_catalog.format_type(v.vartype, v.vartypmod) as vartypname,\n"
+					  "       CASE WHEN v.varcollation <> t.typcollation "
+					  "            THEN v.varcollation\n"
+					  "            ELSE 0\n"
+					  "       END AS varcollation,\n"
+					  "       v.varowner, v.varacl,\n"
+					  "       acldefault('V', v.varowner) AS acldefault\n"
+					  "FROM pg_catalog.pg_variable v\n"
+					  "JOIN pg_catalog.pg_type t "
+					  "ON (v.vartype = t.oid)");
+
+	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+	ntups = PQntuples(res);
+
+	i_tableoid = PQfnumber(res, "tableoid");
+	i_oid = PQfnumber(res, "oid");
+	i_varname = PQfnumber(res, "varname");
+	i_varnamespace = PQfnumber(res, "varnamespace");
+	i_vartype = PQfnumber(res, "vartype");
+	i_vartypname = PQfnumber(res, "vartypname");
+	i_varcollation = PQfnumber(res, "varcollation");
+
+	i_varowner = PQfnumber(res, "varowner");
+	i_varacl = PQfnumber(res, "varacl");
+	i_acldefault = PQfnumber(res, "acldefault");
+
+	varinfo = pg_malloc(ntups * sizeof(VariableInfo));
+
+	for (i = 0; i < ntups; i++)
+	{
+		TypeInfo   *vtype;
+
+		varinfo[i].dobj.objType = DO_VARIABLE;
+		varinfo[i].dobj.catId.tableoid =
+			atooid(PQgetvalue(res, i, i_tableoid));
+		varinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
+		AssignDumpId(&varinfo[i].dobj);
+		varinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_varname));
+		varinfo[i].dobj.namespace =
+			findNamespace(atooid(PQgetvalue(res, i, i_varnamespace)));
+
+		varinfo[i].vartype = atooid(PQgetvalue(res, i, i_vartype));
+		varinfo[i].vartypname = pg_strdup(PQgetvalue(res, i, i_vartypname));
+		varinfo[i].varcollation = atooid(PQgetvalue(res, i, i_varcollation));
+
+		varinfo[i].dacl.acl = pg_strdup(PQgetvalue(res, i, i_varacl));
+		varinfo[i].dacl.acldefault = pg_strdup(PQgetvalue(res, i, i_acldefault));
+		varinfo[i].dacl.privtype = 0;
+		varinfo[i].dacl.initprivs = NULL;
+		varinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_varowner));
+
+		/* do not try to dump ACL if no ACL exists */
+		if (!PQgetisnull(res, i, i_varacl))
+			varinfo[i].dobj.components |= DUMP_COMPONENT_ACL;
+
+		if (strlen(varinfo[i].rolname) == 0)
+			pg_log_warning("owner of variable \"%s\" appears to be invalid",
+						   varinfo[i].dobj.name);
+
+		/* decide whether we want to dump it */
+		selectDumpableObject(&(varinfo[i].dobj), fout);
+
+		vtype = findTypeByOid(varinfo[i].vartype);
+		addObjectDependency(&varinfo[i].dobj, vtype->dobj.dumpId);
+	}
+	PQclear(res);
+
+	destroyPQExpBuffer(query);
+}
+
+/*
+ * dumpVariable
+ *	  dump the definition of the given session variable
+ */
+static void
+dumpVariable(Archive *fout, const VariableInfo *varinfo)
+{
+	DumpOptions *dopt = fout->dopt;
+
+	PQExpBuffer delq;
+	PQExpBuffer query;
+	char	   *qualvarname;
+	const char *vartypname;
+	Oid			varcollation;
+
+	/* skip if not to be dumped */
+	if (!varinfo->dobj.dump || !dopt->dumpSchema)
+		return;
+
+	delq = createPQExpBuffer();
+	query = createPQExpBuffer();
+
+	qualvarname = pg_strdup(fmtQualifiedDumpable(varinfo));
+	vartypname = varinfo->vartypname;
+	varcollation = varinfo->varcollation;
+
+	appendPQExpBuffer(delq, "DROP VARIABLE %s;\n",
+					  qualvarname);
+
+	appendPQExpBuffer(query, "CREATE VARIABLE %s AS %s",
+					  qualvarname, vartypname);
+
+	if (OidIsValid(varcollation))
+	{
+		CollInfo   *coll;
+
+		coll = findCollationByOid(varcollation);
+		if (coll)
+			appendPQExpBuffer(query, " COLLATE %s",
+							  fmtQualifiedDumpable(coll));
+	}
+
+	appendPQExpBuffer(query, ";\n");
+
+	if (varinfo->dobj.dump & DUMP_COMPONENT_DEFINITION)
+		ArchiveEntry(fout, varinfo->dobj.catId, varinfo->dobj.dumpId,
+					 ARCHIVE_OPTS(.tag = varinfo->dobj.name,
+								  .namespace = varinfo->dobj.namespace->dobj.name,
+								  .owner = varinfo->rolname,
+								  .description = "VARIABLE",
+								  .section = SECTION_PRE_DATA,
+								  .createStmt = query->data,
+								  .dropStmt = delq->data));
+
+	/* dump comment if any */
+	if (varinfo->dobj.dump & DUMP_COMPONENT_COMMENT)
+		dumpComment(fout, "VARIABLE", qualvarname,
+					NULL, varinfo->rolname,
+					varinfo->dobj.catId, 0, varinfo->dobj.dumpId);
+
+	/* dump ACL if any */
+	if (varinfo->dobj.dump & DUMP_COMPONENT_ACL)
+	{
+		char	   *qvarname = pg_strdup(fmtId(varinfo->dobj.name));
+
+		dumpACL(fout, varinfo->dobj.dumpId, InvalidDumpId, "VARIABLE",
+				qvarname, NULL,
+				varinfo->dobj.namespace->dobj.name, NULL, varinfo->rolname,
+				&varinfo->dacl);
+
+		free(qvarname);
+	}
+
+	destroyPQExpBuffer(delq);
+	destroyPQExpBuffer(query);
+
+	free(qualvarname);
+}
+
 static void
 binary_upgrade_set_type_oids_by_type_oid(Archive *fout,
 										 PQExpBuffer upgrade_buffer,
@@ -11558,6 +11741,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
 		case DO_REL_STATS:
 			dumpRelationStats(fout, (const RelStatsInfo *) dobj);
 			break;
+		case DO_VARIABLE:
+			dumpVariable(fout, (VariableInfo *) dobj);
+			break;
 		case DO_PRE_DATA_BOUNDARY:
 		case DO_POST_DATA_BOUNDARY:
 			/* never dumped, nothing to do */
@@ -16012,6 +16198,9 @@ dumpDefaultACL(Archive *fout, const DefaultACLInfo *daclinfo)
 		case DEFACLOBJ_LARGEOBJECT:
 			type = "LARGE OBJECTS";
 			break;
+		case DEFACLOBJ_VARIABLE:
+			type = "VARIABLES";
+			break;
 		default:
 			/* shouldn't get here */
 			pg_fatal("unrecognized object type in default privileges: %d",
@@ -19750,6 +19939,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
 			case DO_CONVERSION:
 			case DO_TABLE:
 			case DO_TABLE_ATTACH:
+			case DO_VARIABLE:
 			case DO_ATTRDEF:
 			case DO_PROCLANG:
 			case DO_CAST:
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 7417eab6aef..1c722e48de5 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -53,6 +53,7 @@ typedef enum
 	DO_TABLE,
 	DO_TABLE_ATTACH,
 	DO_ATTRDEF,
+	DO_VARIABLE,
 	DO_INDEX,
 	DO_INDEX_ATTACH,
 	DO_STATSEXT,
@@ -735,6 +736,19 @@ typedef struct _SubRelInfo
 	char	   *srsublsn;
 } SubRelInfo;
 
+/*
+ * The VariableInfo struct is used to represent session variables
+ */
+typedef struct _VariableInfo
+{
+	DumpableObject dobj;
+	DumpableAcl dacl;
+	Oid			vartype;
+	char	   *vartypname;
+	Oid			varcollation;
+	const char *rolname;		/* name of owner, or empty string */
+} VariableInfo;
+
 /*
  *	common utility functions
  */
@@ -818,5 +832,6 @@ extern void getPublicationTables(Archive *fout, TableInfo tblinfo[],
 								 int numTables);
 extern void getSubscriptions(Archive *fout);
 extern void getSubscriptionTables(Archive *fout);
+extern void getVariables(Archive *fout);
 
 #endif							/* PG_DUMP_H */
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 0b0977788f1..b34f3418f64 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -77,6 +77,7 @@ enum dbObjectTypePriorities
 	PRIO_DUMMY_TYPE,
 	PRIO_ATTRDEF,
 	PRIO_LARGE_OBJECT,
+	PRIO_VARIABLE,
 	PRIO_PRE_DATA_BOUNDARY,		/* boundary! */
 	PRIO_TABLE_DATA,
 	PRIO_SEQUENCE_SET,
@@ -119,6 +120,7 @@ static const int dbObjectTypePriority[] =
 	[DO_TABLE] = PRIO_TABLE,
 	[DO_TABLE_ATTACH] = PRIO_TABLE_ATTACH,
 	[DO_ATTRDEF] = PRIO_ATTRDEF,
+	[DO_VARIABLE] = PRIO_VARIABLE,
 	[DO_INDEX] = PRIO_INDEX,
 	[DO_INDEX_ATTACH] = PRIO_INDEX_ATTACH,
 	[DO_STATSEXT] = PRIO_STATSEXT,
@@ -1533,6 +1535,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
 					 "RELATION STATISTICS FOR %s  (ID %d OID %u)",
 					 obj->name, obj->dumpId, obj->catId.oid);
 			return;
+		case DO_VARIABLE:
+			snprintf(buf, bufsize,
+					 "VARIABLE %s  (ID %d OID %u)",
+					 obj->name, obj->dumpId, obj->catId.oid);
+			return;
 	}
 	/* shouldn't get here */
 	snprintf(buf, bufsize,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 386e21e0c59..663d31a4557 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -960,6 +960,16 @@ my %tests = (
 		unlike => { no_privs => 1, },
 	  },
 
+	'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT SELECT ON VARIABLES TO PUBLIC'
+	  => {
+		create_order => 56,
+		create_sql   => 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT SELECT ON VARIABLES TO PUBLIC;',
+		regexp => qr/^
+			\QALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT SELECT ON VARIABLES TO PUBLIC;\E/xm,
+		like => { %full_runs, section_post_data => 1, },
+		unlike => { no_privs => 1, },
+	  },
+
 	'ALTER ROLE regress_dump_test_role' => {
 		regexp => qr/^
 			\QALTER ROLE regress_dump_test_role WITH \E
@@ -1928,6 +1938,23 @@ my %tests = (
 		},
 	},
 
+	'COMMENT ON VARIABLE dump_test.variable1' => {
+		create_order => 71,
+		create_sql   => 'COMMENT ON VARIABLE dump_test.variable1
+					   IS \'comment on variable\';',
+		regexp =>
+		  qr/^\QCOMMENT ON VARIABLE dump_test.variable1 IS 'comment on variable';\E/m,
+		like => {
+			%full_runs,
+			%dump_test_schema_runs,
+			section_pre_data     => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			only_dump_measurement    => 1,
+		},
+	},
+
 	'COPY test_table' => {
 		create_order => 4,
 		create_sql => 'INSERT INTO dump_test.test_table (col1) '
@@ -4236,6 +4263,24 @@ my %tests = (
 		},
 	},
 
+	'CREATE VARIABLE test_variable' => {
+		all_runs     => 1,
+		catch_all    => 'CREATE ... commands',
+		create_order => 61,
+		create_sql   => 'CREATE VARIABLE dump_test.variable1 AS integer;',
+		regexp => qr/^
+			\QCREATE VARIABLE dump_test.variable1 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
@@ -4700,6 +4745,25 @@ my %tests = (
 		like => {},
 	},
 
+	'GRANT SELECT ON VARIABLE dump_test.variable1' => {
+		create_order => 73,
+		create_sql =>
+		  'GRANT SELECT ON VARIABLE dump_test.variable1 TO regress_dump_test_role;',
+		regexp => qr/^
+			\QGRANT SELECT ON VARIABLE dump_test.variable1 TO regress_dump_test_role;\E
+			/xm,
+		like => {
+			%full_runs,
+			%dump_test_schema_runs,
+			section_pre_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			no_privs                 => 1,
+			only_dump_measurement    => 1,
+		},
+	},
+
 	'REFRESH MATERIALIZED VIEW matview' => {
 		regexp => qr/^\QREFRESH MATERIALIZED VIEW dump_test.matview;\E/m,
 		like =>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ec499bcf287..bdcdbd3f57f 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3187,6 +3187,7 @@ VarString
 VarStringSortSupport
 Variable
 VariableAssignHook
+VariableInfo
 VariableSetKind
 VariableSetStmt
 VariableShowStmt
-- 
2.49.0