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

Commit0b126c6

Browse files
committed
Fix pg_dump --inserts mode for generated columns with dropped columns.
If a table contains a generated column that's preceded by a droppedcolumn, dumpTableData_insert failed to account for the droppedcolumn, and would emit DEFAULT placeholder(s) in the wrong column(s).This resulted in failures at restore time. The default COPY code pathdid not have this bug, likely explaining why it wasn't noticed sooner.While we're fixing this, we can be a little smarter about thesituation: (1) avoid unnecessarily fetching the values of generatedcolumns, (2) omit generated columns from the output, too, if we'reusing --column-inserts. While these modes aren't expected to beas high-performance as the COPY path, we might as well be asefficient as we can; it doesn't add much complexity.Per report from Дмитрий Иванов.Back-patch to v12 where generated columns came in.Discussion:https://postgr.es/m/CAPL5KHrkBniyQt5e1rafm5DdXvbgiiqfEQEJ9GjtVzN71Jj5pA@mail.gmail.com
1 parentc4fe319 commit0b126c6

File tree

2 files changed

+111
-9
lines changed

2 files changed

+111
-9
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 43 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2088,13 +2088,42 @@ dumpTableData_insert(Archive *fout, const void *dcontext)
20882088
DumpOptions *dopt = fout->dopt;
20892089
PQExpBuffer q = createPQExpBuffer();
20902090
PQExpBuffer insertStmt = NULL;
2091+
char *attgenerated;
20912092
PGresult *res;
2092-
intnfields;
2093+
intnfields,
2094+
i;
20932095
introws_per_statement = dopt->dump_inserts;
20942096
introws_this_statement = 0;
20952097

2096-
appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
2097-
"SELECT * FROM ONLY %s",
2098+
/*
2099+
* If we're going to emit INSERTs with column names, the most efficient
2100+
* way to deal with generated columns is to exclude them entirely. For
2101+
* INSERTs without column names, we have to emit DEFAULT rather than the
2102+
* actual column value --- but we can save a few cycles by fetching nulls
2103+
* rather than the uninteresting-to-us value.
2104+
*/
2105+
attgenerated = (char *) pg_malloc(tbinfo->numatts * sizeof(char));
2106+
appendPQExpBufferStr(q, "DECLARE _pg_dump_cursor CURSOR FOR SELECT ");
2107+
nfields = 0;
2108+
for (i = 0; i < tbinfo->numatts; i++)
2109+
{
2110+
if (tbinfo->attisdropped[i])
2111+
continue;
2112+
if (tbinfo->attgenerated[i] && dopt->column_inserts)
2113+
continue;
2114+
if (nfields > 0)
2115+
appendPQExpBufferStr(q, ", ");
2116+
if (tbinfo->attgenerated[i])
2117+
appendPQExpBufferStr(q, "NULL");
2118+
else
2119+
appendPQExpBufferStr(q, fmtId(tbinfo->attnames[i]));
2120+
attgenerated[nfields] = tbinfo->attgenerated[i];
2121+
nfields++;
2122+
}
2123+
/* Servers before 9.4 will complain about zero-column SELECT */
2124+
if (nfields == 0)
2125+
appendPQExpBufferStr(q, "NULL");
2126+
appendPQExpBuffer(q, " FROM ONLY %s",
20982127
fmtQualifiedDumpable(tbinfo));
20992128
if (tdinfo->filtercond)
21002129
appendPQExpBuffer(q, " %s", tdinfo->filtercond);
@@ -2105,14 +2134,19 @@ dumpTableData_insert(Archive *fout, const void *dcontext)
21052134
{
21062135
res = ExecuteSqlQuery(fout, "FETCH 100 FROM _pg_dump_cursor",
21072136
PGRES_TUPLES_OK);
2108-
nfields = PQnfields(res);
2137+
2138+
/* cross-check field count, allowing for dummy NULL if any */
2139+
if (nfields != PQnfields(res) &&
2140+
!(nfields == 0 && PQnfields(res) == 1))
2141+
fatal("wrong number of fields retrieved from table \"%s\"",
2142+
tbinfo->dobj.name);
21092143

21102144
/*
21112145
* First time through, we build as much of the INSERT statement as
21122146
* possible in "insertStmt", which we can then just print for each
2113-
* statement. If the table happens to have zero columns then this will
2114-
* be a complete statement, otherwise it will end in "VALUES" and be
2115-
* ready to have the row's column values printed.
2147+
* statement. If the table happens to have zerodumpablecolumns then
2148+
*this willbe a complete statement, otherwise it will end in
2149+
*"VALUES" and beready to have the row's column values printed.
21162150
*/
21172151
if (insertStmt == NULL)
21182152
{
@@ -2191,7 +2225,7 @@ dumpTableData_insert(Archive *fout, const void *dcontext)
21912225
{
21922226
if (field > 0)
21932227
archputs(", ", fout);
2194-
if (tbinfo->attgenerated[field])
2228+
if (attgenerated[field])
21952229
{
21962230
archputs("DEFAULT", fout);
21972231
continue;
@@ -2296,6 +2330,7 @@ dumpTableData_insert(Archive *fout, const void *dcontext)
22962330
destroyPQExpBuffer(q);
22972331
if (insertStmt != NULL)
22982332
destroyPQExpBuffer(insertStmt);
2333+
free(attgenerated);
22992334

23002335
return 1;
23012336
}

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

Lines changed: 68 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -216,6 +216,13 @@
216216
'postgres',
217217
],
218218
},
219+
inserts=> {
220+
dump_cmd=> [
221+
'pg_dump','--no-sync',
222+
"--file=$tempdir/inserts.sql",'-a',
223+
'--inserts','postgres',
224+
],
225+
},
219226
pg_dumpall_globals=> {
220227
dump_cmd=> [
221228
'pg_dumpall','-v',"--file=$tempdir/pg_dumpall_globals.sql",
@@ -610,6 +617,7 @@
610617
%full_runs,
611618
column_inserts=> 1,
612619
data_only=> 1,
620+
inserts=> 1,
613621
section_pre_data=> 1,
614622
test_schema_plus_blobs=> 1,
615623
},
@@ -957,6 +965,7 @@
957965
%full_runs,
958966
column_inserts=> 1,
959967
data_only=> 1,
968+
inserts=> 1,
960969
section_pre_data=> 1,
961970
test_schema_plus_blobs=> 1,
962971
},
@@ -977,6 +986,7 @@
977986
%full_runs,
978987
column_inserts=> 1,
979988
data_only=> 1,
989+
inserts=> 1,
980990
section_data=> 1,
981991
test_schema_plus_blobs=> 1,
982992
},
@@ -1128,6 +1138,7 @@
11281138
%full_runs,
11291139
column_inserts=> 1,
11301140
data_only=> 1,
1141+
inserts=> 1,
11311142
section_pre_data=> 1,
11321143
test_schema_plus_blobs=> 1,
11331144
},
@@ -1327,6 +1338,27 @@
13271338
},
13281339
},
13291340

