Skip to content

Commit 9067310

Browse files
alvherrealvherre
committed
pg_dump et al: Add --if-exists option
This option makes pg_dump, pg_dumpall and pg_restore inject an IF EXISTS clause to each DROP command they emit. (In pg_dumpall, the clause is not added to individual objects drops, but rather to the CREATE DATABASE commands, as well as CREATE ROLE and CREATE TABLESPACE.) This allows for a better user dump experience when using --clean in case some objects do not already exist. Per bug #7873 by Dave Rolsky. Author: Pavel Stěhule Reviewed-by: Jeevan Chalke, Álvaro Herrera, Josh Kupershmidt
1 parent 34c6d96 commit 9067310

File tree

8 files changed

+146
-7
lines changed

8 files changed

+146
-7
lines changed

‎doc/src/sgml/ref/pg_dump.sgml

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -145,7 +145,8 @@ PostgreSQL documentation
145145
<para>
146146
Output commands to clean (drop)
147147
database objects prior to outputting the commands for creating them.
148-
(Restore might generate some harmless error messages, if any objects
148+
(Unless <option>--if-exists</> is also specified,
149+
restore might generate some harmless error messages, if any objects
149150
were not present in the destination database.)
150151
</para>
151152

@@ -649,6 +650,17 @@ PostgreSQL documentation
649650
</listitem>
650651
</varlistentry>
651652

653+
<varlistentry>
654+
<term><option>--if-exists</option></term>
655+
<listitem>
656+
<para>
657+
Use conditional commands (i.e. add an <literal>IF EXISTS</literal>
658+
clause) when cleaning database objects. This option is not valid
659+
unless <option>--clean</> is also specified.
660+
</para>
661+
</listitem>
662+
</varlistentry>
663+
652664
<varlistentry>
653665
<term><option>--disable-dollar-quoting</></term>
654666
<listitem>

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -300,6 +300,17 @@ PostgreSQL documentation
300300
</listitem>
301301
</varlistentry>
302302

303+
<varlistentry>
304+
<term><option>--if-exists</option></term>
305+
<listitem>
306+
<para>
307+
Use conditional commands (i.e. add an <literal>IF EXISTS</literal>
308+
clause) to clean databases and other objects. This option is not valid
309+
unless <option>--clean</> is also specified.
310+
</para>
311+
</listitem>
312+
</varlistentry>
313+
303314
<varlistentry>
304315
<term><option>--inserts</option></term>
305316
<listitem>

‎doc/src/sgml/ref/pg_restore.sgml

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -109,7 +109,8 @@
109109
<listitem>
110110
<para>
111111
Clean (drop) database objects before recreating them.
112-
(This might generate some harmless error messages, if any objects
112+
(Unless <option>--if-exists</> is used,
113+
this might generate some harmless error messages, if any objects
113114
were not present in the destination database.)
114115
</para>
115116
</listitem>
@@ -489,6 +490,17 @@
489490
</listitem>
490491
</varlistentry>
491492

493+
<varlistentry>
494+
<term><option>--if-exists</option></term>
495+
<listitem>
496+
<para>
497+
Use conditional commands (i.e. add an <literal>IF EXISTS</literal>
498+
clause) when cleaning database objects. This option is not valid
499+
unless <option>--clean</> is also specified.
500+
</para>
501+
</listitem>
502+
</varlistentry>
503+
492504
<varlistentry>
493505
<term><option>--no-data-for-failed-tables</option></term>
494506
<listitem>

‎src/bin/pg_dump/pg_backup.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,7 @@ typedef struct _restoreOptions
113113
char *superuser; /* Username to use as superuser */
114114
char *use_role; /* Issue SET ROLE to this */
115115
int dropSchema;
116+
int if_exists;
116117
const char *filename;
117118
int dataOnly;
118119
int schemaOnly;

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 71 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -413,8 +413,77 @@ RestoreArchive(Archive *AHX)
413413
/* Select owner and schema as necessary */
414414
_becomeOwner(AH, te);
415415
_selectOutputSchema(AH, te->namespace);
416-
/* Drop it */
417-
ahprintf(AH, "%s", te->dropStmt);
416+
417+
/*
418+
* Now emit the DROP command, if the object has one. Note we
419+
* don't necessarily emit it verbatim; at this point we add an
420+
* appropriate IF EXISTS clause, if the user requested it.
421+
*/
422+
if (*te->dropStmt != '\0')
423+
{
424+
if (!ropt->if_exists)
425+
{
426+
/* No --if-exists? Then just use the original */
427+
ahprintf(AH, "%s", te->dropStmt);
428+
}
429+
else
430+
{
431+
char buffer[40];
432+
char *mark;
433+
char *dropStmt = pg_strdup(te->dropStmt);
434+
char *dropStmtPtr = dropStmt;
435+
PQExpBuffer ftStmt = createPQExpBuffer();
436+
437+
/*
438+
* Need to inject IF EXISTS clause after ALTER TABLE
439+
* part in ALTER TABLE .. DROP statement
440+
*/
441+
if (strncmp(dropStmt, "ALTER TABLE", 11) == 0)
442+
{
443+
appendPQExpBuffer(ftStmt,
444+
"ALTER TABLE IF EXISTS");
445+
dropStmt = dropStmt + 11;
446+
}
447+
448+
/*
449+
* ALTER TABLE..ALTER COLUMN..DROP DEFAULT does not
450+
* support the IF EXISTS clause, and therefore we
451+
* simply emit the original command for such objects.
452+
* For other objects, we need to extract the first part
453+
* of the DROP which includes the object type. Most of
454+
* the time this matches te->desc, so search for that;
455+
* however for the different kinds of CONSTRAINTs, we
456+
* know to search for hardcoded "DROP CONSTRAINT"
457+
* instead.
458+
*/
459+
if (strcmp(te->desc, "DEFAULT") == 0)
460+
appendPQExpBuffer(ftStmt, "%s", dropStmt);
461+
else
462+
{
463+
if (strcmp(te->desc, "CONSTRAINT") == 0 ||
464+
strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
465+
strcmp(te->desc, "FK CONSTRAINT") == 0)
466+
strcpy(buffer, "DROP CONSTRAINT");
467+
else
468+
snprintf(buffer, sizeof(buffer), "DROP %s",
469+
te->desc);
470+
471+
mark = strstr(dropStmt, buffer);
472+
Assert(mark != NULL);
473+
474+
*mark = '\0';
475+
appendPQExpBuffer(ftStmt, "%s%s IF EXISTS%s",
476+
dropStmt, buffer,
477+
mark + strlen(buffer));
478+
}
479+
480+
ahprintf(AH, "%s", ftStmt->data);
481+
482+
destroyPQExpBuffer(ftStmt);
483+
484+
pg_free(dropStmtPtr);
485+
}
486+
}
418487
}
419488
}
420489

