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

Commit6a781c4

Browse files
committed
Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise)
Using PARTITION OF can result in column ordering being changed from thedatabase being dumped, if the partition uses a column layout differentfrom the parent's. It's not pg_dump's job to editorialize on tabledefinitions, so this is not acceptable; back-patch all the way back topg10, where partitioned tables where introduced.This change also ensures that partitions end up in the correcttablespace, if different from the parent's; this is an oversight inca41030 (in pg12 only). Partitioned indexes (in pg11) don't havethis problem, because they're already created as independent indexes andattached to their parents afterwards.This change also has the advantage that the partition is restorable fromthe dump (as a standalone table) even if its parent table isn'trestored.The original commits (3b23552 in branch master) failed to coversubsidiary column elements correctly, such as NOT NULL constraint andCHECK constraints, as reported by Rushabh Lathia (initially as a failureto restore serial columns). They were reverted. This recapitulationcommit fixes those problems.Add some pg_dump tests to verify these things more exhaustively,including constraints with legacy-inheritance tables, which were nottested originally. In branches 10 and 11, add a local constraint to thepg_dump test partition that was added by commit2d7eeb1 to master.Author: Álvaro Herrera, David RowleyReviewed-by: Álvaro HerreraDiscussion:https://postgr.es/m/CAKJS1f_1c260nOt_vBJ067AZ3JXptXVRohDVMLEBmudX1YEx-A@mail.gmail.comDiscussion:https://postgr.es/m/20190423185007.GA27954@alvherre.pgsqlDiscussion:https://postgr.es/m/CAGPqQf0iQV=PPOv2Btog9J9AwOQp6HmuVd6SbGTR_v3Zp2XT1w@mail.gmail.com
1 parentbc93a5a commit6a781c4

File tree

2 files changed

+143
-93
lines changed

2 files changed

+143
-93
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 78 additions & 80 deletions
Original file line numberDiff line numberDiff line change
@@ -8638,9 +8638,12 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
86388638
* Normally this is always true, but it's false for dropped columns, as well
86398639
* as those that were inherited without any local definition. (If we print
86408640
* such a column it will mistakenly get pg_attribute.attislocal set to true.)
8641-
* However, in binary_upgrade mode, we must print all such columns anyway and
8642-
* fix the attislocal/attisdropped state later, so as to keep control of the
8643-
* physical column order.
8641+
* For partitions, it's always true, because we want the partitions to be
8642+
* created independently and ATTACH PARTITION used afterwards.
8643+
*
8644+
* In binary_upgrade mode, we must print all columns and fix the attislocal/
8645+
* attisdropped state later, so as to keep control of the physical column
8646+
* order.
86448647
*
86458648
* This function exists because there are scattered nonobvious places that
86468649
* must be kept in sync with this decision.
@@ -8650,7 +8653,9 @@ shouldPrintColumn(DumpOptions *dopt, TableInfo *tbinfo, int colno)
86508653
{
86518654
if (dopt->binary_upgrade)
86528655
return true;
8653-
return (tbinfo->attislocal[colno] && !tbinfo->attisdropped[colno]);
8656+
if (tbinfo->attisdropped[colno])
8657+
return false;
8658+
return (tbinfo->attislocal[colno] || tbinfo->ispartition);
86548659
}
86558660

86568661

@@ -15559,27 +15564,6 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1555915564
if (tbinfo->reloftype && !dopt->binary_upgrade)
1556015565
appendPQExpBuffer(q, " OF %s", tbinfo->reloftype);
1556115566