1341+
'COPY test_third_table'=> {
1342+
create_order=> 7,
1343+
create_sql=>
1344+
'INSERT INTO dump_test.test_third_table VALUES (123, DEFAULT, 456);',
1345+
regexp=>qr/^
1346+
\QCOPY dump_test.test_third_table (f1, "F3") FROM stdin;\E
1347+
\n123\t456\n\\\.\n
1348+
/xm,
1349+
like=> {
1350+
%full_runs,
1351+
%dump_test_schema_runs,
1352+
data_only=> 1,
1353+
section_data=> 1,
1354+
},
1355+
unlike=> {
1356+
binary_upgrade=> 1,
1357+
exclude_dump_test_schema=> 1,
1358+
schema_only=> 1,
1359+
},
1360+
},
1361+
13301362
'COPY test_fourth_table'=> {
13311363
create_order=> 7,
13321364
create_sql=>
@@ -1418,10 +1450,22 @@
14181450
like=> {column_inserts=> 1, },
14191451
},
14201452

1453+
'INSERT INTO test_third_table (colnames)'=> {
1454+
regexp=>
1455+
qr/^INSERT INTO dump_test\.test_third_table\(f1, "F3"\) VALUES\(123, 456\);\n/m,
1456+
like=> {column_inserts=> 1, },
1457+
},
1458+
1459+
'INSERT INTO test_third_table'=> {
1460+
regexp=>
1461+
qr/^INSERT INTO dump_test\.test_third_table VALUES\(123, DEFAULT, 456, DEFAULT\);\n/m,
1462+
like=> {inserts=> 1, },
1463+
},
1464+
14211465
'INSERT INTO test_fourth_table'=> {
14221466
regexp=>
14231467
qr/^(?:INSERT INTO dump_test\.test_fourth_table DEFAULT VALUES;\n){2}/m,
1424-
like=> {column_inserts=> 1,rows_per_insert=> 1, },
1468+
like=> {column_inserts=> 1,inserts=> 1,rows_per_insert=> 1, },
14251469
},
14261470

14271471
'INSERT INTO test_fifth_table'=> {
@@ -2633,6 +2677,28 @@
26332677
like => {}
26342678
},
26352679
2680+
'CREATE TABLE test_third_table_generated_cols' => {
2681+
create_order => 6,
2682+
create_sql => 'CREATE TABLE dump_test.test_third_table (
2683+
f1 int, junk int,
2684+
g1 int generated always as (f1 * 2) stored,
2685+
"F3" int,
2686+
g2 int generated always as ("F3" * 3) stored
2687+
);
2688+
ALTER TABLE dump_test.test_third_table DROP COLUMN junk;',
2689+
regexp => qr/^
2690+
\QCREATE TABLE dump_test.test_third_table (\E\n
2691+
\s+\Qf1 integer,\E\n
2692+
\s+\Qg1 integer GENERATED ALWAYS AS ((f1 * 2)) STORED,\E\n
2693+
\s+\Q"F3" integer,\E\n
2694+
\s+\Qg2 integer GENERATED ALWAYS AS (("F3" * 3)) STORED\E\n
2695+
\);\n
2696+
/xm,
2697+
like =>
2698+
{%full_runs,%dump_test_schema_runs, section_pre_data => 1, },
2699+
unlike => { binary_upgrade => 1, exclude_dump_test_schema => 1, },
2700+
},
2701+
26362702
'CREATE TABLE test_fourth_table_zero_col' => {
26372703
create_order => 6,
26382704
create_sql => 'CREATE TABLE dump_test.test_fourth_table (
@@ -3316,6 +3382,7 @@
33163382
%full_runs,
33173383
column_inserts => 1,
33183384
data_only => 1,
3385+
inserts => 1,
33193386
section_pre_data => 1,
33203387
test_schema_plus_blobs => 1,
33213388
binary_upgrade => 1,

0 commit comments

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