‎src/bin/pg_dump/pg_dump.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,7 @@ static int binary_upgrade = 0;
132132
static int disable_dollar_quoting = 0;
133133
static int dump_inserts = 0;
134134
static int column_inserts = 0;
135+
static int if_exists = 0;
135136
static int no_security_labels = 0;
136137
static int no_synchronized_snapshots = 0;
137138
static int no_unlogged_table_data = 0;
@@ -345,6 +346,7 @@ main(int argc, char **argv)
345346
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
346347
{"disable-triggers", no_argument, &disable_triggers, 1},
347348
{"exclude-table-data", required_argument, NULL, 4},
349+
{"if-exists", no_argument, &if_exists, 1},
348350
{"inserts", no_argument, &dump_inserts, 1},
349351
{"lock-wait-timeout", required_argument, NULL, 2},
350352
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
@@ -573,6 +575,9 @@ main(int argc, char **argv)
573575
exit_nicely(1);
574576
}
575577

578+
if (if_exists && !outputClean)
579+
exit_horribly(NULL, "option --if-exists requires -c/--clean option\n");
580+
576581
/* Identify archive format to emit */
577582
archiveFormat = parseArchiveFormat(format, &archiveMode);
578583

@@ -805,6 +810,7 @@ main(int argc, char **argv)
805810
ropt->dropSchema = outputClean;
806811
ropt->dataOnly = dataOnly;
807812
ropt->schemaOnly = schemaOnly;
813+
ropt->if_exists = if_exists;
808814
ropt->dumpSections = dumpSections;
809815
ropt->aclsSkip = aclsSkip;
810816
ropt->superuser = outputSuperuser;
@@ -886,6 +892,7 @@ help(const char *progname)
886892
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
887893
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
888894
printf(_(" --exclude-table-data=TABLE do NOT dump data for the named table(s)\n"));
895+
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
889896
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
890897
printf(_(" --no-security-labels do not dump security label assignments\n"));
891898
printf(_(" --no-synchronized-snapshots do not use synchronized snapshots in parallel jobs\n"));

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -73,6 +73,7 @@ static int binary_upgrade = 0;
7373
static int column_inserts = 0;
7474
static int disable_dollar_quoting = 0;
7575
static int disable_triggers = 0;
76+
static int if_exists = 0;
7677
static int inserts = 0;
7778
static int no_tablespaces = 0;
7879
static int use_setsessauth = 0;
@@ -119,6 +120,7 @@ main(int argc, char *argv[])
119120
{"column-inserts", no_argument, &column_inserts, 1},
120121
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
121122
{"disable-triggers", no_argument, &disable_triggers, 1},
123+
{"if-exists", no_argument, &if_exists, 1},
122124
{"inserts", no_argument, &inserts, 1},
123125
{"lock-wait-timeout", required_argument, NULL, 2},
124126
{"no-tablespaces", no_argument, &no_tablespaces, 1},
@@ -334,6 +336,13 @@ main(int argc, char *argv[])
334336
exit_nicely(1);
335337
}
336338