15562-
/*
15563-
* If the table is a partition, dump it as such; except in the case of
15564-
* a binary upgrade, we dump the table normally and attach it to the
15565-
* parent afterward.
15566-
*/
15567-
if (tbinfo->ispartition && !dopt->binary_upgrade)
15568-
{
15569-
TableInfo *parentRel = tbinfo->parents[0];
15570-
15571-
/*
15572-
* With partitions, unlike inheritance, there can only be one
15573-
* parent.
15574-
*/
15575-
if (tbinfo->numParents != 1)
15576-
exit_horribly(NULL, "invalid number of parents %d for table \"%s\"\n",
15577-
tbinfo->numParents, tbinfo->dobj.name);
15578-
15579-
appendPQExpBuffer(q, " PARTITION OF %s",
15580-
fmtQualifiedDumpable(parentRel));
15581-
}
15582-
1558315567
if (tbinfo->relkind != RELKIND_MATVIEW)
1558415568
{
1558515569
/* Dump the attributes */
@@ -15594,26 +15578,30 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1559415578
*/
1559515579
if (shouldPrintColumn(dopt, tbinfo, j))
1559615580
{
15581+
boolprint_default;
15582+
boolprint_notnull;
15583+
1559715584
/*
1559815585
* Default value --- suppress if to be printed separately.
1559915586
*/
15600-
boolhas_default = (tbinfo->attrdefs[j] != NULL &&
15601-
!tbinfo->attrdefs[j]->separate);
15587+
print_default = (tbinfo->attrdefs[j] != NULL &&
15588+
!tbinfo->attrdefs[j]->separate);
1560215589

1560315590
/*
1560415591
* Not Null constraint --- suppress if inherited, except
15605-
* in binary-upgrade case where that won't work.
15592+
* if partition, or in binary-upgrade case where that
15593+
* won't work.
1560615594
*/
15607-
boolhas_notnull = (tbinfo->notnull[j] &&
15608-
(!tbinfo->inhNotNull[j] ||
15609-
dopt->binary_upgrade));
15595+
print_notnull = (tbinfo->notnull[j] &&
15596+
(!tbinfo->inhNotNull[j] ||
15597+
tbinfo->ispartition ||dopt->binary_upgrade));
1561015598

1561115599
/*
15612-
* Skip column if fully defined by reloftype or the
15613-
*partition parent.
15600+
* Skip column if fully defined by reloftype, except in
15601+
*binary upgrade
1561415602
*/
15615-
if ((tbinfo->reloftype|| tbinfo->ispartition) &&
15616-
!has_default && !has_notnull && !dopt->binary_upgrade)
15603+
if (tbinfo->reloftype&& !print_default && !print_notnull &&
15604+
!dopt->binary_upgrade)
1561715605
continue;
1561815606

1561915607
/* Format properly if not first attr */
@@ -15636,20 +15624,16 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1563615624
* clean things up later.
1563715625
*/
1563815626
appendPQExpBufferStr(q, " INTEGER /* dummy */");
15639-
/*Skip alltherest, too */
15627+
/*and skip tothenext column */
1564015628
continue;
1564115629
}
1564215630

1564315631
/*
15644-
* Attribute type
15645-
*
15646-
* In binary-upgrade mode, we always include the type. If
15647-
* we aren't in binary-upgrade mode, then we skip the type
15648-
* when creating a typed table ('OF type_name') or a
15649-
* partition ('PARTITION OF'), since the type comes from
15650-
* the parent/partitioned table.
15632+
* Attribute type; print it except when creating a typed
15633+
* table ('OF type_name'), but in binary-upgrade mode,
15634+
* print it in that case too.
1565115635
*/
15652-
if (dopt->binary_upgrade ||(!tbinfo->reloftype && !tbinfo->ispartition))
15636+
if (dopt->binary_upgrade || !tbinfo->reloftype)
1565315637
{
1565415638
appendPQExpBuffer(q, " %s",
1565515639
tbinfo->atttypnames[j]);
@@ -15666,23 +15650,29 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1566615650
fmtQualifiedDumpable(coll));
1566715651
}
1566815652

15669-
if (has_default)
15653+
if (print_default)
1567015654
appendPQExpBuffer(q, " DEFAULT %s",
1567115655
tbinfo->attrdefs[j]->adef_expr);
1567215656

15673-
if (has_notnull)
15657+
if (print_notnull)
1567415658
appendPQExpBufferStr(q, " NOT NULL");
1567515659
}
1567615660
}
1567715661

