In certain cases, pg_dump's dumpTableSchema() emits a separate ALTER TABLE
command for those schema elements of a table that could not be included
directly in the CREATE TABLE command for the table.
For example:
create table p (a int, b int) partition by range (a);
create table p1 partition of p for values from (1) to (10) partition by
range (b);
create table p11 partition of p1 for values from (1) to (10);
pg_dump -s gives:
CREATE TABLE p (
a integer NOT NULL,
b integer
)
PARTITION BY RANGE (a);
CREATE TABLE p1 PARTITION OF p
FOR VALUES FROM (1) TO (10)
PARTITION BY RANGE (b);
ALTER TABLE ONLY p1 ALTER COLUMN a SET NOT NULL;
ALTER TABLE ONLY p1 ALTER COLUMN b SET NOT NULL;
<snip>
Note the ONLY in the above emitted command. Now if I run the above
commands in another database, the following error occurs:
ERROR: constraint must be added to child tables too
That's because specifying ONLY for the AT commands on partitioned tables
that must recurse causes an error.
Attached patch fixes that - it prevents emitting ONLY for those ALTER
TABLE commands, which if run, would cause an error like the one above.
Thanks,
Amit
>From a1b73a072ee366d8473289ed208b3bf9e7190312 Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Tue, 14 Feb 2017 10:48:26 +0900
Subject: [PATCH] pg_dump: do not emit ALTER TABLE ONLY for partitioned tables
At least for cases where the inheritance recursion must take place,
such as dropping a column, adding a constraint, setting a column
NOT NULL.
---
src/bin/pg_dump/pg_dump.c | 25 ++++++++++++++++++-------
1 file changed, 18 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7364a12c25..a077606b85 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15114,10 +15114,13 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
- if (tbinfo->relkind == RELKIND_RELATION ||
- tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ if (tbinfo->relkind == RELKIND_RELATION)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
+ /* attribute must be dropped in partitions too */
+ else if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "ALTER TABLE %s ",
+ fmtId(tbinfo->dobj.name));
else
appendPQExpBuffer(q, "ALTER FOREIGN TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
@@ -15145,8 +15148,12 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
continue;
appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inherited constraint.\n");
- appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
- fmtId(tbinfo->dobj.name));
+ if (tbinfo->relkind == RELKIND_RELATION)
+ appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
+ fmtId(tbinfo->dobj.name));
+ else if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "ALTER TABLE %s ",
+ fmtId(tbinfo->dobj.name));
appendPQExpBuffer(q, " ADD CONSTRAINT %s ",
fmtId(constr->dobj.name));
appendPQExpBuffer(q, "%s;\n", constr->condef);
@@ -15187,7 +15194,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
if (tbinfo->partitionOf)
{
appendPQExpBufferStr(q, "\n-- For binary upgrade, set up partitions this way.\n");
- appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
+ appendPQExpBuffer(q, "ALTER TABLE %s ",
fmtId(tbinfo->partitionOf->dobj.name));
appendPQExpBuffer(q, "ATTACH PARTITION %s %s;\n",
fmtId(tbinfo->dobj.name),
@@ -15249,8 +15256,12 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
if (!shouldPrintColumn(dopt, tbinfo, j) &&
tbinfo->notnull[j] && !tbinfo->inhNotNull[j])
{
- appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
- fmtId(tbinfo->dobj.name));
+ if (tbinfo->relkind == RELKIND_RELATION)
+ appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
+ fmtId(tbinfo->dobj.name));
+ else if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ appendPQExpBuffer(q, "ALTER TABLE %s ",
+ fmtId(tbinfo->dobj.name));
appendPQExpBuffer(q, "ALTER COLUMN %s SET NOT NULL;\n",
fmtId(tbinfo->attnames[j]));
}
--
2.11.0
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
