vViaDellaAttnums-0002-Dump-expression-index-stats-by-attn.patch

From 92f75fc9835e7982216d32329caa34b26347a6b3 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 26 Feb 2025 04:08:31 -0500
Subject: [PATCH vViaDellaAttnums 2/2] Dump expression index stats by attnum,
 not attname.

Names of columns in index expressions are not stable across major
versions, so we are forced to dump those by attnum instead.
---
 src/bin/pg_dump/pg_dump.c | 219 +++++++++++++++++++++-----------------
 src/bin/pg_dump/pg_dump.h |   2 +
 2 files changed, 125 insertions(+), 96 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 0de6c959bb0..65413e07899 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6819,7 +6819,8 @@ getFuncs(Archive *fout)
  */
 static RelStatsInfo *
 getRelationStatistics(Archive *fout, DumpableObject *rel, int32 relpages,
-					  float reltuples, int32 relallvisible, char relkind)
+					  float reltuples, int32 relallvisible, char relkind,
+					  char **indexprattnames, int nindexprattnames)
 {
 	if (!fout->dopt->dumpStatistics)
 		return NULL;
@@ -6844,6 +6845,8 @@ getRelationStatistics(Archive *fout, DumpableObject *rel, int32 relpages,
 		dobj->components |= DUMP_COMPONENT_STATISTICS;
 		dobj->name = pg_strdup(rel->name);
 		dobj->namespace = rel->namespace;
+		info->indexprattnames = indexprattnames;
+		info->nindexprattnames = nindexprattnames;
 		info->relpages = relpages;
 		info->reltuples = reltuples;
 		info->relallvisible = relallvisible;
@@ -7249,7 +7252,8 @@ getTables(Archive *fout, int *numTables)
 		/* Add statistics */
 		if (tblinfo[i].interesting)
 			getRelationStatistics(fout, &tblinfo[i].dobj, tblinfo[i].relpages,
-								  reltuples, relallvisible, tblinfo[i].relkind);
+								  reltuples, relallvisible, tblinfo[i].relkind,
+								  NULL, 0);
 
 		/*
 		 * Read-lock target tables to make sure they aren't DROPPED or altered
@@ -7537,7 +7541,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 				i_tablespace,
 				i_indreloptions,
 				i_indstatcols,
-				i_indstatvals;
+				i_indstatvals,
+				i_indexprattnames;
 
 	/*
 	 * We want to perform just one query against pg_index.  However, we
@@ -7579,6 +7584,10 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 						 "c.tableoid AS contableoid, "
 						 "c.oid AS conoid, "
 						 "pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
+						 "(SELECT pg_catalog.array_agg(attname ORDER BY attnum) "
+						 "  FROM pg_catalog.pg_attribute "
+						 "  WHERE attrelid = i.indexrelid AND "
+						 "    i.indexprs IS NOT NULL) AS indexprattnames, "
 						 "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
 						 "t.reloptions AS indreloptions, ");
 
@@ -7702,6 +7711,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 	i_indreloptions = PQfnumber(res, "indreloptions");
 	i_indstatcols = PQfnumber(res, "indstatcols");
 	i_indstatvals = PQfnumber(res, "indstatvals");
+	i_indexprattnames = PQfnumber(res, "indexprattnames");
 
 	indxinfo = (IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo));
 
@@ -7715,6 +7725,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 		Oid			indrelid = atooid(PQgetvalue(res, j, i_indrelid));
 		TableInfo  *tbinfo = NULL;
 		int			numinds;
+		char	  **indexprattnames = NULL;	/* attnames for expression indexes only */
+		int			nindexprattnames = 0;	/* number of attnames for expression indexes only */
 
 		/* Count rows for this table */
 		for (numinds = 1; numinds < ntups - j; numinds++)
@@ -7784,9 +7796,16 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 			else
 				indexkind = RELKIND_PARTITIONED_INDEX;
 
-			contype = *(PQgetvalue(res, j, i_contype));
+			if (!PQgetisnull(res, j, i_indexprattnames))
+				if (!parsePGArray(PQgetvalue(res, j, i_indexprattnames),
+								   &indexprattnames, &nindexprattnames))
+				pg_fatal("could not parse %s array", "indattnames");
+
 			relstats = getRelationStatistics(fout, &indxinfo[j].dobj, relpages,
-											 reltuples, relallvisible, indexkind);
+											 reltuples, relallvisible, indexkind,
+											 indexprattnames, nindexprattnames);
+
+			contype = *(PQgetvalue(res, j, i_contype));
 
 			if (contype == 'p' || contype == 'u' || contype == 'x')
 			{
@@ -10410,28 +10429,6 @@ dumpComment(Archive *fout, const char *type,
 						catalogId, subid, dumpId, NULL);
 }
 
-/*
- * Tabular description of the parameters to pg_restore_attribute_stats()
- * param_name, param_type
- */
-static const char *att_stats_arginfo[][2] = {
-	{"attname", "name"},
-	{"inherited", "boolean"},
-	{"null_frac", "float4"},
-	{"avg_width", "integer"},
-	{"n_distinct", "float4"},
-	{"most_common_vals", "text"},
-	{"most_common_freqs", "float4[]"},
-	{"histogram_bounds", "text"},
-	{"correlation", "float4"},
-	{"most_common_elems", "text"},
-	{"most_common_elem_freqs", "float4[]"},
-	{"elem_count_histogram", "float4[]"},
-	{"range_length_histogram", "text"},
-	{"range_empty_frac", "float4"},
-	{"range_bounds_histogram", "text"},
-};
-
 /*
  * appendNamedArgument --
  *
@@ -10440,9 +10437,9 @@ static const char *att_stats_arginfo[][2] = {
  */
 static void
 appendNamedArgument(PQExpBuffer out, Archive *fout, const char *argname,
-					const char *argval, const char *argtype)
+					const char *argtype, const char *argval)
 {
-	appendPQExpBufferStr(out, "\t");
+	appendPQExpBufferStr(out, ",\n\t");
 
 	appendStringLiteralAH(out, argname, fout);
 	appendPQExpBufferStr(out, ", ");
@@ -10451,68 +10448,6 @@ appendNamedArgument(PQExpBuffer out, Archive *fout, const char *argname,
 	appendPQExpBuffer(out, "::%s", argtype);
 }
 
-/*
- * appendRelStatsImport --
- *
- * Append a formatted pg_restore_relation_stats statement.
- */
-static void
-appendRelStatsImport(PQExpBuffer out, Archive *fout, const RelStatsInfo *rsinfo,
-					 const char *qualified_name)
-{
-	char		reltuples_str[FLOAT_SHORTEST_DECIMAL_LEN];
-
-	float_to_shortest_decimal_buf(rsinfo->reltuples, reltuples_str);
-
-	appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_relation_stats(\n");
-	appendPQExpBuffer(out, "\t'version', '%u'::integer,\n",
-					  fout->remoteVersion);
-	appendPQExpBuffer(out, "\t'relation', '%s'::regclass,\n", qualified_name);
-	appendPQExpBuffer(out, "\t'relpages', '%d'::integer,\n", rsinfo->relpages);
-	appendPQExpBuffer(out, "\t'reltuples', '%s'::real,\n", reltuples_str);
-	appendPQExpBuffer(out, "\t'relallvisible', '%d'::integer\n);\n",
-					  rsinfo->relallvisible);
-}
-
-/*
- * appendAttStatsImport --
- *
- * Append a series of formatted pg_restore_attribute_stats statements.
- */
-static void
-appendAttStatsImport(PQExpBuffer out, Archive *fout, PGresult *res,
-					 const char *qualified_name)
-{
-	for (int rownum = 0; rownum < PQntuples(res); rownum++)
-	{
-		const char *sep = "";
-
-		appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_attribute_stats(\n");
-		appendPQExpBuffer(out, "\t'version', '%u'::integer,\n",
-						  fout->remoteVersion);
-		appendPQExpBuffer(out, "\t'relation', '%s'::regclass,\n",
-						  qualified_name);
-		for (int argno = 0; argno < lengthof(att_stats_arginfo); argno++)
-		{
-			const char *argname = att_stats_arginfo[argno][0];
-			const char *argtype = att_stats_arginfo[argno][1];
-			int			fieldno = PQfnumber(res, argname);
-
-			if (fieldno < 0)
-				pg_fatal("attribute stats export query missing field '%s'",
-						 argname);
-
-			if (PQgetisnull(res, rownum, fieldno))
-				continue;
-
-			appendPQExpBufferStr(out, sep);
-			appendNamedArgument(out, fout, argname, PQgetvalue(res, rownum, fieldno), argtype);
-			sep = ",\n";
-		}
-		appendPQExpBufferStr(out, "\n);\n");
-	}
-}
-
 /*
  * Decide which section to use based on the relkind of the parent object.
  *
@@ -10557,6 +10492,7 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
 	DumpId	   *deps = NULL;
 	int			ndeps = 0;
 	const char *qualified_name;
+	char		reltuples_str[FLOAT_SHORTEST_DECIMAL_LEN];
 
 	/* nothing to do if we are not dumping statistics */
 	if (!fout->dopt->dumpStatistics)
@@ -10606,6 +10542,22 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
 		resetPQExpBuffer(query);
 	}
 
+	out = createPQExpBuffer();
+
+	qualified_name = fmtQualifiedId(rsinfo->dobj.namespace->dobj.name,
+									rsinfo->dobj.name);
+
+	/* restore relation stats */
+	float_to_shortest_decimal_buf(rsinfo->reltuples, reltuples_str);
+	appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_relation_stats(\n");
+	appendPQExpBuffer(out, "\t'version', '%u'::integer,\n",
+					  fout->remoteVersion);
+	appendPQExpBuffer(out, "\t'relation', '%s'::regclass,\n", qualified_name);
+	appendPQExpBuffer(out, "\t'relpages', '%d'::integer,\n", rsinfo->relpages);
+	appendPQExpBuffer(out, "\t'reltuples', '%s'::real,\n", reltuples_str);
+	appendPQExpBuffer(out, "\t'relallvisible', '%d'::integer\n);\n",
+					  rsinfo->relallvisible);
+
 	appendPQExpBufferStr(query, "EXECUTE getAttributeStats(");
 	appendStringLiteralAH(query, dobj->namespace->dobj.name, fout);
 	appendPQExpBufferStr(query, ", ");
@@ -10614,13 +10566,88 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
 
 	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
 
-	out = createPQExpBuffer();
+	/* restore attribute stats */
+	for (int rownum = 0; rownum < PQntuples(res); rownum++)
+	{
+		const char *attname;
 
-	qualified_name = fmtQualifiedId(rsinfo->dobj.namespace->dobj.name,
-									rsinfo->dobj.name);
+		appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_attribute_stats(\n");
+		appendPQExpBuffer(out, "\t'version', '%u'::integer,\n",
+						  fout->remoteVersion);
+		appendPQExpBuffer(out, "\t'relation', '%s'::regclass",
+						  qualified_name);
 
-	appendRelStatsImport(out, fout, rsinfo, qualified_name);
-	appendAttStatsImport(out, fout, res, qualified_name);
+
+		if (PQgetisnull(res, rownum, 0))
+			pg_fatal("attname cannot be NULL");
+		attname = PQgetvalue(res, rownum, 0);
+
+		/*
+		 * Expression indexes look up attname in attnames to derive attnum,
+		 * all others use attname directly.
+		 */
+		if (rsinfo->nindexprattnames == 0)
+			appendNamedArgument(out, fout, "attname", "name", attname);
+		else
+		{
+			bool found = false;
+
+			for (int i = 0; i < rsinfo->nindexprattnames; i++)
+				if (strcmp(attname, rsinfo->indexprattnames[i]) == 0)
+				{
+					appendPQExpBuffer(out, ",\n\t'attnum', '%d'::smallint", (AttrNumber) i + 1);
+					found = true;
+					break;
+				}
+
+			if (!found)
+				pg_fatal("unable to find attname '%s'", attname);
+		}
+
+		if (!PQgetisnull(res, rownum, 1))
+			appendNamedArgument(out, fout, "inherited", "boolean",
+								PQgetvalue(res, rownum, 1));
+		if (!PQgetisnull(res, rownum, 2))
+			appendNamedArgument(out, fout, "null_frac", "float4",
+								PQgetvalue(res, rownum, 2));
+		if (!PQgetisnull(res, rownum, 3))
+			appendNamedArgument(out, fout, "avg_width", "integer",
+								PQgetvalue(res, rownum, 3));
+		if (!PQgetisnull(res, rownum, 4))
+			appendNamedArgument(out, fout, "n_distinct", "float4",
+								PQgetvalue(res, rownum, 4));
+		if (!PQgetisnull(res, rownum, 5))
+			appendNamedArgument(out, fout, "most_common_vals", "text",
+								PQgetvalue(res, rownum, 5));
+		if (!PQgetisnull(res, rownum, 6))
+			appendNamedArgument(out, fout, "most_common_freqs", "float4[]",
+								PQgetvalue(res, rownum, 6));
+		if (!PQgetisnull(res, rownum, 7))
+			appendNamedArgument(out, fout, "histogram_bounds", "text",
+								PQgetvalue(res, rownum, 7));
+		if (!PQgetisnull(res, rownum, 8))
+			appendNamedArgument(out, fout, "correlation", "float4",
+								PQgetvalue(res, rownum, 8));
+		if (!PQgetisnull(res, rownum, 9))
+			appendNamedArgument(out, fout, "most_common_elems", "text",
+								PQgetvalue(res, rownum, 9));
+		if (!PQgetisnull(res, rownum, 10))
+			appendNamedArgument(out, fout, "most_common_elem_freqs", "float4[]",
+								PQgetvalue(res, rownum, 10));
+		if (!PQgetisnull(res, rownum, 11))
+			appendNamedArgument(out, fout, "elem_count_histogram", "float4[]",
+								PQgetvalue(res, rownum, 11));
+		if (!PQgetisnull(res, rownum, 12))
+			appendNamedArgument(out, fout, "range_length_histogram", "text",
+								PQgetvalue(res, rownum, 12));
+		if (!PQgetisnull(res, rownum, 13))
+			appendNamedArgument(out, fout, "range_empty_frac", "float4",
+								PQgetvalue(res, rownum, 13));
+		if (!PQgetisnull(res, rownum, 14))
+			appendNamedArgument(out, fout, "range_bounds_histogram", "text",
+								PQgetvalue(res, rownum, 14));
+		appendPQExpBufferStr(out, "\n);\n");
+	}
 
 	PQclear(res);
 
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 9d6a4857c4b..0584b1c7abb 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -438,6 +438,8 @@ typedef struct _indexAttachInfo
 typedef struct _relStatsInfo
 {
 	DumpableObject dobj;
+	char	  **indexprattnames;	/* attnames in an expression index */
+	int32		nindexprattnames;	/* number of attnames for expression indexes, else 0 */
 	int32		relpages;
 	float		reltuples;
 	int32		relallvisible;
-- 
2.48.1