1567815662
/*
1567915663
* Add non-inherited CHECK constraints, if any.
15664+
*
15665+
* For partitions, we need to include check constraints even if
15666+
* they're not defined locally, because the ALTER TABLE ATTACH
15667+
* PARTITION that we'll emit later expects the constraint to be
15668+
* there. (No need to fix conislocal: ATTACH PARTITION does that)
1568015669
*/
1568115670
for (j = 0; j < tbinfo->ncheck; j++)
1568215671
{
1568315672
ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
1568415673

15685-
if (constr->separate || !constr->conislocal)
15674+
if (constr->separate ||
15675+
(!constr->conislocal && !tbinfo->ispartition))
1568615676
continue;
1568715677

1568815678
if (actual_atts == 0)
@@ -15699,25 +15689,20 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1569915689

1570015690
if (actual_atts)
1570115691
appendPQExpBufferStr(q, "\n)");
15702-
else if (!((tbinfo->reloftype || tbinfo->ispartition) &&
15703-
!dopt->binary_upgrade))
15692+
else if (!(tbinfo->reloftype && !dopt->binary_upgrade))
1570415693
{
1570515694
/*
15706-
*Wemust have a parenthesized attribute list, even though
15707-
* empty, when not using the OF TYPE or PARTITION OF syntax.
15695+
*No attributes? wemust have a parenthesized attribute list,
15696+
*even thoughempty, when not using the OF TYPE syntax.
1570815697
*/
1570915698
appendPQExpBufferStr(q, " (\n)");
1571015699
}
1571115700

15712-
if (tbinfo->ispartition && !dopt->binary_upgrade)
15713-
{
15714-
appendPQExpBufferChar(q, '\n');
15715-
appendPQExpBufferStr(q, tbinfo->partbound);
15716-
}
15717-
15718-
/* Emit the INHERITS clause, except if this is a partition. */
15719-
if (numParents > 0 &&
15720-
!tbinfo->ispartition &&
15701+
/*
15702+
* Emit the INHERITS clause (not for partitions), except in
15703+
* binary-upgrade mode.
15704+
*/
15705+
if (numParents > 0 && !tbinfo->ispartition &&
1572115706
!dopt->binary_upgrade)
1572215707
{
1572315708
appendPQExpBufferStr(q, "\nINHERITS (");
@@ -15868,11 +15853,17 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1586815853
}
1586915854
}
1587015855

15856+
/*
15857+
* Add inherited CHECK constraints, if any.
15858+
*
15859+
* For partitions, they were already dumped, and conislocal
15860+
* doesn't need fixing.
15861+
*/
1587115862
for (k = 0; k < tbinfo->ncheck; k++)
1587215863
{
1587315864
ConstraintInfo *constr = &(tbinfo->checkexprs[k]);
1587415865

15875-
if (constr->separate || constr->conislocal)
15866+
if (constr->separate || constr->conislocal || tbinfo->ispartition)
1587615867
continue;
1587715868

1587815869
appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inherited constraint.\n");
@@ -15890,30 +15881,16 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1589015881
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
1589115882
}
1589215883

15893-
if (numParents > 0)
15884+
if (numParents > 0 && !tbinfo->ispartition)
1589415885
{
15895-
appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inheritanceand partitioningthis way.\n");
15886+
appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inheritance this way.\n");
1589615887
for (k = 0; k < numParents; k++)
1589715888
{
1589815889
TableInfo *parentRel = parents[k];
1589915890

15900-
/* In the partitioning case, we alter the parent */
15901-
if (tbinfo->ispartition)
15902-
appendPQExpBuffer(q,
15903-
"ALTER TABLE ONLY %s ATTACH PARTITION ",
15904-
fmtQualifiedDumpable(parentRel));
15905-
else
15906-
appendPQExpBuffer(q, "ALTER TABLE ONLY %s INHERIT ",
15907-
qualrelname);
15908-
15909-
/* Partition needs specifying the bounds */
15910-
if (tbinfo->ispartition)
15911-
appendPQExpBuffer(q, "%s %s;\n",
15912-
qualrelname,
15913-
tbinfo->partbound);
15914-
else
15915-
appendPQExpBuffer(q, "%s;\n",
15916-
fmtQualifiedDumpable(parentRel));
15891+
appendPQExpBuffer(q, "ALTER TABLE ONLY %s INHERIT %s;\n",
15892+
qualrelname,
15893+
fmtQualifiedDumpable(parentRel));
1591715894
}
1591815895
}
1591915896

@@ -15926,6 +15903,27 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
1592615903
}
1592715904
}
1592815905

