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

Commit2fa989e

Browse files
committed
Reduce number of commands dumpTableSchema emits for binary upgrade.
Avoid issuing a separate SQL UPDATE command for each column whendirectly manipulating pg_attribute contents in binary upgrade mode.With the separate updates, we triggered a relcache invalidation witheach update. For a table with N columns, that causes O(N^2) relcachebloat in txn_size mode because the table's newly-created relcacheentry can't be flushed till end of transaction. Reducing the numberof commands should make it marginally faster as well as avoiding thatproblem.While at it, likewise avoid issuing a separate UPDATE on pg_constraintfor each inherited constraint. This is less exciting, first becauseinherited (non-partitioned) constraints are relatively rare, andsecond because the backend has a good deal of trouble anyway withrestoring tables containing many such constraints, due toMergeConstraintsIntoExisting being horribly inefficient. But it seemsmore consistent to do it this way here too, and it surely can't hurt.In passing, fix one place in dumpTableSchema that failed to use ONLYin ALTER TABLE. That's not a live bug, but it's inconsistent.Also avoid silently casting away const from string literals.Per report from Justin Pryzby. Back-patch to v17 where txn_size modewas introduced.Discussion:https://postgr.es/m/ZqEND4ZcTDBmcv31@pryzbyj2023
1 parent1e02025 commit2fa989e

File tree

2 files changed

+99
-37
lines changed

2 files changed

+99
-37
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 98 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -15764,6 +15764,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
1576415764
DumpOptions *dopt = fout->dopt;
1576515765
PQExpBuffer q = createPQExpBuffer();
1576615766
PQExpBuffer delq = createPQExpBuffer();
15767+
PQExpBuffer extra = createPQExpBuffer();
1576715768
char *qrelname;
1576815769
char *qualrelname;
1576915770
intnumParents;
@@ -15830,7 +15831,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
1583015831
char *partkeydef = NULL;
1583115832
char *ftoptions = NULL;
1583215833
char *srvname = NULL;
15833-
char *foreign = "";
15834+
const char *foreign = "";
1583415835

