Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite4fca46

Browse files
committed
Include ALTER INDEX SET STATISTICS in pg_dump
The new grammar pattern of ALTER INDEX SET STATISTICS able to use columnnumbers on top of the existing column names introduced by commit5b6d13eforgot to add support for the feature in pg_dump, so defining statisticson index columns was missing from the dumps, potentially causing silentplanning problems with a subsequent restore.pg_dump ought to not use column names in what it generates as these areautomatically generated by the server and could conflict with realrelation attributes with matching patterns. "expr" and "exprN", Nincremented automatically after the creation of the first one, are usedas default attribute names for index expressions, and that could easilymatch what is defined in other relations, causing the dumps to fail ifsome of those attributes are renamed at some point. So to avoid anyproblems, the new grammar with column numbers gets used.Reported-by: Ronan DunklauAuthor: Michael PaquierReviewed-by: Tom Lane, Adrien Nayrat, Amul SulDiscussion:https://postgr.es/m/CAARsnT3UQ4V=yDNW468w8RqHfYiY9mpn2r_c5UkBJ97NAApUEw@mail.gmail.comBackpatch-through: 11, where the new syntax has been introduced.
1 parentcc92cca commite4fca46

File tree

3 files changed

+90
-6
lines changed

3 files changed

+90
-6
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 66 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -6712,7 +6712,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
67126712
i_conoid,
67136713
i_condef,
67146714
i_tablespace,
6715-
i_indreloptions;
6715+
i_indreloptions,
6716+
i_indstatcols,
6717+
i_indstatvals;
67166718
intntups;
67176719

67186720
for (i = 0; i < numTables; i++)
@@ -6766,7 +6768,15 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
67666768
"c.oid AS conoid, "
67676769
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
67686770
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6769-
"t.reloptions AS indreloptions "
6771+
"t.reloptions AS indreloptions, "
6772+
"(SELECT pg_catalog.array_agg(attnum ORDER BY attnum) "
6773+
" FROM pg_catalog.pg_attribute "
6774+
" WHERE attrelid = i.indexrelid AND "
6775+
" attstattarget >= 0) AS indstatcols,"
6776+
"(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) "
6777+
" FROM pg_catalog.pg_attribute "
6778+
" WHERE attrelid = i.indexrelid AND "
6779+
" attstattarget >= 0) AS indstatvals "
67706780
"FROM pg_catalog.pg_index i "
67716781
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
67726782
"JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid) "
@@ -6803,7 +6813,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
68036813
"c.oid AS conoid, "
68046814
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
68056815
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6806-
"t.reloptions AS indreloptions "
6816+
"t.reloptions AS indreloptions, "
6817+
"'' AS indstatcols, "
6818+
"'' AS indstatvals "
68076819
"FROM pg_catalog.pg_index i "
68086820
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
68096821
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6836,7 +6848,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
68366848
"c.oid AS conoid, "
68376849
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
68386850
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6839-
"t.reloptions AS indreloptions "
6851+
"t.reloptions AS indreloptions, "
6852+
"'' AS indstatcols, "
6853+
"'' AS indstatvals "
68406854
"FROM pg_catalog.pg_index i "
68416855
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
68426856
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6865,7 +6879,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
68656879
"c.oid AS conoid, "
68666880
"null AS condef, "
68676881
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6868-
"t.reloptions AS indreloptions "
6882+
"t.reloptions AS indreloptions, "
6883+
"'' AS indstatcols, "
6884+
"'' AS indstatvals "
68696885
"FROM pg_catalog.pg_index i "
68706886
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
68716887
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6897,7 +6913,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
68976913
"c.oid AS conoid, "
68986914
"null AS condef, "
68996915
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6900-
"null AS indreloptions "
6916+
"null AS indreloptions, "
6917+
"'' AS indstatcols, "
6918+
"'' AS indstatvals "
69016919
"FROM pg_catalog.pg_index i "
69026920
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
69036921
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6935,6 +6953,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
69356953
i_condef = PQfnumber(res, "condef");
69366954
i_tablespace = PQfnumber(res, "tablespace");
69376955
i_indreloptions = PQfnumber(res, "indreloptions");
6956+
i_indstatcols = PQfnumber(res, "indstatcols");
6957+
i_indstatvals = PQfnumber(res, "indstatvals");
69386958

