Hello,
Restoring a backup generated with pg_dump/pg_dumpall in plain text
format and the --clean option will report errors if the backup is loaded
in an other or empty database. I mean that the backup file contains all
SQL order to drop the database's objects before recreating them, so if
you load this backup into a new database it will throw errors on each
DROP call complaining that the objects doesn't exists.
This is not very important because everything goes fine but these error
reports can be easily prevented with the addition of IF EXISTS clauses
and this will probably be less confusing. I've attached a patch adding
those IF EXISTS on each DROP and ALTER statements.
Best regards,
--
Gilles Darold
http://dalibo.com - http://dalibo.org
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9920d96..e61cdd6 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1965,7 +1965,7 @@ dumpDatabase(Archive *fout)
}
- appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
+ appendPQExpBuffer(delQry, "DROP DATABASE IF EXISTS %s;\n",
fmtId(datname));
dbDumpId = createDumpId();
@@ -7345,7 +7345,7 @@ dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
qnspname = pg_strdup(fmtId(nspinfo->dobj.name));
- appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
+ appendPQExpBuffer(delq, "DROP SCHEMA IF EXISTS %s;\n", qnspname);
appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
@@ -7404,7 +7404,7 @@ dumpExtension(Archive *fout, ExtensionInfo *extinfo)
qextname = pg_strdup(fmtId(extinfo->dobj.name));
- appendPQExpBuffer(delq, "DROP EXTENSION %s;\n", qextname);
+ appendPQExpBuffer(delq, "DROP EXTENSION IF EXISTS %s;\n", qextname);
if (!binary_upgrade)
{
@@ -7575,7 +7575,7 @@ dumpEnumType(Archive *fout, TypeInfo *tyinfo)
* CASCADE shouldn't be required here as for normal types since the I/O
* functions are generic and do not get dropped.
*/
- appendPQExpBuffer(delq, "DROP TYPE %s.",
+ appendPQExpBuffer(delq, "DROP TYPE IF EXISTS %s.",
fmtId(tyinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s;\n",
fmtId(tyinfo->dobj.name));
@@ -7697,7 +7697,7 @@ dumpRangeType(Archive *fout, TypeInfo *tyinfo)
* CASCADE shouldn't be required here as for normal types since the I/O
* functions are generic and do not get dropped.
*/
- appendPQExpBuffer(delq, "DROP TYPE %s.",
+ appendPQExpBuffer(delq, "DROP TYPE IF EXISTS %s.",
fmtId(tyinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s;\n",
fmtId(tyinfo->dobj.name));
@@ -8029,7 +8029,7 @@ dumpBaseType(Archive *fout, TypeInfo *tyinfo)
* the type and its I/O functions makes it impossible to drop the type any
* other way.
*/
- appendPQExpBuffer(delq, "DROP TYPE %s.",
+ appendPQExpBuffer(delq, "DROP TYPE IF EXISTS %s.",
fmtId(tyinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s CASCADE;\n",
fmtId(tyinfo->dobj.name));
@@ -8281,7 +8281,7 @@ dumpDomain(Archive *fout, TypeInfo *tyinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP DOMAIN %s.",
+ appendPQExpBuffer(delq, "DROP DOMAIN IF EXISTS %s.",
fmtId(tyinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s;\n",
fmtId(tyinfo->dobj.name));
@@ -8794,7 +8794,7 @@ dumpProcLang(Archive *fout, ProcLangInfo *plang)
else
lanschema = NULL;
- appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n",
+ appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE IF EXISTS %s;\n",
qlanname);
if (useParams)
@@ -9335,7 +9335,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
+ appendPQExpBuffer(delqry, "DROP FUNCTION IF EXISTS %s.%s;\n",
fmtId(finfo->dobj.namespace->dobj.name),
funcsig);
@@ -9553,7 +9553,7 @@ dumpCast(Archive *fout, CastInfo *cast)
delqry = createPQExpBuffer();
labelq = createPQExpBuffer();
- appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n",
+ appendPQExpBuffer(delqry, "DROP CAST IF EXISTS (%s AS %s);\n",
getFormattedTypeName(fout, cast->castsource, zeroAsNone),
getFormattedTypeName(fout, cast->casttarget, zeroAsNone));
@@ -9823,7 +9823,7 @@ dumpOpr(Archive *fout, OprInfo *oprinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n",
+ appendPQExpBuffer(delq, "DROP OPERATOR IF EXISTS %s.%s;\n",
fmtId(oprinfo->dobj.namespace->dobj.name),
oprid->data);
@@ -10109,7 +10109,7 @@ dumpOpclass(Archive *fout, OpclassInfo *opcinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s",
+ appendPQExpBuffer(delq, "DROP OPERATOR CLASS IF EXISTS %s",
fmtId(opcinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s",
fmtId(opcinfo->dobj.name));
@@ -10553,7 +10553,7 @@ dumpOpfamily(Archive *fout, OpfamilyInfo *opfinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP OPERATOR FAMILY %s",
+ appendPQExpBuffer(delq, "DROP OPERATOR FAMILY IF EXISTS %s",
fmtId(opfinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s",
fmtId(opfinfo->dobj.name));
@@ -10727,7 +10727,7 @@ dumpCollation(Archive *fout, CollInfo *collinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP COLLATION %s",
+ appendPQExpBuffer(delq, "DROP COLLATION IF EXISTS %s",
fmtId(collinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s;\n",
fmtId(collinfo->dobj.name));
@@ -10824,7 +10824,7 @@ dumpConversion(Archive *fout, ConvInfo *convinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP CONVERSION %s",
+ appendPQExpBuffer(delq, "DROP CONVERSION IF EXISTS %s",
fmtId(convinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s;\n",
fmtId(convinfo->dobj.name));
@@ -11068,7 +11068,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
+ appendPQExpBuffer(delq, "DROP AGGREGATE IF EXISTS %s.%s;\n",
fmtId(agginfo->aggfn.dobj.namespace->dobj.name),
aggsig);
@@ -11167,7 +11167,7 @@ dumpTSParser(Archive *fout, TSParserInfo *prsinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP TEXT SEARCH PARSER %s",
+ appendPQExpBuffer(delq, "DROP TEXT SEARCH PARSER IF EXISTS %s",
fmtId(prsinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s;\n",
fmtId(prsinfo->dobj.name));
@@ -11254,7 +11254,7 @@ dumpTSDictionary(Archive *fout, TSDictInfo *dictinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP TEXT SEARCH DICTIONARY %s",
+ appendPQExpBuffer(delq, "DROP TEXT SEARCH DICTIONARY IF EXISTS %s",
fmtId(dictinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s;\n",
fmtId(dictinfo->dobj.name));
@@ -11320,7 +11320,7 @@ dumpTSTemplate(Archive *fout, TSTemplateInfo *tmplinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP TEXT SEARCH TEMPLATE %s",
+ appendPQExpBuffer(delq, "DROP TEXT SEARCH TEMPLATE IF EXISTS %s",
fmtId(tmplinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s;\n",
fmtId(tmplinfo->dobj.name));
@@ -11448,7 +11448,7 @@ dumpTSConfig(Archive *fout, TSConfigInfo *cfginfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "DROP TEXT SEARCH CONFIGURATION %s",
+ appendPQExpBuffer(delq, "DROP TEXT SEARCH CONFIGURATION IF EXISTS %s",
fmtId(cfginfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, ".%s;\n",
fmtId(cfginfo->dobj.name));
@@ -11524,7 +11524,7 @@ dumpForeignDataWrapper(Archive *fout, FdwInfo *fdwinfo)
appendPQExpBuffer(q, ";\n");
- appendPQExpBuffer(delq, "DROP FOREIGN DATA WRAPPER %s;\n",
+ appendPQExpBuffer(delq, "DROP FOREIGN DATA WRAPPER IF EXISTS %s;\n",
qfdwname);
appendPQExpBuffer(labelq, "FOREIGN DATA WRAPPER %s",
@@ -11617,7 +11617,7 @@ dumpForeignServer(Archive *fout, ForeignServerInfo *srvinfo)
appendPQExpBuffer(q, ";\n");
- appendPQExpBuffer(delq, "DROP SERVER %s;\n",
+ appendPQExpBuffer(delq, "DROP SERVER IF EXISTS %s;\n",
qsrvname);
appendPQExpBuffer(labelq, "SERVER %s", qsrvname);
@@ -11735,7 +11735,7 @@ dumpUserMappings(Archive *fout,
appendPQExpBuffer(q, ";\n");
resetPQExpBuffer(delq);
- appendPQExpBuffer(delq, "DROP USER MAPPING FOR %s", fmtId(usename));
+ appendPQExpBuffer(delq, "DROP USER MAPPING IF EXISTS FOR %s", fmtId(usename));
appendPQExpBuffer(delq, " SERVER %s;\n", fmtId(servername));
resetPQExpBuffer(tag);
@@ -12319,7 +12319,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* DROP must be fully qualified in case same name appears in
* pg_catalog
*/
- appendPQExpBuffer(delq, "DROP VIEW %s.",
+ appendPQExpBuffer(delq, "DROP VIEW IF EXISTS %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s;\n",
fmtId(tbinfo->dobj.name));
@@ -12381,7 +12381,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* DROP must be fully qualified in case same name appears in
* pg_catalog
*/
- appendPQExpBuffer(delq, "DROP %s %s.", reltypename,
+ appendPQExpBuffer(delq, "DROP %s IF EXISTS %s.", reltypename,
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s;\n",
fmtId(tbinfo->dobj.name));
@@ -12873,7 +12873,7 @@ dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delq, "ALTER TABLE %s.",
+ appendPQExpBuffer(delq, "ALTER TABLE IF EXISTS %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s ",
fmtId(tbinfo->dobj.name));
@@ -12977,7 +12977,7 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
* DROP must be fully qualified in case same name appears in
* pg_catalog
*/
- appendPQExpBuffer(delq, "DROP INDEX %s.",
+ appendPQExpBuffer(delq, "DROP INDEX IF EXISTS %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s;\n",
fmtId(indxinfo->dobj.name));
@@ -13096,7 +13096,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
* DROP must be fully qualified in case same name appears in
* pg_catalog
*/
- appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
+ appendPQExpBuffer(delq, "ALTER TABLE IF EXISTS ONLY %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s ",
fmtId(tbinfo->dobj.name));
@@ -13129,7 +13129,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
* DROP must be fully qualified in case same name appears in
* pg_catalog
*/
- appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
+ appendPQExpBuffer(delq, "ALTER TABLE IF EXISTS ONLY %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s ",
fmtId(tbinfo->dobj.name));
@@ -13164,7 +13164,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
* DROP must be fully qualified in case same name appears in
* pg_catalog
*/
- appendPQExpBuffer(delq, "ALTER TABLE %s.",
+ appendPQExpBuffer(delq, "ALTER TABLE IF EXISTS %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delq, "%s ",
fmtId(tbinfo->dobj.name));
@@ -13411,7 +13411,7 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
* DROP must be fully qualified in case same name appears in
* pg_catalog
*/
- appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",
+ appendPQExpBuffer(delqry, "DROP SEQUENCE IF EXISTS %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
appendPQExpBuffer(delqry, "%s;\n",
fmtId(tbinfo->dobj.name));
@@ -13574,7 +13574,7 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
+ appendPQExpBuffer(delqry, "DROP TRIGGER IF EXISTS %s ",
fmtId(tginfo->dobj.name));
appendPQExpBuffer(delqry, "ON %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
@@ -13914,7 +13914,7 @@ dumpRule(Archive *fout, RuleInfo *rinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delcmd, "DROP RULE %s ",
+ appendPQExpBuffer(delcmd, "DROP RULE IF EXISTS %s ",
fmtId(rinfo->dobj.name));
appendPQExpBuffer(delcmd, "ON %s.",
fmtId(tbinfo->dobj.namespace->dobj.name));
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 10ce222..f38d9ea 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -615,7 +615,7 @@ dropRoles(PGconn *conn)
rolename = PQgetvalue(res, i, i_rolname);
- fprintf(OPF, "DROP ROLE %s;\n", fmtId(rolename));
+ fprintf(OPF, "DROP ROLE IF EXISTS %s;\n", fmtId(rolename));
}
PQclear(res);
@@ -976,7 +976,7 @@ dropTablespaces(PGconn *conn)
{
char *spcname = PQgetvalue(res, i, 0);
- fprintf(OPF, "DROP TABLESPACE %s;\n", fmtId(spcname));
+ fprintf(OPF, "DROP TABLESPACE IF EXISTS %s;\n", fmtId(spcname));
}
PQclear(res);
@@ -1130,7 +1130,7 @@ dropDBs(PGconn *conn)
if (strcmp(dbname, "template1") != 0 &&
strcmp(dbname, "postgres") != 0)
{
- fprintf(OPF, "DROP DATABASE %s;\n", fmtId(dbname));
+ fprintf(OPF, "DROP DATABASE IF EXISTS %s;\n", fmtId(dbname));
}
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