1583515836
/*
1583615837
* Set reltypename, and collect any relkind-specific data that we
@@ -16188,70 +16189,130 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
1618816189
tbinfo->relkind == RELKIND_FOREIGN_TABLE ||
1618916190
tbinfo->relkind == RELKIND_PARTITIONED_TABLE))
1619016191
{
16192+
boolfirstitem;
16193+
16194+
/*
16195+
* Drop any dropped columns. Merge the pg_attribute manipulations
16196+
* into a single SQL command, so that we don't cause repeated
16197+
* relcache flushes on the target table. Otherwise we risk O(N^2)
16198+
* relcache bloat while dropping N columns.
16199+
*/
16200+
resetPQExpBuffer(extra);
16201+
firstitem = true;
1619116202
for (j = 0; j < tbinfo->numatts; j++)
1619216203
{
1619316204
if (tbinfo->attisdropped[j])
1619416205
{
16195-
appendPQExpBufferStr(q, "\n-- For binary upgrade, recreate dropped column.\n");
16196-
appendPQExpBuffer(q, "UPDATE pg_catalog.pg_attribute\n"
16197-
"SET attlen = %d, "
16198-
"attalign = '%c', attbyval = false\n"
16199-
"WHERE attname = ",
16206+
if (firstitem)
16207+
{
16208+
appendPQExpBufferStr(q, "\n-- For binary upgrade, recreate dropped columns.\n"
16209+
"UPDATE pg_catalog.pg_attribute\n"
16210+
"SET attlen = v.dlen, "
16211+
"attalign = v.dalign, "
16212+
"attbyval = false\n"
16213+
"FROM (VALUES ");
16214+
firstitem = false;
16215+
}
16216+
else
16217+
appendPQExpBufferStr(q, ",\n ");
16218+
appendPQExpBufferChar(q, '(');
16219+
appendStringLiteralAH(q, tbinfo->attnames[j], fout);
16220+
appendPQExpBuffer(q, ", %d, '%c')",
1620016221
tbinfo->attlen[j],
1620116222
tbinfo->attalign[j]);
16202-
appendStringLiteralAH(q, tbinfo->attnames[j], fout);
16203-
appendPQExpBufferStr(q, "\n AND attrelid = ");
16204-
appendStringLiteralAH(q, qualrelname, fout);
16205-
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
16206-
16207-
if (tbinfo->relkind == RELKIND_RELATION ||
16208-
tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
16209-
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
16210-
qualrelname);
16211-
else
16212-
appendPQExpBuffer(q, "ALTER FOREIGN TABLE ONLY %s ",
16213-
qualrelname);
16214-
appendPQExpBuffer(q, "DROP COLUMN %s;\n",
16223+
/* The ALTER ... DROP COLUMN commands must come after */
16224+
appendPQExpBuffer(extra, "ALTER %sTABLE ONLY %s ",
16225+
foreign, qualrelname);
16226+
appendPQExpBuffer(extra, "DROP COLUMN %s;\n",
1621516227
fmtId(tbinfo->attnames[j]));
1621616228
}
16217-
else if (!tbinfo->attislocal[j])
16229+
}
16230+
if (!firstitem)
16231+
{
16232+
appendPQExpBufferStr(q, ") v(dname, dlen, dalign)\n"
16233+
"WHERE attrelid = ");
16234+
appendStringLiteralAH(q, qualrelname, fout);
16235+
appendPQExpBufferStr(q, "::pg_catalog.regclass\n"
16236+
" AND attname = v.dname;\n");
16237+
/* Now we can issue the actual DROP COLUMN commands */
16238+
appendBinaryPQExpBuffer(q, extra->data, extra->len);
16239+
}
16240+
16241+
/*
16242+
* Fix up inherited columns. As above, do the pg_attribute
16243+
* manipulations in a single SQL command.
16244+
*/
16245+
firstitem = true;
16246+
for (j = 0; j < tbinfo->numatts; j++)
16247+
{
16248+
if (!tbinfo->attisdropped[j] &&
16249+
!tbinfo->attislocal[j])
1621816250
{
16219-
appendPQExpBufferStr(q, "\n-- For binary upgrade, recreate inherited column.\n");
16220-
appendPQExpBufferStr(q, "UPDATE pg_catalog.pg_attribute\n"
16221-
"SET attislocal = false\n"
16222-
"WHERE attname = ");
16251+
if (firstitem)
16252+
{
16253+
appendPQExpBufferStr(q, "\n-- For binary upgrade, recreate inherited columns.\n");
16254+
appendPQExpBufferStr(q, "UPDATE pg_catalog.pg_attribute\n"
16255+
"SET attislocal = false\n"
16256+
"WHERE attrelid = ");
16257+
appendStringLiteralAH(q, qualrelname, fout);
16258+
appendPQExpBufferStr(q, "::pg_catalog.regclass\n"
16259+
" AND attname IN (");
16260+
firstitem = false;
16261+
}
16262+
else
16263+
appendPQExpBufferStr(q, ", ");
1622316264
appendStringLiteralAH(q, tbinfo->attnames[j], fout);
16224-
appendPQExpBufferStr(q, "\n AND attrelid = ");
16225-
appendStringLiteralAH(q, qualrelname, fout);
16226-
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
1622716265
}
1622816266
}
16267+
if (!firstitem)
16268+
appendPQExpBufferStr(q, ");\n");
1622916269

1623016270
/*
1623116271
* Add inherited CHECK constraints, if any.
1623216272
*
1623316273
* For partitions, they were already dumped, and conislocal
1623416274
* doesn't need fixing.
16275+
*
16276+
* As above, issue only one direct manipulation of pg_constraint.
16277+
* Although it is tempting to merge the ALTER ADD CONSTRAINT
16278+
* commands into one as well, refrain for now due to concern about
16279+
* possible backend memory bloat if there are many such
16280+
* constraints.
1623516281
*/
16282+
resetPQExpBuffer(extra);
16283+
firstitem = true;
1623616284
for (k = 0; k < tbinfo->ncheck; k++)
1623716285
{
1623816286
ConstraintInfo *constr = &(tbinfo->checkexprs[k]);
1623916287

1624016288
if (constr->separate || constr->conislocal || tbinfo->ispartition)
1624116289
continue;
1624216290

16243-
appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inherited constraint.\n");
16291+
if (firstitem)
16292+
appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inherited constraints.\n");
1624416293
appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ADD CONSTRAINT %s %s;\n",
1624516294
foreign, qualrelname,
1624616295
fmtId(constr->dobj.name),
1624716296
constr->condef);
16248-
appendPQExpBufferStr(q, "UPDATE pg_catalog.pg_constraint\n"
16249-
"SET conislocal = false\n"
16250-
"WHERE contype = 'c' AND conname = ");
16251-
appendStringLiteralAH(q, constr->dobj.name, fout);
16252-
appendPQExpBufferStr(q, "\n AND conrelid = ");
16253-
appendStringLiteralAH(q, qualrelname, fout);
16254-
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
16297+
/* Update pg_constraint after all the ALTER TABLEs */
16298+
if (firstitem)
16299+
{
16300+
appendPQExpBufferStr(extra, "UPDATE pg_catalog.pg_constraint\n"
16301+
"SET conislocal = false\n"
16302+
"WHERE contype = 'c' AND conrelid = ");
16303+
appendStringLiteralAH(extra, qualrelname, fout);
16304+
appendPQExpBufferStr(extra, "::pg_catalog.regclass\n");
16305+
appendPQExpBufferStr(extra, " AND conname IN (");
16306+
firstitem = false;
16307+
}
16308+
else
16309+
appendPQExpBufferStr(extra, ", ");
16310+
appendStringLiteralAH(extra, constr->dobj.name, fout);
16311+
}
16312+
if (!firstitem)
16313+
{
16314+
appendPQExpBufferStr(extra, ");\n");
16315+
appendBinaryPQExpBuffer(q, extra->data, extra->len);
1625516316
}
1625616317

1625716318
if (numParents > 0 && !tbinfo->ispartition)
@@ -16438,7 +16499,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
1643816499
if (tbinfo->relkind == RELKIND_FOREIGN_TABLE &&
1643916500
tbinfo->attfdwoptions[j][0] != '\0')
1644016501
appendPQExpBuffer(q,
16441-
"ALTER FOREIGN TABLE %s ALTER COLUMN %s OPTIONS (\n"
16502+
"ALTER FOREIGN TABLEONLY%s ALTER COLUMN %s OPTIONS (\n"
1644216503
" %s\n"
1644316504
");\n",
1644416505
qualrelname,
@@ -16539,6 +16600,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
1653916600

1654016601
destroyPQExpBuffer(q);
1654116602
destroyPQExpBuffer(delq);
16603+
destroyPQExpBuffer(extra);
1654216604
free(qrelname);
1654316605
free(qualrelname);
1654416606
}

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1154,7 +1154,7 @@
11541154

11551155
'ALTER FOREIGN TABLE foreign_table ALTER COLUMN c1 OPTIONS'=> {
11561156
regexp=>qr/^
1157-
\QALTER FOREIGN TABLE dump_test.foreign_table ALTER COLUMN c1 OPTIONS (\E\n
1157+
\QALTER FOREIGN TABLEONLYdump_test.foreign_table ALTER COLUMN c1 OPTIONS (\E\n
11581158
\s+\Qcolumn_name 'col1'\E\n
11591159
\Q);\E\n
11601160
/xm,

0 commit comments

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