15906+
/*
15907+
* For partitioned tables, emit the ATTACH PARTITION clause. Note
15908+
* that we always want to create partitions this way instead of using
15909+
* CREATE TABLE .. PARTITION OF, mainly to preserve a possible column
15910+
* layout discrepancy with the parent, but also to ensure it gets the
15911+
* correct tablespace setting if it differs from the parent's.
15912+
*/
15913+
if (tbinfo->ispartition)
15914+
{
15915+
/* With partitions there can only be one parent */
15916+
if (tbinfo->numParents != 1)
15917+
exit_horribly(NULL, "invalid number of parents %d for table \"%s\"\n",
15918+
tbinfo->numParents, tbinfo->dobj.name);
15919+
15920+
/* Perform ALTER TABLE on the parent */
15921+
appendPQExpBuffer(q,
15922+
"ALTER TABLE ONLY %s ATTACH PARTITION %s %s;\n",
15923+
fmtQualifiedDumpable(parents[0]),
15924+
qualrelname, tbinfo->partbound);
15925+
}
15926+
1592915927
/*
1593015928
* In binary_upgrade mode, arrange to restore the old relfrozenxid and
1593115929
* relminmxid of all vacuumable relations. (While vacuum.c processes

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

Lines changed: 65 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -732,7 +732,12 @@
732732
\QALTER TABLE ONLY dump_test.measurement ATTACH PARTITION dump_test_second_schema.measurement_y2006m2\E
733733
\QFOR VALUES FROM ('2006-02-01') TO ('2006-03-01');\E\n
734734
/xm,
735-
like=> {binary_upgrade=> 1, },
735+
like=> {
736+
%full_runs,
737+
role=> 1,
738+
section_pre_data=> 1,
739+
binary_upgrade=> 1,
740+
},
736741
},
737742

738743
'ALTER TABLE test_table CLUSTER ON test_table_pkey'=> {
@@ -2314,9 +2319,9 @@
23142319
'CREATE TABLE measurement PARTITIONED BY' => {
23152320
create_order => 90,
23162321
create_sql => 'CREATE TABLE dump_test.measurement (
2317-
city_idint not null,
2322+
city_idserial not null,
23182323
logdate date not null,
2319-
peaktemp int,
2324+
peaktemp int CHECK (peaktemp >= -460),
23202325
unitsales int
23212326
) PARTITION BY RANGE (logdate);',
23222327
regexp => qr/^
@@ -2326,7 +2331,8 @@
23262331
\s+\Qcity_id integer NOT NULL,\E\n
23272332
\s+\Qlogdate date NOT NULL,\E\n
23282333
\s+\Qpeaktemp integer,\E\n
2329-
\s+\Qunitsales integer\E\n
2334+
\s+\Qunitsales integer,\E\n
2335+
\s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer))\E\n
23302336
\)\n
23312337
\QPARTITION BY RANGE (logdate);\E\n
23322338
/xm,
@@ -2338,24 +2344,30 @@
23382344
},
23392345
},
23402346
2341-
'CREATE TABLEmeasurement_y2006m2PARTITION OF' => {
2347+
'Partitionmeasurement_y2006m2creation' => {
23422348
create_order => 91,
23432349
create_sql =>
23442350
'CREATE TABLE dump_test_second_schema.measurement_y2006m2
2345-
PARTITION OF dump_test.measurement FOR VALUES
2346-
FROM (\'2006-02-01\') TO (\'2006-03-01\');',
2351+
PARTITION OF dump_test.measurement (
2352+
unitsales DEFAULT 0 CHECK (unitsales >= 0)
2353+
)
2354+
FOR VALUES FROM (\'2006-02-01\') TO (\'2006-03-01\');',
23472355
regexp => qr/^
2348-
\Q-- Name: measurement_y2006m2;\E.*\n
2349-
\Q--\E\n\n
2350-
\QCREATE TABLE dump_test_second_schema.measurement_y2006m2 PARTITION OF dump_test.measurement\E\n
2351-
\QFOR VALUES FROM ('2006-02-01') TO ('2006-03-01');\E\n
2356+
\QCREATE TABLE dump_test_second_schema.measurement_y2006m2 (\E\n
2357+
\s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass) NOT NULL,\E\n
2358+
\s+\Qlogdate date NOT NULL,\E\n
2359+
\s+\Qpeaktemp integer,\E\n
2360+
\s+\Qunitsales integer DEFAULT 0,\E\n
2361+
\s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer)),\E\n
2362+
\s+\QCONSTRAINT measurement_y2006m2_unitsales_check CHECK ((unitsales >= 0))\E\n
2363+
\);\n
23522364
/xm,
23532365
like => {
23542366
%full_runs,
2355-
role => 1,
23562367
section_pre_data => 1,
2368+
role => 1,
2369+
binary_upgrade => 1,
23572370
},
2358-
unlike => { binary_upgrade => 1, },
23592371
},
23602372
23612373
'CREATE TABLE test_fourth_table_zero_col' => {
@@ -2442,6 +2454,46 @@
24422454
unlike => { exclude_dump_test_schema => 1, },
24432455
},
24442456
2457+
'CREATE TABLE test_inheritance_parent' => {
2458+
create_order => 90,
2459+
create_sql => 'CREATE TABLE dump_test.test_inheritance_parent (
2460+
col1 int NOT NULL,
2461+
col2 int CHECK (col2 >= 42)
2462+
);',
2463+
regexp => qr/^
2464+
\QCREATE TABLE dump_test.test_inheritance_parent (\E\n
2465+
\s+\Qcol1 integer NOT NULL,\E\n
2466+
\s+\Qcol2 integer,\E\n
2467+
\s+\QCONSTRAINT test_inheritance_parent_col2_check CHECK ((col2 >= 42))\E\n
2468+
\Q);\E\n
2469+
/xm,
2470+
like =>
2471+
{%full_runs,%dump_test_schema_runs, section_pre_data => 1, },
2472+
unlike => { exclude_dump_test_schema => 1, },
2473+
},
2474+
2475+
'CREATE TABLE test_inheritance_child' => {
2476+
create_order => 91,
2477+
create_sql => 'CREATE TABLE dump_test.test_inheritance_child (
2478+
col1 int NOT NULL,
2479+
CONSTRAINT test_inheritance_child CHECK (col2 >= 142857)
2480+
) INHERITS (dump_test.test_inheritance_parent);',
2481+
regexp => qr/^
2482+
\QCREATE TABLE dump_test.test_inheritance_child (\E\n
2483+
\s+\Qcol1 integer,\E\n
2484+
\s+\QCONSTRAINT test_inheritance_child CHECK ((col2 >= 142857))\E\n
2485+
\)\n
2486+
\QINHERITS (dump_test.test_inheritance_parent);\E\n
2487+
/xm,
2488+
like => {
2489+
%full_runs,%dump_test_schema_runs, section_pre_data => 1,
2490+
},
2491+
unlike => {
2492+
binary_upgrade => 1,
2493+
exclude_dump_test_schema => 1,
2494+
},
2495+
},
2496+
24452497
'CREATE STATISTICS extended_stats_no_options' => {
24462498
create_order => 97,
24472499
create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options

0 commit comments

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