339+
if (if_exists && !output_clean)
340+
{
341+
fprintf(stderr, _("%s: option --if-exists requires -c/--clean option\n"),
342+
progname);
343+
exit_nicely(1);
344+
}
345+
337346
if (roles_only && tablespaces_only)
338347
{
339348
fprintf(stderr, _("%s: options -r/--roles-only and -t/--tablespaces-only cannot be used together\n"),
@@ -564,6 +573,7 @@ help(void)
564573
printf(_(" --column-inserts dump data as INSERT commands with column names\n"));
565574
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
566575
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
576+
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
567577
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
568578
printf(_(" --no-security-labels do not dump security label assignments\n"));
569579
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
@@ -624,7 +634,9 @@ dropRoles(PGconn *conn)
624634

625635
rolename = PQgetvalue(res, i, i_rolname);
626636

627-
fprintf(OPF, "DROP ROLE %s;\n", fmtId(rolename));
637+
fprintf(OPF, "DROP ROLE %s%s;\n",
638+
if_exists ? "IF EXISTS " : "",
639+
fmtId(rolename));
628640
}
629641

630642
PQclear(res);
@@ -994,7 +1006,9 @@ dropTablespaces(PGconn *conn)
9941006
{
9951007
char *spcname = PQgetvalue(res, i, 0);
9961008

997-
fprintf(OPF, "DROP TABLESPACE %s;\n", fmtId(spcname));
1009+
fprintf(OPF, "DROP TABLESPACE %s%s;\n",
1010+
if_exists ? "IF EXISTS " : "",
1011+
fmtId(spcname));
9981012
}
9991013

10001014
PQclear(res);
@@ -1148,7 +1162,9 @@ dropDBs(PGconn *conn)
11481162
if (strcmp(dbname, "template1") != 0 &&
11491163
strcmp(dbname, "postgres") != 0)
11501164
{
1151-
fprintf(OPF, "DROP DATABASE %s;\n", fmtId(dbname));
1165+
fprintf(OPF, "DROP DATABASE %s%s;\n",
1166+
if_exists ? "IF EXISTS " : "",
1167+
fmtId(dbname));
11521168
}
11531169
}
11541170

‎src/bin/pg_dump/pg_restore.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,7 @@ main(int argc, char **argv)
7070
Archive *AH;
7171
char *inputFileSpec;
7272
static int disable_triggers = 0;
73+
static int if_exists = 0;
7374
static int no_data_for_failed_tables = 0;
7475
static int outputNoTablespaces = 0;
7576
static int use_setsessauth = 0;
@@ -110,6 +111,7 @@ main(int argc, char **argv)
110111
* the following options don't have an equivalent short option letter
111112
*/
112113
{"disable-triggers", no_argument, &disable_triggers, 1},
114+
{"if-exists", no_argument, &if_exists, 1},
113115
{"no-data-for-failed-tables", no_argument, &no_data_for_failed_tables, 1},
114116
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
115117
{"role", required_argument, NULL, 2},
@@ -336,6 +338,14 @@ main(int argc, char **argv)
336338
opts->use_setsessauth = use_setsessauth;
337339
opts->no_security_labels = no_security_labels;
338340

341+
if (if_exists && !opts->dropSchema)
342+
{
343+
fprintf(stderr, _("%s: option --if-exists requires -c/--clean option\n"),
344+
progname);
345+
exit_nicely(1);
346+
}
347+
opts->if_exists = if_exists;
348+
339349
if (opts->formatName)
340350
{
341351
switch (opts->formatName[0])
@@ -450,6 +460,7 @@ usage(const char *progname)
450460
printf(_(" -x, --no-privileges skip restoration of access privileges (grant/revoke)\n"));
451461
printf(_(" -1, --single-transaction restore as a single transaction\n"));
452462
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
463+
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
453464
printf(_(" --no-data-for-failed-tables do not restore data of tables that could not be\n"
454465
" created\n"));
455466
printf(_(" --no-security-labels do not restore security labels\n"));

0 commit comments

Comments
 (0)