From 4e62091341b4a55666a32dd7762f185a1faa54f7 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 20 Mar 2020 17:21:00 -0300 Subject: [PATCH] pg_dump: Add FOREIGN to ALTER statements, if appropriate MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Author: Luis Carril Reviewed-by: Tomas Vondra, Daniel Gustafsson, Álvaro Herrera Discussion: https://postgr.es/m/LEJPR01MB0185A19B2E7C98E5E2A031F5E7F20@LEJPR01MB0185.DEUPRD01.PROD.OUTLOOK.DE --- src/bin/pg_dump/pg_dump.c | 99 ++++++++++++++++++--------------------- 1 file changed, 46 insertions(+), 53 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index ced0681ec3..812907b88a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -15655,6 +15655,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) { char *ftoptions = NULL; char *srvname = NULL; + char *foreign = ""; switch (tbinfo->relkind) { @@ -15688,6 +15689,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) ftoptions = pg_strdup(PQgetvalue(res, 0, i_ftoptions)); PQclear(res); destroyPQExpBuffer(query); + + foreign = "FOREIGN "; break; } case RELKIND_MATVIEW: @@ -16037,11 +16040,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) continue; appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inherited constraint.\n"); - appendPQExpBuffer(q, "ALTER TABLE ONLY %s ", - qualrelname); - appendPQExpBuffer(q, " ADD CONSTRAINT %s ", - fmtId(constr->dobj.name)); - appendPQExpBuffer(q, "%s;\n", constr->condef); + appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ADD CONSTRAINT %s %s;\n", + foreign, qualrelname, + fmtId(constr->dobj.name), + constr->condef); appendPQExpBufferStr(q, "UPDATE pg_catalog.pg_constraint\n" "SET conislocal = false\n" "WHERE contype = 'c' AND conname = "); @@ -16058,7 +16060,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) { TableInfo *parentRel = parents[k]; - appendPQExpBuffer(q, "ALTER TABLE ONLY %s INHERIT %s;\n", + appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s INHERIT %s;\n", foreign, qualrelname, fmtQualifiedDumpable(parentRel)); } @@ -16163,12 +16165,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) */ if (!shouldPrintColumn(dopt, tbinfo, j) && tbinfo->notnull[j] && !tbinfo->inhNotNull[j]) - { - appendPQExpBuffer(q, "ALTER TABLE ONLY %s ", - qualrelname); - appendPQExpBuffer(q, "ALTER COLUMN %s SET NOT NULL;\n", + appendPQExpBuffer(q, + "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET NOT NULL;\n", + foreign, qualrelname, fmtId(tbinfo->attnames[j])); - } /* * Dump per-column statistics information. We only issue an ALTER @@ -16176,14 +16176,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) * non-negative (i.e. it's not the default value) */ if (tbinfo->attstattarget[j] >= 0) - { - appendPQExpBuffer(q, "ALTER TABLE ONLY %s ", - qualrelname); - appendPQExpBuffer(q, "ALTER COLUMN %s ", - fmtId(tbinfo->attnames[j])); - appendPQExpBuffer(q, "SET STATISTICS %d;\n", + appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET STATISTICS %d;\n", + foreign, qualrelname, + fmtId(tbinfo->attnames[j]), tbinfo->attstattarget[j]); - } /* * Dump per-column storage information. The statement is only @@ -16213,42 +16209,33 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) * Only dump the statement if it's a storage type we recognize */ if (storage != NULL) - { - appendPQExpBuffer(q, "ALTER TABLE ONLY %s ", - qualrelname); - appendPQExpBuffer(q, "ALTER COLUMN %s ", - fmtId(tbinfo->attnames[j])); - appendPQExpBuffer(q, "SET STORAGE %s;\n", + appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET STORAGE %s;\n", + foreign, qualrelname, + fmtId(tbinfo->attnames[j]), storage); - } } /* * Dump per-column attributes. */ if (tbinfo->attoptions[j][0] != '\0') - { - appendPQExpBuffer(q, "ALTER TABLE ONLY %s ", - qualrelname); - appendPQExpBuffer(q, "ALTER COLUMN %s ", - fmtId(tbinfo->attnames[j])); - appendPQExpBuffer(q, "SET (%s);\n", + appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET (%s);\n", + foreign, qualrelname, + fmtId(tbinfo->attnames[j]), tbinfo->attoptions[j]); - } /* * Dump per-column fdw options. */ if (tbinfo->relkind == RELKIND_FOREIGN_TABLE && tbinfo->attfdwoptions[j][0] != '\0') - { - appendPQExpBuffer(q, "ALTER FOREIGN TABLE %s ", - qualrelname); - appendPQExpBuffer(q, "ALTER COLUMN %s ", - fmtId(tbinfo->attnames[j])); - appendPQExpBuffer(q, "OPTIONS (\n %s\n);\n", + appendPQExpBuffer(q, + "ALTER FOREIGN TABLE %s ALTER COLUMN %s OPTIONS (\n" + " %s\n" + ");\n", + qualrelname, + fmtId(tbinfo->attnames[j]), tbinfo->attfdwoptions[j]); - } } if (ftoptions) @@ -16351,6 +16338,7 @@ dumpAttrDef(Archive *fout, AttrDefInfo *adinfo) PQExpBuffer delq; char *qualrelname; char *tag; + char *foreign; /* Skip if table definition not to be dumped */ if (!tbinfo->dobj.dump || dopt->dataOnly) @@ -16365,15 +16353,15 @@ dumpAttrDef(Archive *fout, AttrDefInfo *adinfo) qualrelname = pg_strdup(fmtQualifiedDumpable(tbinfo)); - appendPQExpBuffer(q, "ALTER TABLE ONLY %s ", - qualrelname); - appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n", - fmtId(tbinfo->attnames[adnum - 1]), + foreign = tbinfo->relkind == RELKIND_FOREIGN_TABLE ? "FOREIGN " : ""; + + appendPQExpBuffer(q, + "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET DEFAULT %s;\n", + foreign, qualrelname, fmtId(tbinfo->attnames[adnum - 1]), adinfo->adef_expr); - appendPQExpBuffer(delq, "ALTER TABLE %s ", - qualrelname); - appendPQExpBuffer(delq, "ALTER COLUMN %s DROP DEFAULT;\n", + appendPQExpBuffer(delq, "ALTER %sTABLE %s ALTER COLUMN %s DROP DEFAULT;\n", + foreign, qualrelname, fmtId(tbinfo->attnames[adnum - 1])); tag = psprintf("%s %s", tbinfo->dobj.name, tbinfo->attnames[adnum - 1]); @@ -16683,6 +16671,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) PQExpBuffer q; PQExpBuffer delq; char *tag = NULL; + char *foreign; /* Skip if not to be dumped */ if (!coninfo->dobj.dump || dopt->dataOnly) @@ -16691,6 +16680,9 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) q = createPQExpBuffer(); delq = createPQExpBuffer(); + foreign = tbinfo && + tbinfo->relkind == RELKIND_FOREIGN_TABLE ? "FOREIGN " : ""; + if (coninfo->contype == 'p' || coninfo->contype == 'u' || coninfo->contype == 'x') @@ -16709,7 +16701,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) binary_upgrade_set_pg_class_oids(fout, q, indxinfo->dobj.catId.oid, true); - appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n", + appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s\n", foreign, fmtQualifiedDumpable(tbinfo)); appendPQExpBuffer(q, " ADD CONSTRAINT %s ", fmtId(coninfo->dobj.name)); @@ -16804,7 +16796,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) "pg_catalog.pg_class", "INDEX", fmtQualifiedDumpable(indxinfo)); - appendPQExpBuffer(delq, "ALTER TABLE ONLY %s ", + appendPQExpBuffer(delq, "ALTER %sTABLE ONLY %s ", foreign, fmtQualifiedDumpable(tbinfo)); appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n", fmtId(coninfo->dobj.name)); @@ -16838,13 +16830,13 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that the * current table data is not processed */ - appendPQExpBuffer(q, "ALTER TABLE %s%s\n", + appendPQExpBuffer(q, "ALTER %sTABLE %s%s\n", foreign, only, fmtQualifiedDumpable(tbinfo)); appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n", fmtId(coninfo->dobj.name), coninfo->condef); - appendPQExpBuffer(delq, "ALTER TABLE %s%s ", + appendPQExpBuffer(delq, "ALTER %sTABLE %s%s ", foreign, only, fmtQualifiedDumpable(tbinfo)); appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n", fmtId(coninfo->dobj.name)); @@ -16869,13 +16861,13 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) if (coninfo->separate && coninfo->conislocal) { /* not ONLY since we want it to propagate to children */ - appendPQExpBuffer(q, "ALTER TABLE %s\n", + appendPQExpBuffer(q, "ALTER %sTABLE %s\n", foreign, fmtQualifiedDumpable(tbinfo)); appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n", fmtId(coninfo->dobj.name), coninfo->condef); - appendPQExpBuffer(delq, "ALTER TABLE %s ", + appendPQExpBuffer(delq, "ALTER %sTABLE %s ", foreign, fmtQualifiedDumpable(tbinfo)); appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n", fmtId(coninfo->dobj.name)); @@ -17474,7 +17466,8 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo) if (tginfo->tgenabled != 't' && tginfo->tgenabled != 'O') { - appendPQExpBuffer(query, "\nALTER TABLE %s ", + appendPQExpBuffer(query, "\nALTER %sTABLE %s ", foreign, + tbinfo->relkind == RELKIND_FOREIGN_TABLE ? "FOREIGN " : "", fmtQualifiedDumpable(tbinfo)); switch (tginfo->tgenabled) { -- 2.39.5