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

Commit9067310

Browse files
committed
pg_dump et al: Add --if-exists option
This option makes pg_dump, pg_dumpall and pg_restore inject an IF EXISTSclause to each DROP command they emit. (In pg_dumpall, the clause isnot added to individual objects drops, but rather to the CREATE DATABASEcommands, as well as CREATE ROLE and CREATE TABLESPACE.)This allows for a better user dump experience when using --clean in casesome objects do not already exist. Per bug #7873 by Dave Rolsky.Author: Pavel StěhuleReviewed-by: Jeevan Chalke, Álvaro Herrera, Josh Kupershmidt
1 parent34c6d96 commit9067310

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
intdropSchema;
116+
intif_exists;
116117
constchar*filename;
117118
intdataOnly;
118119
intschemaOnly;

‎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+
charbuffer[40];
432+
char*mark;
433+
char*dropStmt=pg_strdup(te->dropStmt);
434+
char*dropStmtPtr=dropStmt;
435+
PQExpBufferftStmt=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 intbinary_upgrade = 0;
132132
staticintdisable_dollar_quoting=0;
133133
staticintdump_inserts=0;
134134
staticintcolumn_inserts=0;
135+
staticintif_exists=0;
135136
staticintno_security_labels=0;
136137
staticintno_synchronized_snapshots=0;
137138
staticintno_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 intbinary_upgrade = 0;
7373
staticintcolumn_inserts=0;
7474
staticintdisable_dollar_quoting=0;
7575
staticintdisable_triggers=0;
76+
staticintif_exists=0;
7677
staticintinserts=0;
7778
staticintno_tablespaces=0;
7879
staticintuse_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
staticintdisable_triggers=0;
73+
staticintif_exists=0;
7374
staticintno_data_for_failed_tables=0;
7475
staticintoutputNoTablespaces=0;
7576
staticintuse_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)
[8]ページ先頭
©2009-2025 Movatter.jp