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

Commitd0960eb

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 parentf760a8b commitd0960eb

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
@@ -6752,7 +6752,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
67526752
i_condef,
67536753
i_tablespace,
67546754
i_indreloptions,
6755-
i_relpages;
6755+
i_relpages,
6756+
i_indstatcols,
6757+
i_indstatvals;
67566758
intntups;
67576759

67586760
for (i = 0; i < numTables; i++)
@@ -6806,7 +6808,15 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
68066808
"c.oid AS conoid, "
68076809
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
68086810
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6809-
"t.reloptions AS indreloptions "
6811+
"t.reloptions AS indreloptions, "
6812+
"(SELECT pg_catalog.array_agg(attnum ORDER BY attnum) "
6813+
" FROM pg_catalog.pg_attribute "
6814+
" WHERE attrelid = i.indexrelid AND "
6815+
" attstattarget >= 0) AS indstatcols,"
6816+
"(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) "
6817+
" FROM pg_catalog.pg_attribute "
6818+
" WHERE attrelid = i.indexrelid AND "
6819+
" attstattarget >= 0) AS indstatvals "
68106820
"FROM pg_catalog.pg_index i "
68116821
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
68126822
"JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid) "
@@ -6843,7 +6853,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
68436853
"c.oid AS conoid, "
68446854
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
68456855
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6846-
"t.reloptions AS indreloptions "
6856+
"t.reloptions AS indreloptions, "
6857+
"'' AS indstatcols, "
6858+
"'' AS indstatvals "
68476859
"FROM pg_catalog.pg_index i "
68486860
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
68496861
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6876,7 +6888,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
68766888
"c.oid AS conoid, "
68776889
"pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
68786890
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6879-
"t.reloptions AS indreloptions "
6891+
"t.reloptions AS indreloptions, "
6892+
"'' AS indstatcols, "
6893+
"'' AS indstatvals "
68806894
"FROM pg_catalog.pg_index i "
68816895
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
68826896
"LEFT JOIN pg_catalog.pg_constraint c "
@@ -6905,7 +6919,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
69056919
"c.oid AS conoid, "
69066920
"null AS condef, "
69076921
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6908-
"t.reloptions AS indreloptions "
6922+
"t.reloptions AS indreloptions, "
6923+
"'' AS indstatcols, "
6924+
"'' AS indstatvals "
69096925
"FROM pg_catalog.pg_index i "
69106926
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
69116927
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6937,7 +6953,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
69376953
"c.oid AS conoid, "
69386954
"null AS condef, "
69396955
"(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
6940-
"null AS indreloptions "
6956+
"null AS indreloptions, "
6957+
"'' AS indstatcols, "
6958+
"'' AS indstatvals "
69416959
"FROM pg_catalog.pg_index i "
69426960
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
69436961
"LEFT JOIN pg_catalog.pg_depend d "
@@ -6976,6 +6994,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
69766994
i_condef = PQfnumber(res, "condef");
69776995
i_tablespace = PQfnumber(res, "tablespace");
69786996
i_indreloptions = PQfnumber(res, "indreloptions");
6997+
i_indstatcols = PQfnumber(res, "indstatcols");
6998+
i_indstatvals = PQfnumber(res, "indstatvals");
69796999

69807000
tbinfo->indexes = indxinfo =
69817001
(IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo));
@@ -6999,6 +7019,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
69997019
indxinfo[j].indnattrs = atoi(PQgetvalue(res, j, i_indnatts));
70007020
indxinfo[j].tablespace = pg_strdup(PQgetvalue(res, j, i_tablespace));
70017021
indxinfo[j].indreloptions = pg_strdup(PQgetvalue(res, j, i_indreloptions));
7022+
indxinfo[j].indstatcols = pg_strdup(PQgetvalue(res, j, i_indstatcols));
7023+
indxinfo[j].indstatvals = pg_strdup(PQgetvalue(res, j, i_indstatvals));
70027024
indxinfo[j].indkeys = (Oid *) pg_malloc(indxinfo[j].indnattrs * sizeof(Oid));
70037025
parseOidArray(PQgetvalue(res, j, i_indkey),
70047026
indxinfo[j].indkeys, indxinfo[j].indnattrs);
@@ -16242,6 +16264,13 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
1624216264
*/
1624316265
if (!is_constraint)
1624416266
{
16267+
char *indstatcols = indxinfo->indstatcols;
16268+
char *indstatvals = indxinfo->indstatvals;
16269+
char **indstatcolsarray = NULL;
16270+
char **indstatvalsarray = NULL;
16271+
intnstatcols;
16272+
intnstatvals;
16273+
1624516274
if (dopt->binary_upgrade)
1624616275
binary_upgrade_set_pg_class_oids(fout, q,
1624716276
indxinfo->dobj.catId.oid, true);
@@ -16265,6 +16294,32 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
1626516294
qindxname);
1626616295
}
1626716296