69396959
tbinfo->indexes = indxinfo =
69406960
(IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo));
@@ -6958,6 +6978,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
69586978
indxinfo[j].indnattrs = atoi(PQgetvalue(res, j, i_indnatts));
69596979
indxinfo[j].tablespace = pg_strdup(PQgetvalue(res, j, i_tablespace));
69606980
indxinfo[j].indreloptions = pg_strdup(PQgetvalue(res, j, i_indreloptions));
6981+
indxinfo[j].indstatcols = pg_strdup(PQgetvalue(res, j, i_indstatcols));
6982+
indxinfo[j].indstatvals = pg_strdup(PQgetvalue(res, j, i_indstatvals));
69616983
indxinfo[j].indkeys = (Oid *) pg_malloc(indxinfo[j].indnattrs * sizeof(Oid));
69626984
parseOidArray(PQgetvalue(res, j, i_indkey),
69636985
indxinfo[j].indkeys, indxinfo[j].indnattrs);
@@ -16148,6 +16170,13 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
1614816170
*/
1614916171
if (!is_constraint)
1615016172
{
16173+
char *indstatcols = indxinfo->indstatcols;
16174+
char *indstatvals = indxinfo->indstatvals;
16175+
char **indstatcolsarray = NULL;
16176+
char **indstatvalsarray = NULL;
16177+
intnstatcols;
16178+
intnstatvals;
16179+
1615116180
if (dopt->binary_upgrade)
1615216181
binary_upgrade_set_pg_class_oids(fout, q,
1615316182
indxinfo->dobj.catId.oid, true);
@@ -16171,6 +16200,32 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
1617116200
qindxname);
1617216201
}
1617316202

16203+
/*
16204+
* If the index has any statistics on some of its columns, generate
16205+
* the associated ALTER INDEX queries.
16206+
*/
16207+
if (parsePGArray(indstatcols, &indstatcolsarray, &nstatcols) &&
16208+
parsePGArray(indstatvals, &indstatvalsarray, &nstatvals) &&
16209+
nstatcols == nstatvals)
16210+
{
16211+
intj;
16212+
16213+
for (j = 0; j < nstatcols; j++)
16214+
{
16215+
appendPQExpBuffer(q, "ALTER INDEX %s ",
16216+
fmtQualifiedDumpable(indxinfo));
16217+
16218+
/*
16219+
* Note that this is a column number, so no quotes should be
16220+
* used.
16221+
*/
16222+
appendPQExpBuffer(q, "ALTER COLUMN %s ",
16223+
indstatcolsarray[j]);
16224+
appendPQExpBuffer(q, "SET STATISTICS %s;\n",
16225+
indstatvalsarray[j]);
16226+
}
16227+
}
16228+
1617416229
/* If the index defines identity, we need to record that. */
1617516230
if (indxinfo->indisreplident)
1617616231
{
@@ -16194,6 +16249,11 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
1619416249
q->data, delq->data, NULL,
1619516250
NULL, 0,
1619616251
NULL, NULL);
16252+
16253+
if (indstatcolsarray)
16254+
free(indstatcolsarray);
16255+
if (indstatvalsarray)
16256+
free(indstatvalsarray);
1619716257
}
1619816258

1619916259
/* Dump Index Comments */

‎src/bin/pg_dump/pg_dump.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -360,6 +360,8 @@ typedef struct _indxInfo
360360
char*indexdef;
361361
char*tablespace;/* tablespace in which index is stored */
362362
char*indreloptions;/* options specified by WITH (...) */
363+
char*indstatcols;/* column numbers with statistics */
364+
char*indstatvals;/* statistic values for columns */
363365
intindnkeyattrs;/* number of index key attributes */
364366
intindnattrs;/* total number of index attributes */
365367
Oid*indkeys;/* In spite of the name 'indkeys' this field

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2343,6 +2343,28 @@
23432343
unlike => { exclude_dump_test_schema => 1, },
23442344
},
23452345
2346+
'CREATE TABLE table_with_stats' => {
2347+
create_order => 98,
2348+
create_sql => 'CREATE TABLE dump_test.table_index_stats (
2349+
col1 int,
2350+
col2 int,
2351+
col3 int);
2352+
CREATE INDEX index_with_stats
2353+
ON dump_test.table_index_stats
2354+
((col1 + 1), col1, (col2 + 1), (col3 + 1));
2355+
ALTER INDEX dump_test.index_with_stats
2356+
ALTER COLUMN 1 SET STATISTICS 400;
2357+
ALTER INDEX dump_test.index_with_stats
2358+
ALTER COLUMN 3 SET STATISTICS 500;',
2359+
regexp => qr/^
2360+
\QALTER INDEX dump_test.index_with_stats ALTER COLUMN 1 SET STATISTICS 400;\E\n
2361+
\QALTER INDEX dump_test.index_with_stats ALTER COLUMN 3 SET STATISTICS 500;\E\n
2362+
/xms,
2363+
like =>
2364+
{%full_runs,%dump_test_schema_runs, section_post_data => 1, },
2365+
unlike => { exclude_dump_test_schema => 1, },
2366+
},
2367+
23462368
'CREATE STATISTICS extended_stats_no_options' => {
23472369
create_order => 97,
23482370
create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp