On Mon, Dec 17, 2018 at 12:24:15AM -0500, Tom Lane wrote: > If we were to rename the "foo.expr" column at this point, > and then dump and reload, the expression column in the > second index would presumably acquire the name "expr" > not "expr1", because "expr" would no longer be taken. > So if pg_dump were to try to use that index column name > in ALTER ... SET STATISTICS, it'd fail.
Good point, thanks! I did not think about the case where a table uses an attribute name matching what would be generated for indexes. So this settles the argument that we had better not do anything before v11. Switching the dump code to use column numbers has not proved to be complicated as only the query and some comments had to be tweaked. Attached is an updated patch, and I am switching back the patch to "Needs review" to have an extra pair of eyes look at that in case I missed something. -- Michael
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 637c79af48..a46dd4c8e6 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6712,7 +6712,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_conoid,
i_condef,
i_tablespace,
- i_indreloptions;
+ i_indreloptions,
+ i_indstatcols,
+ i_indstatvals;
int ntups;
for (i = 0; i < numTables; i++)
@@ -6766,7 +6768,15 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions "
+ "t.reloptions AS indreloptions, "
+ "(SELECT pg_catalog.array_agg(attnum ORDER BY attnum) "
+ " FROM pg_catalog.pg_attribute "
+ " WHERE attrelid = i.indexrelid AND "
+ " attstattarget >= 0) AS indstatcols,"
+ "(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) "
+ " FROM pg_catalog.pg_attribute "
+ " WHERE attrelid = i.indexrelid AND "
+ " attstattarget >= 0) AS indstatvals "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid) "
@@ -6803,7 +6813,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions "
+ "t.reloptions AS indreloptions, "
+ "'' AS indstatcols, "
+ "'' AS indstatvals "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6836,7 +6848,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions "
+ "t.reloptions AS indreloptions, "
+ "'' AS indstatcols, "
+ "'' AS indstatvals "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6865,7 +6879,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"null AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "t.reloptions AS indreloptions "
+ "t.reloptions AS indreloptions, "
+ "'' AS indstatcols, "
+ "'' AS indstatvals "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6897,7 +6913,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"c.oid AS conoid, "
"null AS condef, "
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
- "null AS indreloptions "
+ "null AS indreloptions, "
+ "'' AS indstatcols, "
+ "'' AS indstatvals "
"FROM pg_catalog.pg_index i "
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6935,6 +6953,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_condef = PQfnumber(res, "condef");
i_tablespace = PQfnumber(res, "tablespace");
i_indreloptions = PQfnumber(res, "indreloptions");
+ i_indstatcols = PQfnumber(res, "indstatcols");
+ i_indstatvals = PQfnumber(res, "indstatvals");
tbinfo->indexes = indxinfo =
(IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo));
@@ -6958,6 +6978,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
indxinfo[j].indnattrs = atoi(PQgetvalue(res, j, i_indnatts));
indxinfo[j].tablespace = pg_strdup(PQgetvalue(res, j, i_tablespace));
indxinfo[j].indreloptions = pg_strdup(PQgetvalue(res, j, i_indreloptions));
+ indxinfo[j].indstatcols = pg_strdup(PQgetvalue(res, j, i_indstatcols));
+ indxinfo[j].indstatvals = pg_strdup(PQgetvalue(res, j, i_indstatvals));
indxinfo[j].indkeys = (Oid *) pg_malloc(indxinfo[j].indnattrs * sizeof(Oid));
parseOidArray(PQgetvalue(res, j, i_indkey),
indxinfo[j].indkeys, indxinfo[j].indnattrs);
@@ -16148,6 +16170,13 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
*/
if (!is_constraint)
{
+ char *indstatcols = indxinfo->indstatcols;
+ char *indstatvals = indxinfo->indstatvals;
+ char **indstatcolsarray = NULL;
+ char **indstatvalsarray = NULL;
+ int nstatcols;
+ int nstatvals;
+
if (dopt->binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
indxinfo->dobj.catId.oid, true);
@@ -16171,6 +16200,31 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
qindxname);
}
+ /*
+ * If the index has any statistics on some of its columns,
+ * generate the associated ALTER INDEX queries.
+ */
+ if (parsePGArray(indstatcols, &indstatcolsarray, &nstatcols) &&
+ parsePGArray(indstatvals, &indstatvalsarray, &nstatvals) &&
+ nstatcols == nstatvals)
+ {
+ int j;
+
+ for (j = 0; j < nstatcols; j++)
+ {
+ appendPQExpBuffer(q, "ALTER INDEX %s ",
+ fmtQualifiedDumpable(indxinfo));
+ /*
+ * Note that this is a column number, so no quotes should
+ * be used.
+ */
+ appendPQExpBuffer(q, "ALTER COLUMN %s ",
+ indstatcolsarray[j]);
+ appendPQExpBuffer(q, "SET STATISTICS %s;\n",
+ indstatvalsarray[j]);
+ }
+ }
+
/* If the index defines identity, we need to record that. */
if (indxinfo->indisreplident)
{
@@ -16194,6 +16248,11 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
q->data, delq->data, NULL,
NULL, 0,
NULL, NULL);
+
+ if (indstatcolsarray)
+ free(indstatcolsarray);
+ if (indstatvalsarray)
+ free(indstatvalsarray);
}
/* Dump Index Comments */
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 789d6a24e2..1d997fa36f 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -360,6 +360,8 @@ typedef struct _indxInfo
char *indexdef;
char *tablespace; /* tablespace in which index is stored */
char *indreloptions; /* options specified by WITH (...) */
+ char *indstatcols; /* column numbers with statistics */
+ char *indstatvals; /* statistic values for columns */
int indnkeyattrs; /* number of index key attributes */
int indnattrs; /* total number of index attributes */
Oid *indkeys; /* In spite of the name 'indkeys' this field
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 46dbb078cf..8fd283f601 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2343,6 +2343,24 @@ my %tests = (
unlike => { exclude_dump_test_schema => 1, },
},
+ 'CREATE TABLE table_with_stats' => {
+ create_order => 98,
+ create_sql => 'CREATE TABLE dump_test.table_index_stats (
+ col1 int,
+ col2 int);
+ CREATE INDEX index_with_stats
+ ON dump_test.table_index_stats
+ ((col1 + 1), (col2 + 1));
+ ALTER INDEX dump_test.index_with_stats
+ ALTER COLUMN 1 SET STATISTICS 1000;',
+ regexp => qr/^
+ \QALTER INDEX dump_test.index_with_stats ALTER COLUMN 1 SET STATISTICS 1000;\E
+ /xm,
+ like =>
+ { %full_runs, %dump_test_schema_runs, section_post_data => 1, },
+ unlike => { exclude_dump_test_schema => 1, },
+ },
+
'CREATE STATISTICS extended_stats_no_options' => {
create_order => 97,
create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options
signature.asc
Description: PGP signature