16297+
/*
16298+
* If the index has any statistics on some of its columns, generate
16299+
* the associated ALTER INDEX queries.
16300+
*/
16301+
if (parsePGArray(indstatcols, &indstatcolsarray, &nstatcols) &&
16302+
parsePGArray(indstatvals, &indstatvalsarray, &nstatvals) &&
16303+
nstatcols == nstatvals)
16304+
{
16305+
intj;
16306+
16307+
for (j = 0; j < nstatcols; j++)
16308+
{
16309+
appendPQExpBuffer(q, "ALTER INDEX %s ",
16310+
fmtQualifiedDumpable(indxinfo));
16311+
16312+
/*
16313+
* Note that this is a column number, so no quotes should be
16314+
* used.
16315+
*/
16316+
appendPQExpBuffer(q, "ALTER COLUMN %s ",
16317+
indstatcolsarray[j]);
16318+
appendPQExpBuffer(q, "SET STATISTICS %s;\n",
16319+
indstatvalsarray[j]);
16320+
}
16321+
}
16322+
1626816323
/* If the index defines identity, we need to record that. */
1626916324
if (indxinfo->indisreplident)
1627016325
{
@@ -16288,6 +16343,11 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
1628816343
q->data, delq->data, NULL,
1628916344
NULL, 0,
1629016345
NULL, NULL);
16346+
16347+
if (indstatcolsarray)
16348+
free(indstatcolsarray);
16349+
if (indstatvalsarray)
16350+
free(indstatvalsarray);
1629116351
}
1629216352

1629316353
/* Dump Index Comments */

‎src/bin/pg_dump/pg_dump.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -361,6 +361,8 @@ typedef struct _indxInfo
361361
char*indexdef;
362362
char*tablespace;/* tablespace in which index is stored */
363363
char*indreloptions;/* options specified by WITH (...) */
364+
char*indstatcols;/* column numbers with statistics */
365+
char*indstatvals;/* statistic values for columns */
364366
intindnkeyattrs;/* number of index key attributes */
365367
intindnattrs;/* total number of index attributes */
366368
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
@@ -2420,6 +2420,28 @@
24202420
unlike => { exclude_dump_test_schema => 1, },
24212421
},
24222422
2423+
'CREATE TABLE table_with_stats' => {
2424+
create_order => 98,
2425+
create_sql => 'CREATE TABLE dump_test.table_index_stats (
2426+
col1 int,
2427+
col2 int,
2428+
col3 int);
2429+
CREATE INDEX index_with_stats
2430+
ON dump_test.table_index_stats
2431+
((col1 + 1), col1, (col2 + 1), (col3 + 1));
2432+
ALTER INDEX dump_test.index_with_stats
2433+
ALTER COLUMN 1 SET STATISTICS 400;
2434+
ALTER INDEX dump_test.index_with_stats
2435+
ALTER COLUMN 3 SET STATISTICS 500;',
2436+
regexp => qr/^
2437+
\QALTER INDEX dump_test.index_with_stats ALTER COLUMN 1 SET STATISTICS 400;\E\n
2438+
\QALTER INDEX dump_test.index_with_stats ALTER COLUMN 3 SET STATISTICS 500;\E\n
2439+
/xms,
2440+
like =>
2441+
{%full_runs,%dump_test_schema_runs, section_post_data => 1, },
2442+
unlike => { exclude_dump_test_schema => 1, },
2443+
},
2444+
24232445
'CREATE STATISTICS extended_stats_no_options' => {
24242446
create_order => 97,
24252447
create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp