Add option --drop-cascade for pg_dump/restore

From 1888aa9e12da21db39b6ce0c72be665cd17b0bca Mon Sep 17 00:00:00 2001
From: Wu Haotian <whtsky@gmail.com>
Date: Thu, 1 Apr 2021 17:14:29 +0800
Subject: [PATCH] pg_dump/restore: add --drop-cascade option

This option makes pg_dump and pg_store inject a CASCADE clause
to each DROP command.

This allows for restoring an old backup after adding new constraints,
at the risk of losing new data.
---
 doc/src/sgml/ref/pg_dump.sgml        |  10 ++
 doc/src/sgml/ref/pg_dumpall.sgml     |  10 ++
 doc/src/sgml/ref/pg_restore.sgml     |  10 ++
 src/bin/pg_dump/pg_backup.h          |   2 +
 src/bin/pg_dump/pg_backup_archiver.c | 153 ++++++++++++++-------------
 src/bin/pg_dump/pg_dump.c            |   6 ++
 src/bin/pg_dump/pg_restore.c         |  10 ++
 7 files changed, 127 insertions(+), 74 deletions(-)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 529b167c96..b486c26e39 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -732,6 +732,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 3c2f9c774f..fc5742b461 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -289,6 +289,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--exclude-database=<replaceable class="parameter">pattern</replaceable></option></term>
       <listitem>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 93ea937ac8..d52067a149 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -553,6 +553,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 3bc86635f7..f06ea051a3 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -83,6 +83,7 @@ typedef struct _restoreOptions
 									 * instead of OWNER TO */
 	char	   *superuser;		/* Username to use as superuser */
 	char	   *use_role;		/* Issue SET ROLE to this */
+	int			drop_cascade;
 	int			dropSchema;
 	int			disable_dollar_quoting;
 	int			dump_inserts;	/* 0 = COPY, otherwise rows per INSERT */
@@ -152,6 +153,7 @@ typedef struct _dumpOptions
 
 	/* flags for various command-line long options */
 	int			disable_dollar_quoting;
+	int			drop_cascade;
 	int			column_inserts;
 	int			if_exists;
 	int			no_comments;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 86de26a4bf..8ce6864b70 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -187,6 +187,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt)
 	dopt->cparams.promptPassword = ropt->cparams.promptPassword;
 	dopt->outputClean = ropt->dropSchema;
 	dopt->dataOnly = ropt->dataOnly;
+	dopt->drop_cascade = ropt->drop_cascade;
 	dopt->schemaOnly = ropt->schemaOnly;
 	dopt->if_exists = ropt->if_exists;
 	dopt->column_inserts = ropt->column_inserts;
@@ -528,100 +529,104 @@ RestoreArchive(Archive *AHX)
 				 */
 				if (*te->dropStmt != '\0')
 				{
-					if (!ropt->if_exists)
+					if (!ropt->if_exists && !ropt->drop_cascade)
 					{
-						/* No --if-exists?	Then just use the original */
+						/* Just use the original */
 						ahprintf(AH, "%s", te->dropStmt);
 					}
+					/*
+					* Inject an appropriate spelling of "if exists" or "cascade".
+					* For large objects, we have a separate routine that
+					* knows how to do it, without depending on
+					* te->dropStmt; use that.  For other objects we need
+					* to parse the command.
+					*/
+					else if (strncmp(te->desc, "BLOB", 4) == 0)
+					{
+						if (ropt->if_exists)
+							DropBlobIfExists(AH, te->catalogId.oid);
+						else
+							ahprintf(AH, "%s", te->dropStmt);
+					}
 					else
 					{
+						char	   *dropStmt = pg_strdup(te->dropStmt);
+						char	   *dropStmtOrig = dropStmt;
+						PQExpBuffer ftStmt = createPQExpBuffer();
 						/*
-						 * Inject an appropriate spelling of "if exists".  For
-						 * large objects, we have a separate routine that
-						 * knows how to do it, without depending on
-						 * te->dropStmt; use that.  For other objects we need
-						 * to parse the command.
-						 */
-						if (strncmp(te->desc, "BLOB", 4) == 0)
+						* Need to inject IF EXISTS clause after ALTER
+						* TABLE part in ALTER TABLE .. DROP statement
+						*/
+						if (ropt->if_exists && strncmp(dropStmt, "ALTER TABLE", 11) == 0)
 						{
-							DropBlobIfExists(AH, te->catalogId.oid);
+							appendPQExpBufferStr(ftStmt,
+													"ALTER TABLE IF EXISTS");
+							dropStmt = dropStmt + 11;
 						}
+
+						/*
+						* ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
+						* not support the IF EXISTS or CASCADE clause, and therefore
+						* we simply emit the original command for DEFAULT
+						* objects (modulo the adjustment made above).
+						*
+						* Likewise, don't mess with DATABASE PROPERTIES.
+						*
+						* If we used CREATE OR REPLACE VIEW as a means of
+						* quasi-dropping an ON SELECT rule, that should
+						* be emitted unchanged as well.
+						*
+						* For other object types, we need to extract the
+						* first part of the DROP which includes the
+						* object type.  Most of the time this matches
+						* te->desc, so search for that; however for the
+						* different kinds of CONSTRAINTs, we know to
+						* search for hardcoded "DROP CONSTRAINT" instead.
+						*/
+						if (strcmp(te->desc, "DEFAULT") == 0 ||
+							strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
+							strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
+							appendPQExpBufferStr(ftStmt, dropStmt);
 						else
 						{
-							char	   *dropStmt = pg_strdup(te->dropStmt);
-							char	   *dropStmtOrig = dropStmt;
-							PQExpBuffer ftStmt = createPQExpBuffer();
-
-							/*
-							 * Need to inject IF EXISTS clause after ALTER
-							 * TABLE part in ALTER TABLE .. DROP statement
-							 */
-							if (strncmp(dropStmt, "ALTER TABLE", 11) == 0)
+							char		buffer[40];
+							char	   *mark;
+
+							if (strcmp(te->desc, "CONSTRAINT") == 0 ||
+								strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
+								strcmp(te->desc, "FK CONSTRAINT") == 0)
+								strcpy(buffer, "DROP CONSTRAINT");
+							else
+								snprintf(buffer, sizeof(buffer), "DROP %s",
+											te->desc);
+
+							mark = strstr(dropStmt, buffer);
+
+							if (mark)
 							{
-								appendPQExpBufferStr(ftStmt,
-													 "ALTER TABLE IF EXISTS");
-								dropStmt = dropStmt + 11;
+								char *stmtEnd;
+								*mark = '\0';
+								stmtEnd = strstr(mark + strlen(buffer), ";");
+								*stmtEnd = '\0';
+								appendPQExpBuffer(ftStmt, "%s%s %s%s%s\n",
+													dropStmt, buffer,
+													ropt->if_exists ? "IF EXISTS" : "",
+													mark + strlen(buffer),
+													ropt->drop_cascade ? " CASCADE;" : ";");
 							}
-
-							/*
-							 * ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
-							 * not support the IF EXISTS clause, and therefore
-							 * we simply emit the original command for DEFAULT
-							 * objects (modulo the adjustment made above).
-							 *
-							 * Likewise, don't mess with DATABASE PROPERTIES.
-							 *
-							 * If we used CREATE OR REPLACE VIEW as a means of
-							 * quasi-dropping an ON SELECT rule, that should
-							 * be emitted unchanged as well.
-							 *
-							 * For other object types, we need to extract the
-							 * first part of the DROP which includes the
-							 * object type.  Most of the time this matches
-							 * te->desc, so search for that; however for the
-							 * different kinds of CONSTRAINTs, we know to
-							 * search for hardcoded "DROP CONSTRAINT" instead.
-							 */
-							if (strcmp(te->desc, "DEFAULT") == 0 ||
-								strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
-								strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
-								appendPQExpBufferStr(ftStmt, dropStmt);
 							else
 							{
-								char		buffer[40];
-								char	   *mark;
-
-								if (strcmp(te->desc, "CONSTRAINT") == 0 ||
-									strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
-									strcmp(te->desc, "FK CONSTRAINT") == 0)
-									strcpy(buffer, "DROP CONSTRAINT");
-								else
-									snprintf(buffer, sizeof(buffer), "DROP %s",
-											 te->desc);
-
-								mark = strstr(dropStmt, buffer);
-
-								if (mark)
-								{
-									*mark = '\0';
-									appendPQExpBuffer(ftStmt, "%s%s IF EXISTS%s",
-													  dropStmt, buffer,
-													  mark + strlen(buffer));
-								}
-								else
-								{
-									/* complain and emit unmodified command */
-									pg_log_warning("could not find where to insert IF EXISTS in statement \"%s\"",
-												   dropStmtOrig);
-									appendPQExpBufferStr(ftStmt, dropStmt);
-								}
+								/* complain and emit unmodified command */
+								pg_log_warning("could not find where to insert IF EXISTS or CASCADE in statement \"%s\"",
+												dropStmtOrig);
+								appendPQExpBufferStr(ftStmt, dropStmt);
 							}
-
 							ahprintf(AH, "%s", ftStmt->data);
 
 							destroyPQExpBuffer(ftStmt);
 							pg_free(dropStmtOrig);
 						}
+
 					}
 				}
 			}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d0ea489614..896f6e7f1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -375,6 +375,7 @@ main(int argc, char **argv)
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
+		{"drop-cascade", no_argument, &dopt.drop_cascade, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
 		{"extra-float-digits", required_argument, NULL, 8},
@@ -677,6 +678,9 @@ main(int argc, char **argv)
 		exit_nicely(1);
 	}
 
+	if (dopt.drop_cascade && !dopt.outputClean)
+		fatal("option --drop-cascade requires option -c/--clean");
+
 	if (dopt.if_exists && !dopt.outputClean)
 		fatal("option --if-exists requires option -c/--clean");
 
@@ -962,6 +966,7 @@ main(int argc, char **argv)
 	ropt->cparams.pghost = dopt.cparams.pghost ? pg_strdup(dopt.cparams.pghost) : NULL;
 	ropt->cparams.username = dopt.cparams.username ? pg_strdup(dopt.cparams.username) : NULL;
 	ropt->cparams.promptPassword = dopt.cparams.promptPassword;
+	ropt->drop_cascade = dopt.drop_cascade;
 	ropt->dropSchema = dopt.outputClean;
 	ropt->dataOnly = dopt.dataOnly;
 	ropt->schemaOnly = dopt.schemaOnly;
@@ -1063,6 +1068,7 @@ help(const char *progname)
 	printf(_("  --column-inserts             dump data as INSERT commands with column names\n"));
 	printf(_("  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security (dump only content user has\n"
 			 "                               access to)\n"));
 	printf(_("  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n"));
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 589b4aed53..d4636d5664 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -62,6 +62,7 @@ main(int argc, char **argv)
 	Archive    *AH;
 	char	   *inputFileSpec;
 	static int	disable_triggers = 0;
+	static int	drop_cascade = 0;
 	static int	enable_row_security = 0;
 	static int	if_exists = 0;
 	static int	no_data_for_failed_tables = 0;
@@ -108,6 +109,7 @@ main(int argc, char **argv)
 		 * the following options don't have an equivalent short option letter
 		 */
 		{"disable-triggers", no_argument, &disable_triggers, 1},
+		{"drop-cascade", no_argument, &drop_cascade, 1},
 		{"enable-row-security", no_argument, &enable_row_security, 1},
 		{"if-exists", no_argument, &if_exists, 1},
 		{"no-data-for-failed-tables", no_argument, &no_data_for_failed_tables, 1},
@@ -368,6 +370,13 @@ main(int argc, char **argv)
 	}
 
 	opts->disable_triggers = disable_triggers;
+
+	if (drop_cascade && !opts->dropSchema)
+	{
+		pg_log_error("option --drop-cascade requires option -c/--clean");
+		exit_nicely(1);
+	}
+	opts->drop_cascade = drop_cascade;
 	opts->enable_row_security = enable_row_security;
 	opts->noDataForFailedTables = no_data_for_failed_tables;
 	opts->noTablespace = outputNoTablespaces;
@@ -491,6 +500,7 @@ usage(const char *progname)
 	printf(_("  -x, --no-privileges          skip restoration of access privileges (grant/revoke)\n"));
 	printf(_("  -1, --single-transaction     restore as a single transaction\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security\n"));
 	printf(_("  --if-exists                  use IF EXISTS when dropping objects\n"));
 	printf(_("  --no-comments                do not restore comments\n"));
-- 
2.31.1

From a7b90d36b64021315c09a15d17f8cf26a6e72fe7 Mon Sep 17 00:00:00 2001
From: Wu Haotian <whtsky@gmail.com>
Date: Thu, 1 Apr 2021 17:14:29 +0800
Subject: [PATCH] pg_dump/restore: add --drop-cascade option

This option makes pg_dump and pg_store inject a CASCADE clause
to each DROP command.

This allows for restoring an old backup after adding new constraints,
at the risk of losing new data.
---
 doc/src/sgml/ref/pg_dump.sgml        |  10 ++
 doc/src/sgml/ref/pg_restore.sgml     |  10 ++
 src/bin/pg_dump/pg_backup.h          |   2 +
 src/bin/pg_dump/pg_backup_archiver.c | 157 ++++++++++++++-------------
 src/bin/pg_dump/pg_dump.c            |   6 +
 src/bin/pg_dump/pg_restore.c         |  10 ++
 src/bin/pg_dump/t/001_basic.pl       |   7 +-
 src/bin/pg_dump/t/002_pg_dump.pl     | 147 +++++++++++++++++--------
 8 files changed, 228 insertions(+), 121 deletions(-)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 529b167c96..b486c26e39 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -732,6 +732,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 93ea937ac8..d52067a149 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -553,6 +553,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 3bc86635f7..f06ea051a3 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -83,6 +83,7 @@ typedef struct _restoreOptions
 									 * instead of OWNER TO */
 	char	   *superuser;		/* Username to use as superuser */
 	char	   *use_role;		/* Issue SET ROLE to this */
+	int			drop_cascade;
 	int			dropSchema;
 	int			disable_dollar_quoting;
 	int			dump_inserts;	/* 0 = COPY, otherwise rows per INSERT */
@@ -152,6 +153,7 @@ typedef struct _dumpOptions
 
 	/* flags for various command-line long options */
 	int			disable_dollar_quoting;
+	int			drop_cascade;
 	int			column_inserts;
 	int			if_exists;
 	int			no_comments;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 86de26a4bf..68c38eae49 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -187,6 +187,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt)
 	dopt->cparams.promptPassword = ropt->cparams.promptPassword;
 	dopt->outputClean = ropt->dropSchema;
 	dopt->dataOnly = ropt->dataOnly;
+	dopt->drop_cascade = ropt->drop_cascade;
 	dopt->schemaOnly = ropt->schemaOnly;
 	dopt->if_exists = ropt->if_exists;
 	dopt->column_inserts = ropt->column_inserts;
@@ -528,95 +529,103 @@ RestoreArchive(Archive *AHX)
 				 */
 				if (*te->dropStmt != '\0')
 				{
-					if (!ropt->if_exists)
+					if (!ropt->if_exists && !ropt->drop_cascade)
 					{
-						/* No --if-exists?	Then just use the original */
+						/* Just use the original */
 						ahprintf(AH, "%s", te->dropStmt);
 					}
+					/*
+					* Inject an appropriate spelling of "if exists" or "cascade".
+					* For large objects, we have a separate routine that
+					* knows how to do it, without depending on
+					* te->dropStmt; use that.  For other objects we need
+					* to parse the command.
+					*/
+					else if (strncmp(te->desc, "BLOB", 4) == 0)
+					{
+						if (ropt->if_exists)
+							DropBlobIfExists(AH, te->catalogId.oid);
+						else
+							ahprintf(AH, "%s", te->dropStmt);
+					}
 					else
 					{
+						char	   *dropStmt = pg_strdup(te->dropStmt);
+						char	   *dropStmtOrig = dropStmt;
+						PQExpBuffer ftStmt = createPQExpBuffer();
 						/*
-						 * Inject an appropriate spelling of "if exists".  For
-						 * large objects, we have a separate routine that
-						 * knows how to do it, without depending on
-						 * te->dropStmt; use that.  For other objects we need
-						 * to parse the command.
-						 */
-						if (strncmp(te->desc, "BLOB", 4) == 0)
+						* Need to inject IF EXISTS clause after ALTER
+						* TABLE part in ALTER TABLE .. DROP statement
+						*/
+						if (ropt->if_exists && strncmp(dropStmt, "ALTER TABLE", 11) == 0)
 						{
-							DropBlobIfExists(AH, te->catalogId.oid);
+							appendPQExpBufferStr(ftStmt,
+													"ALTER TABLE IF EXISTS");
+							dropStmt = dropStmt + 11;
 						}
+
+						/*
+						* ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
+						* not support the IF EXISTS or CASCADE clause, and therefore
+						* we simply emit the original command for DEFAULT
+						* objects (modulo the adjustment made above).
+						*
+						* Likewise, don't mess with DATABASE PROPERTIES.
+						*
+						* If we used CREATE OR REPLACE VIEW as a means of
+						* quasi-dropping an ON SELECT rule, that should
+						* be emitted unchanged as well.
+						*
+						* For other object types, we need to extract the
+						* first part of the DROP which includes the
+						* object type.  Most of the time this matches
+						* te->desc, so search for that; however for the
+						* different kinds of CONSTRAINTs, we know to
+						* search for hardcoded "DROP CONSTRAINT" instead.
+						*/
+						if (strcmp(te->desc, "DEFAULT") == 0 ||
+							strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
+							strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
+							appendPQExpBufferStr(ftStmt, dropStmt);
 						else
 						{
-							char	   *dropStmt = pg_strdup(te->dropStmt);
-							char	   *dropStmtOrig = dropStmt;
-							PQExpBuffer ftStmt = createPQExpBuffer();
-
-							/*
-							 * Need to inject IF EXISTS clause after ALTER
-							 * TABLE part in ALTER TABLE .. DROP statement
-							 */
-							if (strncmp(dropStmt, "ALTER TABLE", 11) == 0)
+							char		buffer[40];
+							char	   *mark;
+
+							if (strcmp(te->desc, "CONSTRAINT") == 0 ||
+								strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
+								strcmp(te->desc, "FK CONSTRAINT") == 0)
+								strcpy(buffer, "DROP CONSTRAINT");
+							else
+								snprintf(buffer, sizeof(buffer), "DROP %s",
+											te->desc);
+
+							mark = strstr(dropStmt, buffer);
+
+							if (mark)
 							{
-								appendPQExpBufferStr(ftStmt,
-													 "ALTER TABLE IF EXISTS");
-								dropStmt = dropStmt + 11;
+								char *name = mark + strlen(buffer);
+								*mark = '\0';
+								/*
+								 * pg_dump always add CASCADE for TYPE with circular dependencies.
+								 * avoid adding duplicated CASCADE.
+								 */
+								bool shouldCascade = ropt->drop_cascade && strstr(name, "CASCADE;") == NULL;
+								char *stmtEnd = strstr(name, ";");
+								*stmtEnd = '\0';
+								appendPQExpBuffer(ftStmt, "%s%s%s%s%s\n",
+													dropStmt, buffer,
+													ropt->if_exists ? " IF EXISTS" : "",
+													name,
+													shouldCascade ? " CASCADE;" : ";");
 							}
-
-							/*
-							 * ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
-							 * not support the IF EXISTS clause, and therefore
-							 * we simply emit the original command for DEFAULT
-							 * objects (modulo the adjustment made above).
-							 *
-							 * Likewise, don't mess with DATABASE PROPERTIES.
-							 *
-							 * If we used CREATE OR REPLACE VIEW as a means of
-							 * quasi-dropping an ON SELECT rule, that should
-							 * be emitted unchanged as well.
-							 *
-							 * For other object types, we need to extract the
-							 * first part of the DROP which includes the
-							 * object type.  Most of the time this matches
-							 * te->desc, so search for that; however for the
-							 * different kinds of CONSTRAINTs, we know to
-							 * search for hardcoded "DROP CONSTRAINT" instead.
-							 */
-							if (strcmp(te->desc, "DEFAULT") == 0 ||
-								strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
-								strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
-								appendPQExpBufferStr(ftStmt, dropStmt);
 							else
 							{
-								char		buffer[40];
-								char	   *mark;
-
-								if (strcmp(te->desc, "CONSTRAINT") == 0 ||
-									strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
-									strcmp(te->desc, "FK CONSTRAINT") == 0)
-									strcpy(buffer, "DROP CONSTRAINT");
-								else
-									snprintf(buffer, sizeof(buffer), "DROP %s",
-											 te->desc);
-
-								mark = strstr(dropStmt, buffer);
-
-								if (mark)
-								{
-									*mark = '\0';
-									appendPQExpBuffer(ftStmt, "%s%s IF EXISTS%s",
-													  dropStmt, buffer,
-													  mark + strlen(buffer));
-								}
-								else
-								{
-									/* complain and emit unmodified command */
-									pg_log_warning("could not find where to insert IF EXISTS in statement \"%s\"",
-												   dropStmtOrig);
-									appendPQExpBufferStr(ftStmt, dropStmt);
-								}
+								/* complain and emit unmodified command */
+								pg_log_warning("could not find where to insert IF EXISTS or CASCADE in statement \"%s\"",
+												dropStmtOrig);
+								appendPQExpBufferStr(ftStmt, dropStmt);
 							}
-
 							ahprintf(AH, "%s", ftStmt->data);
 
 							destroyPQExpBuffer(ftStmt);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d0ea489614..896f6e7f1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -375,6 +375,7 @@ main(int argc, char **argv)
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
+		{"drop-cascade", no_argument, &dopt.drop_cascade, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
 		{"extra-float-digits", required_argument, NULL, 8},
@@ -677,6 +678,9 @@ main(int argc, char **argv)
 		exit_nicely(1);
 	}
 
+	if (dopt.drop_cascade && !dopt.outputClean)
+		fatal("option --drop-cascade requires option -c/--clean");
+
 	if (dopt.if_exists && !dopt.outputClean)
 		fatal("option --if-exists requires option -c/--clean");
 
@@ -962,6 +966,7 @@ main(int argc, char **argv)
 	ropt->cparams.pghost = dopt.cparams.pghost ? pg_strdup(dopt.cparams.pghost) : NULL;
 	ropt->cparams.username = dopt.cparams.username ? pg_strdup(dopt.cparams.username) : NULL;
 	ropt->cparams.promptPassword = dopt.cparams.promptPassword;
+	ropt->drop_cascade = dopt.drop_cascade;
 	ropt->dropSchema = dopt.outputClean;
 	ropt->dataOnly = dopt.dataOnly;
 	ropt->schemaOnly = dopt.schemaOnly;
@@ -1063,6 +1068,7 @@ help(const char *progname)
 	printf(_("  --column-inserts             dump data as INSERT commands with column names\n"));
 	printf(_("  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security (dump only content user has\n"
 			 "                               access to)\n"));
 	printf(_("  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n"));
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 589b4aed53..d4636d5664 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -62,6 +62,7 @@ main(int argc, char **argv)
 	Archive    *AH;
 	char	   *inputFileSpec;
 	static int	disable_triggers = 0;
+	static int	drop_cascade = 0;
 	static int	enable_row_security = 0;
 	static int	if_exists = 0;
 	static int	no_data_for_failed_tables = 0;
@@ -108,6 +109,7 @@ main(int argc, char **argv)
 		 * the following options don't have an equivalent short option letter
 		 */
 		{"disable-triggers", no_argument, &disable_triggers, 1},
+		{"drop-cascade", no_argument, &drop_cascade, 1},
 		{"enable-row-security", no_argument, &enable_row_security, 1},
 		{"if-exists", no_argument, &if_exists, 1},
 		{"no-data-for-failed-tables", no_argument, &no_data_for_failed_tables, 1},
@@ -368,6 +370,13 @@ main(int argc, char **argv)
 	}
 
 	opts->disable_triggers = disable_triggers;
+
+	if (drop_cascade && !opts->dropSchema)
+	{
+		pg_log_error("option --drop-cascade requires option -c/--clean");
+		exit_nicely(1);
+	}
+	opts->drop_cascade = drop_cascade;
 	opts->enable_row_security = enable_row_security;
 	opts->noDataForFailedTables = no_data_for_failed_tables;
 	opts->noTablespace = outputNoTablespaces;
@@ -491,6 +500,7 @@ usage(const char *progname)
 	printf(_("  -x, --no-privileges          skip restoration of access privileges (grant/revoke)\n"));
 	printf(_("  -1, --single-transaction     restore as a single transaction\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security\n"));
 	printf(_("  --if-exists                  use IF EXISTS when dropping objects\n"));
 	printf(_("  --no-comments                do not restore comments\n"));
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index 083fb3ad08..e5fa4ce013 100644
--- a/src/bin/pg_dump/t/001_basic.pl
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -4,7 +4,7 @@ use warnings;
 use Config;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 82;
+use Test::More tests => 84;
 
 my $tempdir       = TestLib::tempdir;
 my $tempdir_short = TestLib::tempdir_short;
@@ -93,6 +93,11 @@ command_fails_like(
 	qr/\Qpg_dump: error: option --if-exists requires option -c\/--clean\E/,
 	'pg_dump: option --if-exists requires option -c/--clean');
 
+command_fails_like(
+	[ 'pg_dump', '--drop-cascade' ],
+	qr/\Qpg_dump: error: option --drop-cascade requires option -c\/--clean\E/,
+	'pg_dump: option --drop-cascade requires option -c/--clean');
+
 command_fails_like(
 	[ 'pg_dump', '-j3' ],
 	qr/\Qpg_dump: error: parallel backup only supported by the directory format\E/,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 86113df29c..1f39dc3c4f 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -73,6 +73,29 @@ my %pgdump_runs = (
 			'postgres',
 		],
 	},
+	clean_drop_cascade => {
+		dump_cmd => [
+			'pg_dump',
+			'--no-sync',
+			"--file=$tempdir/clean_drop_cascade.sql",
+			'-c',
+			'--drop-cascade',
+			'--encoding=UTF8',    # no-op, just tests that option is accepted
+			'postgres',
+		],
+	},
+	clean_if_exists_drop_cascade => {
+		dump_cmd => [
+			'pg_dump',
+			'--no-sync',
+			"--file=$tempdir/clean_if_exists_drop_cascade.sql",
+			'-c',
+			'--if-exists',
+			'--drop-cascade',
+			'--encoding=UTF8',    # no-op, just tests that option is accepted
+			'postgres',
+		],
+	},
 	column_inserts => {
 		dump_cmd => [
 			'pg_dump',                            '--no-sync',
@@ -386,20 +409,22 @@ my %dump_test_schema_runs = (
 # Tests which are considered 'full' dumps by pg_dump, but there
 # are flags used to exclude specific items (ACLs, blobs, etc).
 my %full_runs = (
-	binary_upgrade           => 1,
-	clean                    => 1,
-	clean_if_exists          => 1,
-	createdb                 => 1,
-	defaults                 => 1,
-	exclude_dump_test_schema => 1,
-	exclude_test_table       => 1,
-	exclude_test_table_data  => 1,
-	no_blobs                 => 1,
-	no_owner                 => 1,
-	no_privs                 => 1,
-	pg_dumpall_dbprivs       => 1,
-	pg_dumpall_exclude       => 1,
-	schema_only              => 1,);
+	binary_upgrade               => 1,
+	clean                        => 1,
+	clean_if_exists              => 1,
+	clean_drop_cascade           => 1,
+	clean_if_exists_drop_cascade => 1,
+	createdb                     => 1,
+	defaults                     => 1,
+	exclude_dump_test_schema     => 1,
+	exclude_test_table           => 1,
+	exclude_test_table_data      => 1,
+	no_blobs                     => 1,
+	no_owner                     => 1,
+	no_privs                     => 1,
+	pg_dumpall_dbprivs           => 1,
+	pg_dumpall_exclude           => 1,
+	schema_only                  => 1,);
 
 # This is where the actual tests are defined.
 my %tests = (
@@ -2676,22 +2701,24 @@ my %tests = (
 		\QCREATE INDEX measurement_city_id_logdate_idx ON ONLY dump_test.measurement USING\E
 		/xm,
 		like => {
-			binary_upgrade          => 1,
-			clean                   => 1,
-			clean_if_exists         => 1,
-			createdb                => 1,
-			defaults                => 1,
-			exclude_test_table      => 1,
-			exclude_test_table_data => 1,
-			no_blobs                => 1,
-			no_privs                => 1,
-			no_owner                => 1,
-			only_dump_test_schema   => 1,
-			pg_dumpall_dbprivs      => 1,
-			pg_dumpall_exclude      => 1,
-			schema_only             => 1,
-			section_post_data       => 1,
-			test_schema_plus_blobs  => 1,
+			binary_upgrade               => 1,
+			clean                        => 1,
+			clean_if_exists              => 1,
+			clean_drop_cascade           => 1,
+			clean_if_exists_drop_cascade => 1,
+			createdb                     => 1,
+			defaults                     => 1,
+			exclude_test_table           => 1,
+			exclude_test_table_data      => 1,
+			no_blobs                     => 1,
+			no_privs                     => 1,
+			no_owner                     => 1,
+			only_dump_test_schema        => 1,
+			pg_dumpall_dbprivs           => 1,
+			pg_dumpall_exclude           => 1,
+			schema_only                  => 1,
+			section_post_data            => 1,
+			test_schema_plus_blobs       => 1,
 		},
 		unlike => {
 			exclude_dump_test_schema => 1,
@@ -2747,22 +2774,24 @@ my %tests = (
 		\QALTER INDEX dump_test.measurement_pkey ATTACH PARTITION dump_test_second_schema.measurement_y2006m2_pkey\E
 		/xm,
 		like => {
-			binary_upgrade           => 1,
-			clean                    => 1,
-			clean_if_exists          => 1,
-			createdb                 => 1,
-			defaults                 => 1,
-			exclude_dump_test_schema => 1,
-			exclude_test_table       => 1,
-			exclude_test_table_data  => 1,
-			no_blobs                 => 1,
-			no_privs                 => 1,
-			no_owner                 => 1,
-			pg_dumpall_dbprivs       => 1,
-			pg_dumpall_exclude       => 1,
-			role                     => 1,
-			schema_only              => 1,
-			section_post_data        => 1,
+			binary_upgrade               => 1,
+			clean                        => 1,
+			clean_if_exists              => 1,
+			clean_drop_cascade           => 1,
+			clean_if_exists_drop_cascade => 1,
+			createdb                     => 1,
+			defaults                     => 1,
+			exclude_dump_test_schema     => 1,
+			exclude_test_table           => 1,
+			exclude_test_table_data      => 1,
+			no_blobs                     => 1,
+			no_privs                     => 1,
+			no_owner                     => 1,
+			pg_dumpall_dbprivs           => 1,
+			pg_dumpall_exclude           => 1,
+			role                         => 1,
+			schema_only                  => 1,
+			section_post_data            => 1,
 		},
 		unlike => {
 			only_dump_test_schema    => 1,
@@ -2904,6 +2933,32 @@ my %tests = (
 		like   => { clean_if_exists => 1, },
 	},
 
+	'DROP TYPE int42 CASCADE' => {
+		regexp => qr/^DROP TYPE dump_test.int42 CASCADE;/m,
+		like   => { clean => 1, clean_drop_cascade => 1 },
+	},
+	'DROP FUNCTION trigger_func CASCADE' => {
+		regexp => qr/^
+			\QDROP FUNCTION dump_test.trigger_func() CASCADE;\E
+			/xm,
+		like => { clean_drop_cascade => 1, },
+	},
+
+	'DROP LANGUAGE pltestlang' => {
+		regexp => qr/^DROP PROCEDURAL LANGUAGE pltestlang CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
+	'DROP SCHEMA dump_test' => {
+		regexp => qr/^DROP SCHEMA dump_test CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
+	'DROP TABLE test_table' => {
+		regexp => qr/^DROP TABLE dump_test\.test_table CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
 	'DROP ROLE regress_dump_test_role' => {
 		regexp => qr/^
 			\QDROP ROLE regress_dump_test_role;\E
-- 
2.31.1

From c2f0f3e922a55216a4d35092426c1f65bb218525 Mon Sep 17 00:00:00 2001
From: Wu Haotian <whtsky@gmail.com>
Date: Thu, 1 Apr 2021 17:14:29 +0800
Subject: [PATCH] pg_dump/restore: add --drop-cascade option

This option makes pg_dump and pg_store inject a CASCADE clause
to each DROP command.

This allows for restoring an old backup after adding new constraints,
at the risk of losing new data.
---
 doc/src/sgml/ref/pg_dump.sgml        |  22 ++++
 doc/src/sgml/ref/pg_restore.sgml     |  10 ++
 src/bin/pg_dump/pg_backup.h          |   2 +
 src/bin/pg_dump/pg_backup_archiver.c | 157 ++++++++++++++-------------
 src/bin/pg_dump/pg_dump.c            |   6 +
 src/bin/pg_dump/pg_restore.c         |  10 ++
 src/bin/pg_dump/t/001_basic.pl       |   7 +-
 src/bin/pg_dump/t/002_pg_dump.pl     | 147 +++++++++++++++++--------
 8 files changed, 240 insertions(+), 121 deletions(-)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 529b167c96..07971e549f 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -732,6 +732,22 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+
+       <para>
+        This option is ignored when emitting an archive (non-text) output
+        file.  For the archive formats, you can specify the option when you
+        call <command>pg_restore</command>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
@@ -791,6 +807,12 @@ PostgreSQL documentation
         clause) when cleaning database objects.  This option is not valid
         unless <option>--clean</option> is also specified.
        </para>
+
+       <para>
+        This option is ignored when emitting an archive (non-text) output
+        file.  For the archive formats, you can specify the option when you
+        call <command>pg_restore</command>.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 93ea937ac8..d52067a149 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -553,6 +553,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 3bc86635f7..f06ea051a3 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -83,6 +83,7 @@ typedef struct _restoreOptions
 									 * instead of OWNER TO */
 	char	   *superuser;		/* Username to use as superuser */
 	char	   *use_role;		/* Issue SET ROLE to this */
+	int			drop_cascade;
 	int			dropSchema;
 	int			disable_dollar_quoting;
 	int			dump_inserts;	/* 0 = COPY, otherwise rows per INSERT */
@@ -152,6 +153,7 @@ typedef struct _dumpOptions
 
 	/* flags for various command-line long options */
 	int			disable_dollar_quoting;
+	int			drop_cascade;
 	int			column_inserts;
 	int			if_exists;
 	int			no_comments;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 86de26a4bf..68c38eae49 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -187,6 +187,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt)
 	dopt->cparams.promptPassword = ropt->cparams.promptPassword;
 	dopt->outputClean = ropt->dropSchema;
 	dopt->dataOnly = ropt->dataOnly;
+	dopt->drop_cascade = ropt->drop_cascade;
 	dopt->schemaOnly = ropt->schemaOnly;
 	dopt->if_exists = ropt->if_exists;
 	dopt->column_inserts = ropt->column_inserts;
@@ -528,95 +529,103 @@ RestoreArchive(Archive *AHX)
 				 */
 				if (*te->dropStmt != '\0')
 				{
-					if (!ropt->if_exists)
+					if (!ropt->if_exists && !ropt->drop_cascade)
 					{
-						/* No --if-exists?	Then just use the original */
+						/* Just use the original */
 						ahprintf(AH, "%s", te->dropStmt);
 					}
+					/*
+					* Inject an appropriate spelling of "if exists" or "cascade".
+					* For large objects, we have a separate routine that
+					* knows how to do it, without depending on
+					* te->dropStmt; use that.  For other objects we need
+					* to parse the command.
+					*/
+					else if (strncmp(te->desc, "BLOB", 4) == 0)
+					{
+						if (ropt->if_exists)
+							DropBlobIfExists(AH, te->catalogId.oid);
+						else
+							ahprintf(AH, "%s", te->dropStmt);
+					}
 					else
 					{
+						char	   *dropStmt = pg_strdup(te->dropStmt);
+						char	   *dropStmtOrig = dropStmt;
+						PQExpBuffer ftStmt = createPQExpBuffer();
 						/*
-						 * Inject an appropriate spelling of "if exists".  For
-						 * large objects, we have a separate routine that
-						 * knows how to do it, without depending on
-						 * te->dropStmt; use that.  For other objects we need
-						 * to parse the command.
-						 */
-						if (strncmp(te->desc, "BLOB", 4) == 0)
+						* Need to inject IF EXISTS clause after ALTER
+						* TABLE part in ALTER TABLE .. DROP statement
+						*/
+						if (ropt->if_exists && strncmp(dropStmt, "ALTER TABLE", 11) == 0)
 						{
-							DropBlobIfExists(AH, te->catalogId.oid);
+							appendPQExpBufferStr(ftStmt,
+													"ALTER TABLE IF EXISTS");
+							dropStmt = dropStmt + 11;
 						}
+
+						/*
+						* ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
+						* not support the IF EXISTS or CASCADE clause, and therefore
+						* we simply emit the original command for DEFAULT
+						* objects (modulo the adjustment made above).
+						*
+						* Likewise, don't mess with DATABASE PROPERTIES.
+						*
+						* If we used CREATE OR REPLACE VIEW as a means of
+						* quasi-dropping an ON SELECT rule, that should
+						* be emitted unchanged as well.
+						*
+						* For other object types, we need to extract the
+						* first part of the DROP which includes the
+						* object type.  Most of the time this matches
+						* te->desc, so search for that; however for the
+						* different kinds of CONSTRAINTs, we know to
+						* search for hardcoded "DROP CONSTRAINT" instead.
+						*/
+						if (strcmp(te->desc, "DEFAULT") == 0 ||
+							strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
+							strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
+							appendPQExpBufferStr(ftStmt, dropStmt);
 						else
 						{
-							char	   *dropStmt = pg_strdup(te->dropStmt);
-							char	   *dropStmtOrig = dropStmt;
-							PQExpBuffer ftStmt = createPQExpBuffer();
-
-							/*
-							 * Need to inject IF EXISTS clause after ALTER
-							 * TABLE part in ALTER TABLE .. DROP statement
-							 */
-							if (strncmp(dropStmt, "ALTER TABLE", 11) == 0)
+							char		buffer[40];
+							char	   *mark;
+
+							if (strcmp(te->desc, "CONSTRAINT") == 0 ||
+								strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
+								strcmp(te->desc, "FK CONSTRAINT") == 0)
+								strcpy(buffer, "DROP CONSTRAINT");
+							else
+								snprintf(buffer, sizeof(buffer), "DROP %s",
+											te->desc);
+
+							mark = strstr(dropStmt, buffer);
+
+							if (mark)
 							{
-								appendPQExpBufferStr(ftStmt,
-													 "ALTER TABLE IF EXISTS");
-								dropStmt = dropStmt + 11;
+								char *name = mark + strlen(buffer);
+								*mark = '\0';
+								/*
+								 * pg_dump always add CASCADE for TYPE with circular dependencies.
+								 * avoid adding duplicated CASCADE.
+								 */
+								bool shouldCascade = ropt->drop_cascade && strstr(name, "CASCADE;") == NULL;
+								char *stmtEnd = strstr(name, ";");
+								*stmtEnd = '\0';
+								appendPQExpBuffer(ftStmt, "%s%s%s%s%s\n",
+													dropStmt, buffer,
+													ropt->if_exists ? " IF EXISTS" : "",
+													name,
+													shouldCascade ? " CASCADE;" : ";");
 							}
-
-							/*
-							 * ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
-							 * not support the IF EXISTS clause, and therefore
-							 * we simply emit the original command for DEFAULT
-							 * objects (modulo the adjustment made above).
-							 *
-							 * Likewise, don't mess with DATABASE PROPERTIES.
-							 *
-							 * If we used CREATE OR REPLACE VIEW as a means of
-							 * quasi-dropping an ON SELECT rule, that should
-							 * be emitted unchanged as well.
-							 *
-							 * For other object types, we need to extract the
-							 * first part of the DROP which includes the
-							 * object type.  Most of the time this matches
-							 * te->desc, so search for that; however for the
-							 * different kinds of CONSTRAINTs, we know to
-							 * search for hardcoded "DROP CONSTRAINT" instead.
-							 */
-							if (strcmp(te->desc, "DEFAULT") == 0 ||
-								strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
-								strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
-								appendPQExpBufferStr(ftStmt, dropStmt);
 							else
 							{
-								char		buffer[40];
-								char	   *mark;
-
-								if (strcmp(te->desc, "CONSTRAINT") == 0 ||
-									strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
-									strcmp(te->desc, "FK CONSTRAINT") == 0)
-									strcpy(buffer, "DROP CONSTRAINT");
-								else
-									snprintf(buffer, sizeof(buffer), "DROP %s",
-											 te->desc);
-
-								mark = strstr(dropStmt, buffer);
-
-								if (mark)
-								{
-									*mark = '\0';
-									appendPQExpBuffer(ftStmt, "%s%s IF EXISTS%s",
-													  dropStmt, buffer,
-													  mark + strlen(buffer));
-								}
-								else
-								{
-									/* complain and emit unmodified command */
-									pg_log_warning("could not find where to insert IF EXISTS in statement \"%s\"",
-												   dropStmtOrig);
-									appendPQExpBufferStr(ftStmt, dropStmt);
-								}
+								/* complain and emit unmodified command */
+								pg_log_warning("could not find where to insert IF EXISTS or CASCADE in statement \"%s\"",
+												dropStmtOrig);
+								appendPQExpBufferStr(ftStmt, dropStmt);
 							}
-
 							ahprintf(AH, "%s", ftStmt->data);
 
 							destroyPQExpBuffer(ftStmt);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d0ea489614..896f6e7f1e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -375,6 +375,7 @@ main(int argc, char **argv)
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
+		{"drop-cascade", no_argument, &dopt.drop_cascade, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
 		{"extra-float-digits", required_argument, NULL, 8},
@@ -677,6 +678,9 @@ main(int argc, char **argv)
 		exit_nicely(1);
 	}
 
+	if (dopt.drop_cascade && !dopt.outputClean)
+		fatal("option --drop-cascade requires option -c/--clean");
+
 	if (dopt.if_exists && !dopt.outputClean)
 		fatal("option --if-exists requires option -c/--clean");
 
@@ -962,6 +966,7 @@ main(int argc, char **argv)
 	ropt->cparams.pghost = dopt.cparams.pghost ? pg_strdup(dopt.cparams.pghost) : NULL;
 	ropt->cparams.username = dopt.cparams.username ? pg_strdup(dopt.cparams.username) : NULL;
 	ropt->cparams.promptPassword = dopt.cparams.promptPassword;
+	ropt->drop_cascade = dopt.drop_cascade;
 	ropt->dropSchema = dopt.outputClean;
 	ropt->dataOnly = dopt.dataOnly;
 	ropt->schemaOnly = dopt.schemaOnly;
@@ -1063,6 +1068,7 @@ help(const char *progname)
 	printf(_("  --column-inserts             dump data as INSERT commands with column names\n"));
 	printf(_("  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security (dump only content user has\n"
 			 "                               access to)\n"));
 	printf(_("  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n"));
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 589b4aed53..d4636d5664 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -62,6 +62,7 @@ main(int argc, char **argv)
 	Archive    *AH;
 	char	   *inputFileSpec;
 	static int	disable_triggers = 0;
+	static int	drop_cascade = 0;
 	static int	enable_row_security = 0;
 	static int	if_exists = 0;
 	static int	no_data_for_failed_tables = 0;
@@ -108,6 +109,7 @@ main(int argc, char **argv)
 		 * the following options don't have an equivalent short option letter
 		 */
 		{"disable-triggers", no_argument, &disable_triggers, 1},
+		{"drop-cascade", no_argument, &drop_cascade, 1},
 		{"enable-row-security", no_argument, &enable_row_security, 1},
 		{"if-exists", no_argument, &if_exists, 1},
 		{"no-data-for-failed-tables", no_argument, &no_data_for_failed_tables, 1},
@@ -368,6 +370,13 @@ main(int argc, char **argv)
 	}
 
 	opts->disable_triggers = disable_triggers;
+
+	if (drop_cascade && !opts->dropSchema)
+	{
+		pg_log_error("option --drop-cascade requires option -c/--clean");
+		exit_nicely(1);
+	}
+	opts->drop_cascade = drop_cascade;
 	opts->enable_row_security = enable_row_security;
 	opts->noDataForFailedTables = no_data_for_failed_tables;
 	opts->noTablespace = outputNoTablespaces;
@@ -491,6 +500,7 @@ usage(const char *progname)
 	printf(_("  -x, --no-privileges          skip restoration of access privileges (grant/revoke)\n"));
 	printf(_("  -1, --single-transaction     restore as a single transaction\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security\n"));
 	printf(_("  --if-exists                  use IF EXISTS when dropping objects\n"));
 	printf(_("  --no-comments                do not restore comments\n"));
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index 083fb3ad08..e5fa4ce013 100644
--- a/src/bin/pg_dump/t/001_basic.pl
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -4,7 +4,7 @@ use warnings;
 use Config;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 82;
+use Test::More tests => 84;
 
 my $tempdir       = TestLib::tempdir;
 my $tempdir_short = TestLib::tempdir_short;
@@ -93,6 +93,11 @@ command_fails_like(
 	qr/\Qpg_dump: error: option --if-exists requires option -c\/--clean\E/,
 	'pg_dump: option --if-exists requires option -c/--clean');
 
+command_fails_like(
+	[ 'pg_dump', '--drop-cascade' ],
+	qr/\Qpg_dump: error: option --drop-cascade requires option -c\/--clean\E/,
+	'pg_dump: option --drop-cascade requires option -c/--clean');
+
 command_fails_like(
 	[ 'pg_dump', '-j3' ],
 	qr/\Qpg_dump: error: parallel backup only supported by the directory format\E/,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 86113df29c..1f39dc3c4f 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -73,6 +73,29 @@ my %pgdump_runs = (
 			'postgres',
 		],
 	},
+	clean_drop_cascade => {
+		dump_cmd => [
+			'pg_dump',
+			'--no-sync',
+			"--file=$tempdir/clean_drop_cascade.sql",
+			'-c',
+			'--drop-cascade',
+			'--encoding=UTF8',    # no-op, just tests that option is accepted
+			'postgres',
+		],
+	},
+	clean_if_exists_drop_cascade => {
+		dump_cmd => [
+			'pg_dump',
+			'--no-sync',
+			"--file=$tempdir/clean_if_exists_drop_cascade.sql",
+			'-c',
+			'--if-exists',
+			'--drop-cascade',
+			'--encoding=UTF8',    # no-op, just tests that option is accepted
+			'postgres',
+		],
+	},
 	column_inserts => {
 		dump_cmd => [
 			'pg_dump',                            '--no-sync',
@@ -386,20 +409,22 @@ my %dump_test_schema_runs = (
 # Tests which are considered 'full' dumps by pg_dump, but there
 # are flags used to exclude specific items (ACLs, blobs, etc).
 my %full_runs = (
-	binary_upgrade           => 1,
-	clean                    => 1,
-	clean_if_exists          => 1,
-	createdb                 => 1,
-	defaults                 => 1,
-	exclude_dump_test_schema => 1,
-	exclude_test_table       => 1,
-	exclude_test_table_data  => 1,
-	no_blobs                 => 1,
-	no_owner                 => 1,
-	no_privs                 => 1,
-	pg_dumpall_dbprivs       => 1,
-	pg_dumpall_exclude       => 1,
-	schema_only              => 1,);
+	binary_upgrade               => 1,
+	clean                        => 1,
+	clean_if_exists              => 1,
+	clean_drop_cascade           => 1,
+	clean_if_exists_drop_cascade => 1,
+	createdb                     => 1,
+	defaults                     => 1,
+	exclude_dump_test_schema     => 1,
+	exclude_test_table           => 1,
+	exclude_test_table_data      => 1,
+	no_blobs                     => 1,
+	no_owner                     => 1,
+	no_privs                     => 1,
+	pg_dumpall_dbprivs           => 1,
+	pg_dumpall_exclude           => 1,
+	schema_only                  => 1,);
 
 # This is where the actual tests are defined.
 my %tests = (
@@ -2676,22 +2701,24 @@ my %tests = (
 		\QCREATE INDEX measurement_city_id_logdate_idx ON ONLY dump_test.measurement USING\E
 		/xm,
 		like => {
-			binary_upgrade          => 1,
-			clean                   => 1,
-			clean_if_exists         => 1,
-			createdb                => 1,
-			defaults                => 1,
-			exclude_test_table      => 1,
-			exclude_test_table_data => 1,
-			no_blobs                => 1,
-			no_privs                => 1,
-			no_owner                => 1,
-			only_dump_test_schema   => 1,
-			pg_dumpall_dbprivs      => 1,
-			pg_dumpall_exclude      => 1,
-			schema_only             => 1,
-			section_post_data       => 1,
-			test_schema_plus_blobs  => 1,
+			binary_upgrade               => 1,
+			clean                        => 1,
+			clean_if_exists              => 1,
+			clean_drop_cascade           => 1,
+			clean_if_exists_drop_cascade => 1,
+			createdb                     => 1,
+			defaults                     => 1,
+			exclude_test_table           => 1,
+			exclude_test_table_data      => 1,
+			no_blobs                     => 1,
+			no_privs                     => 1,
+			no_owner                     => 1,
+			only_dump_test_schema        => 1,
+			pg_dumpall_dbprivs           => 1,
+			pg_dumpall_exclude           => 1,
+			schema_only                  => 1,
+			section_post_data            => 1,
+			test_schema_plus_blobs       => 1,
 		},
 		unlike => {
 			exclude_dump_test_schema => 1,
@@ -2747,22 +2774,24 @@ my %tests = (
 		\QALTER INDEX dump_test.measurement_pkey ATTACH PARTITION dump_test_second_schema.measurement_y2006m2_pkey\E
 		/xm,
 		like => {
-			binary_upgrade           => 1,
-			clean                    => 1,
-			clean_if_exists          => 1,
-			createdb                 => 1,
-			defaults                 => 1,
-			exclude_dump_test_schema => 1,
-			exclude_test_table       => 1,
-			exclude_test_table_data  => 1,
-			no_blobs                 => 1,
-			no_privs                 => 1,
-			no_owner                 => 1,
-			pg_dumpall_dbprivs       => 1,
-			pg_dumpall_exclude       => 1,
-			role                     => 1,
-			schema_only              => 1,
-			section_post_data        => 1,
+			binary_upgrade               => 1,
+			clean                        => 1,
+			clean_if_exists              => 1,
+			clean_drop_cascade           => 1,
+			clean_if_exists_drop_cascade => 1,
+			createdb                     => 1,
+			defaults                     => 1,
+			exclude_dump_test_schema     => 1,
+			exclude_test_table           => 1,
+			exclude_test_table_data      => 1,
+			no_blobs                     => 1,
+			no_privs                     => 1,
+			no_owner                     => 1,
+			pg_dumpall_dbprivs           => 1,
+			pg_dumpall_exclude           => 1,
+			role                         => 1,
+			schema_only                  => 1,
+			section_post_data            => 1,
 		},
 		unlike => {
 			only_dump_test_schema    => 1,
@@ -2904,6 +2933,32 @@ my %tests = (
 		like   => { clean_if_exists => 1, },
 	},
 
+	'DROP TYPE int42 CASCADE' => {
+		regexp => qr/^DROP TYPE dump_test.int42 CASCADE;/m,
+		like   => { clean => 1, clean_drop_cascade => 1 },
+	},
+	'DROP FUNCTION trigger_func CASCADE' => {
+		regexp => qr/^
+			\QDROP FUNCTION dump_test.trigger_func() CASCADE;\E
+			/xm,
+		like => { clean_drop_cascade => 1, },
+	},
+
+	'DROP LANGUAGE pltestlang' => {
+		regexp => qr/^DROP PROCEDURAL LANGUAGE pltestlang CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
+	'DROP SCHEMA dump_test' => {
+		regexp => qr/^DROP SCHEMA dump_test CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
+	'DROP TABLE test_table' => {
+		regexp => qr/^DROP TABLE dump_test\.test_table CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
 	'DROP ROLE regress_dump_test_role' => {
 		regexp => qr/^
 			\QDROP ROLE regress_dump_test_role;\E
-- 
2.31.1

From b170e234c9f474651088b59c966fe21e524562f5 Mon Sep 17 00:00:00 2001
From: Wu Haotian <whtsky@gmail.com>
Date: Thu, 1 Apr 2021 17:14:29 +0800
Subject: [PATCH] pg_dump/restore: add --drop-cascade option

This option makes pg_dump and pg_store inject a CASCADE clause
to each DROP command.

This allows for restoring an old backup after adding new constraints,
at the risk of losing new data.
---
 doc/src/sgml/ref/pg_dump.sgml        |  22 ++++
 doc/src/sgml/ref/pg_restore.sgml     |  10 ++
 src/bin/pg_dump/pg_backup.h          |   2 +
 src/bin/pg_dump/pg_backup_archiver.c | 157 ++++++++++++++-------------
 src/bin/pg_dump/pg_dump.c            |   6 +
 src/bin/pg_dump/pg_restore.c         |  10 ++
 src/bin/pg_dump/t/001_basic.pl       |   7 +-
 src/bin/pg_dump/t/002_pg_dump.pl     | 153 +++++++++++++++++---------
 8 files changed, 243 insertions(+), 124 deletions(-)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 7682226b99..d333840f84 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -738,6 +738,22 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+
+       <para>
+        This option is ignored when emitting an archive (non-text) output
+        file.  For the archive formats, you can specify the option when you
+        call <command>pg_restore</command>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
@@ -797,6 +813,12 @@ PostgreSQL documentation
         clause) when cleaning database objects.  This option is not valid
         unless <option>--clean</option> is also specified.
        </para>
+
+       <para>
+        This option is ignored when emitting an archive (non-text) output
+        file.  For the archive formats, you can specify the option when you
+        call <command>pg_restore</command>.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 93ea937ac8..d52067a149 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -553,6 +553,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--drop-cascade</option></term>
+      <listitem>
+       <para>
+        Use <literal>CASCADE</literal> to drop database objects.
+        This option is not valid unless <option>--clean</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 3c1cd858a8..0f8763b819 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -83,6 +83,7 @@ typedef struct _restoreOptions
 									 * instead of OWNER TO */
 	char	   *superuser;		/* Username to use as superuser */
 	char	   *use_role;		/* Issue SET ROLE to this */
+	int			drop_cascade;
 	int			dropSchema;
 	int			disable_dollar_quoting;
 	int			dump_inserts;	/* 0 = COPY, otherwise rows per INSERT */
@@ -152,6 +153,7 @@ typedef struct _dumpOptions
 
 	/* flags for various command-line long options */
 	int			disable_dollar_quoting;
+	int			drop_cascade;
 	int			column_inserts;
 	int			if_exists;
 	int			no_comments;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index ee06dc6822..3a0e9d0081 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -186,6 +186,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt)
 	dopt->cparams.promptPassword = ropt->cparams.promptPassword;
 	dopt->outputClean = ropt->dropSchema;
 	dopt->dataOnly = ropt->dataOnly;
+	dopt->drop_cascade = ropt->drop_cascade;
 	dopt->schemaOnly = ropt->schemaOnly;
 	dopt->if_exists = ropt->if_exists;
 	dopt->column_inserts = ropt->column_inserts;
@@ -527,95 +528,103 @@ RestoreArchive(Archive *AHX)
 				 */
 				if (*te->dropStmt != '\0')
 				{
-					if (!ropt->if_exists)
+					if (!ropt->if_exists && !ropt->drop_cascade)
 					{
-						/* No --if-exists?	Then just use the original */
+						/* Just use the original */
 						ahprintf(AH, "%s", te->dropStmt);
 					}
+					/*
+					* Inject an appropriate spelling of "if exists" or "cascade".
+					* For large objects, we have a separate routine that
+					* knows how to do it, without depending on
+					* te->dropStmt; use that.  For other objects we need
+					* to parse the command.
+					*/
+					else if (strncmp(te->desc, "BLOB", 4) == 0)
+					{
+						if (ropt->if_exists)
+							DropBlobIfExists(AH, te->catalogId.oid);
+						else
+							ahprintf(AH, "%s", te->dropStmt);
+					}
 					else
 					{
+						char	   *dropStmt = pg_strdup(te->dropStmt);
+						char	   *dropStmtOrig = dropStmt;
+						PQExpBuffer ftStmt = createPQExpBuffer();
 						/*
-						 * Inject an appropriate spelling of "if exists".  For
-						 * large objects, we have a separate routine that
-						 * knows how to do it, without depending on
-						 * te->dropStmt; use that.  For other objects we need
-						 * to parse the command.
-						 */
-						if (strncmp(te->desc, "BLOB", 4) == 0)
+						* Need to inject IF EXISTS clause after ALTER
+						* TABLE part in ALTER TABLE .. DROP statement
+						*/
+						if (ropt->if_exists && strncmp(dropStmt, "ALTER TABLE", 11) == 0)
 						{
-							DropBlobIfExists(AH, te->catalogId.oid);
+							appendPQExpBufferStr(ftStmt,
+													"ALTER TABLE IF EXISTS");
+							dropStmt = dropStmt + 11;
 						}
+
+						/*
+						* ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
+						* not support the IF EXISTS or CASCADE clause, and therefore
+						* we simply emit the original command for DEFAULT
+						* objects (modulo the adjustment made above).
+						*
+						* Likewise, don't mess with DATABASE PROPERTIES.
+						*
+						* If we used CREATE OR REPLACE VIEW as a means of
+						* quasi-dropping an ON SELECT rule, that should
+						* be emitted unchanged as well.
+						*
+						* For other object types, we need to extract the
+						* first part of the DROP which includes the
+						* object type.  Most of the time this matches
+						* te->desc, so search for that; however for the
+						* different kinds of CONSTRAINTs, we know to
+						* search for hardcoded "DROP CONSTRAINT" instead.
+						*/
+						if (strcmp(te->desc, "DEFAULT") == 0 ||
+							strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
+							strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
+							appendPQExpBufferStr(ftStmt, dropStmt);
 						else
 						{
-							char	   *dropStmt = pg_strdup(te->dropStmt);
-							char	   *dropStmtOrig = dropStmt;
-							PQExpBuffer ftStmt = createPQExpBuffer();
-
-							/*
-							 * Need to inject IF EXISTS clause after ALTER
-							 * TABLE part in ALTER TABLE .. DROP statement
-							 */
-							if (strncmp(dropStmt, "ALTER TABLE", 11) == 0)
+							char		buffer[40];
+							char	   *mark;
+
+							if (strcmp(te->desc, "CONSTRAINT") == 0 ||
+								strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
+								strcmp(te->desc, "FK CONSTRAINT") == 0)
+								strcpy(buffer, "DROP CONSTRAINT");
+							else
+								snprintf(buffer, sizeof(buffer), "DROP %s",
+											te->desc);
+
+							mark = strstr(dropStmt, buffer);
+
+							if (mark)
 							{
-								appendPQExpBufferStr(ftStmt,
-													 "ALTER TABLE IF EXISTS");
-								dropStmt = dropStmt + 11;
+								char *name = mark + strlen(buffer);
+								*mark = '\0';
+								/*
+								 * pg_dump always add CASCADE for TYPE with circular dependencies.
+								 * avoid adding duplicated CASCADE.
+								 */
+								bool shouldCascade = ropt->drop_cascade && strstr(name, "CASCADE;") == NULL;
+								char *stmtEnd = strstr(name, ";");
+								*stmtEnd = '\0';
+								appendPQExpBuffer(ftStmt, "%s%s%s%s%s\n",
+													dropStmt, buffer,
+													ropt->if_exists ? " IF EXISTS" : "",
+													name,
+													shouldCascade ? " CASCADE;" : ";");
 							}
-
-							/*
-							 * ALTER TABLE..ALTER COLUMN..DROP DEFAULT does
-							 * not support the IF EXISTS clause, and therefore
-							 * we simply emit the original command for DEFAULT
-							 * objects (modulo the adjustment made above).
-							 *
-							 * Likewise, don't mess with DATABASE PROPERTIES.
-							 *
-							 * If we used CREATE OR REPLACE VIEW as a means of
-							 * quasi-dropping an ON SELECT rule, that should
-							 * be emitted unchanged as well.
-							 *
-							 * For other object types, we need to extract the
-							 * first part of the DROP which includes the
-							 * object type.  Most of the time this matches
-							 * te->desc, so search for that; however for the
-							 * different kinds of CONSTRAINTs, we know to
-							 * search for hardcoded "DROP CONSTRAINT" instead.
-							 */
-							if (strcmp(te->desc, "DEFAULT") == 0 ||
-								strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
-								strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
-								appendPQExpBufferStr(ftStmt, dropStmt);
 							else
 							{
-								char		buffer[40];
-								char	   *mark;
-
-								if (strcmp(te->desc, "CONSTRAINT") == 0 ||
-									strcmp(te->desc, "CHECK CONSTRAINT") == 0 ||
-									strcmp(te->desc, "FK CONSTRAINT") == 0)
-									strcpy(buffer, "DROP CONSTRAINT");
-								else
-									snprintf(buffer, sizeof(buffer), "DROP %s",
-											 te->desc);
-
-								mark = strstr(dropStmt, buffer);
-
-								if (mark)
-								{
-									*mark = '\0';
-									appendPQExpBuffer(ftStmt, "%s%s IF EXISTS%s",
-													  dropStmt, buffer,
-													  mark + strlen(buffer));
-								}
-								else
-								{
-									/* complain and emit unmodified command */
-									pg_log_warning("could not find where to insert IF EXISTS in statement \"%s\"",
-												   dropStmtOrig);
-									appendPQExpBufferStr(ftStmt, dropStmt);
-								}
+								/* complain and emit unmodified command */
+								pg_log_warning("could not find where to insert IF EXISTS or CASCADE in statement \"%s\"",
+												dropStmtOrig);
+								appendPQExpBufferStr(ftStmt, dropStmt);
 							}
-
 							ahprintf(AH, "%s", ftStmt->data);
 
 							destroyPQExpBuffer(ftStmt);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 67be849829..5e1249d32e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -377,6 +377,7 @@ main(int argc, char **argv)
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
+		{"drop-cascade", no_argument, &dopt.drop_cascade, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
 		{"extra-float-digits", required_argument, NULL, 8},
@@ -666,6 +667,9 @@ main(int argc, char **argv)
 		exit_nicely(1);
 	}
 
+	if (dopt.drop_cascade && !dopt.outputClean)
+		fatal("option --drop-cascade requires option -c/--clean");
+
 	if (dopt.if_exists && !dopt.outputClean)
 		fatal("option --if-exists requires option -c/--clean");
 
@@ -933,6 +937,7 @@ main(int argc, char **argv)
 	ropt->cparams.pghost = dopt.cparams.pghost ? pg_strdup(dopt.cparams.pghost) : NULL;
 	ropt->cparams.username = dopt.cparams.username ? pg_strdup(dopt.cparams.username) : NULL;
 	ropt->cparams.promptPassword = dopt.cparams.promptPassword;
+	ropt->drop_cascade = dopt.drop_cascade;
 	ropt->dropSchema = dopt.outputClean;
 	ropt->dataOnly = dopt.dataOnly;
 	ropt->schemaOnly = dopt.schemaOnly;
@@ -1034,6 +1039,7 @@ help(const char *progname)
 	printf(_("  --column-inserts             dump data as INSERT commands with column names\n"));
 	printf(_("  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security (dump only content user has\n"
 			 "                               access to)\n"));
 	printf(_("  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n"));
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 64aaa80eee..6f0b63d065 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -63,6 +63,7 @@ main(int argc, char **argv)
 	Archive    *AH;
 	char	   *inputFileSpec;
 	static int	disable_triggers = 0;
+	static int	drop_cascade = 0;
 	static int	enable_row_security = 0;
 	static int	if_exists = 0;
 	static int	no_data_for_failed_tables = 0;
@@ -109,6 +110,7 @@ main(int argc, char **argv)
 		 * the following options don't have an equivalent short option letter
 		 */
 		{"disable-triggers", no_argument, &disable_triggers, 1},
+		{"drop-cascade", no_argument, &drop_cascade, 1},
 		{"enable-row-security", no_argument, &enable_row_security, 1},
 		{"if-exists", no_argument, &if_exists, 1},
 		{"no-data-for-failed-tables", no_argument, &no_data_for_failed_tables, 1},
@@ -356,6 +358,13 @@ main(int argc, char **argv)
 	}
 
 	opts->disable_triggers = disable_triggers;
+
+	if (drop_cascade && !opts->dropSchema)
+	{
+		pg_log_error("option --drop-cascade requires option -c/--clean");
+		exit_nicely(1);
+	}
+	opts->drop_cascade = drop_cascade;
 	opts->enable_row_security = enable_row_security;
 	opts->noDataForFailedTables = no_data_for_failed_tables;
 	opts->noTablespace = outputNoTablespaces;
@@ -479,6 +488,7 @@ usage(const char *progname)
 	printf(_("  -x, --no-privileges          skip restoration of access privileges (grant/revoke)\n"));
 	printf(_("  -1, --single-transaction     restore as a single transaction\n"));
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
+	printf(_("  --drop-cascade               use CASCADE when dropping objects\n"));
 	printf(_("  --enable-row-security        enable row security\n"));
 	printf(_("  --if-exists                  use IF EXISTS when dropping objects\n"));
 	printf(_("  --no-comments                do not restore comments\n"));
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index d6731855ed..40e2029c0c 100644
--- a/src/bin/pg_dump/t/001_basic.pl
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -7,7 +7,7 @@ use warnings;
 use Config;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 82;
+use Test::More tests => 84;
 
 my $tempdir       = TestLib::tempdir;
 
@@ -95,6 +95,11 @@ command_fails_like(
 	qr/\Qpg_dump: error: option --if-exists requires option -c\/--clean\E/,
 	'pg_dump: option --if-exists requires option -c/--clean');
 
+command_fails_like(
+	[ 'pg_dump', '--drop-cascade' ],
+	qr/\Qpg_dump: error: option --drop-cascade requires option -c\/--clean\E/,
+	'pg_dump: option --drop-cascade requires option -c/--clean');
+
 command_fails_like(
 	[ 'pg_dump', '-j3' ],
 	qr/\Qpg_dump: error: parallel backup only supported by the directory format\E/,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index e1b7e31458..b118e15b58 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -75,6 +75,29 @@ my %pgdump_runs = (
 			'postgres',
 		],
 	},
+	clean_drop_cascade => {
+		dump_cmd => [
+			'pg_dump',
+			'--no-sync',
+			"--file=$tempdir/clean_drop_cascade.sql",
+			'-c',
+			'--drop-cascade',
+			'--encoding=UTF8',    # no-op, just tests that option is accepted
+			'postgres',
+		],
+	},
+	clean_if_exists_drop_cascade => {
+		dump_cmd => [
+			'pg_dump',
+			'--no-sync',
+			"--file=$tempdir/clean_if_exists_drop_cascade.sql",
+			'-c',
+			'--if-exists',
+			'--drop-cascade',
+			'--encoding=UTF8',    # no-op, just tests that option is accepted
+			'postgres',
+		],
+	},
 	column_inserts => {
 		dump_cmd => [
 			'pg_dump',                            '--no-sync',
@@ -407,21 +430,23 @@ my %dump_test_schema_runs = (
 # Tests which are considered 'full' dumps by pg_dump, but there
 # are flags used to exclude specific items (ACLs, blobs, etc).
 my %full_runs = (
-	binary_upgrade           => 1,
-	clean                    => 1,
-	clean_if_exists          => 1,
-	createdb                 => 1,
-	defaults                 => 1,
-	exclude_dump_test_schema => 1,
-	exclude_test_table       => 1,
-	exclude_test_table_data  => 1,
-	no_toast_compression     => 1,
-	no_blobs                 => 1,
-	no_owner                 => 1,
-	no_privs                 => 1,
-	pg_dumpall_dbprivs       => 1,
-	pg_dumpall_exclude       => 1,
-	schema_only              => 1,);
+	binary_upgrade               => 1,
+	clean                        => 1,
+	clean_if_exists              => 1,
+	clean_drop_cascade           => 1,
+	clean_if_exists_drop_cascade => 1,
+	createdb                     => 1,
+	defaults                     => 1,
+	exclude_dump_test_schema     => 1,
+	exclude_test_table           => 1,
+	exclude_test_table_data      => 1,
+	no_toast_compression         => 1,
+	no_blobs                     => 1,
+	no_owner                     => 1,
+	no_privs                     => 1,
+	pg_dumpall_dbprivs           => 1,
+	pg_dumpall_exclude           => 1,
+	schema_only                  => 1,);
 
 # This is where the actual tests are defined.
 my %tests = (
@@ -2844,23 +2869,25 @@ my %tests = (
 		\QCREATE INDEX measurement_city_id_logdate_idx ON ONLY dump_test.measurement USING\E
 		/xm,
 		like => {
-			binary_upgrade          => 1,
-			clean                   => 1,
-			clean_if_exists         => 1,
-			createdb                => 1,
-			defaults                => 1,
-			exclude_test_table      => 1,
-			exclude_test_table_data => 1,
-			no_toast_compression    => 1,
-			no_blobs                => 1,
-			no_privs                => 1,
-			no_owner                => 1,
-			only_dump_test_schema   => 1,
-			pg_dumpall_dbprivs      => 1,
-			pg_dumpall_exclude      => 1,
-			schema_only             => 1,
-			section_post_data       => 1,
-			test_schema_plus_blobs  => 1,
+			binary_upgrade               => 1,
+			clean                        => 1,
+			clean_if_exists              => 1,
+			clean_drop_cascade           => 1,
+			clean_if_exists_drop_cascade => 1,
+			createdb                     => 1,
+			defaults                     => 1,
+			exclude_test_table           => 1,
+			exclude_test_table_data      => 1,
+			no_toast_compression         => 1,
+			no_blobs                     => 1,
+			no_privs                     => 1,
+			no_owner                     => 1,
+			only_dump_test_schema        => 1,
+			pg_dumpall_dbprivs           => 1,
+			pg_dumpall_exclude           => 1,
+			schema_only                  => 1,
+			section_post_data            => 1,
+			test_schema_plus_blobs       => 1,
 		},
 		unlike => {
 			exclude_dump_test_schema => 1,
@@ -2916,23 +2943,25 @@ my %tests = (
 		\QALTER INDEX dump_test.measurement_pkey ATTACH PARTITION dump_test_second_schema.measurement_y2006m2_pkey\E
 		/xm,
 		like => {
-			binary_upgrade           => 1,
-			clean                    => 1,
-			clean_if_exists          => 1,
-			createdb                 => 1,
-			defaults                 => 1,
-			exclude_dump_test_schema => 1,
-			exclude_test_table       => 1,
-			exclude_test_table_data  => 1,
-			no_toast_compression     => 1,
-			no_blobs                 => 1,
-			no_privs                 => 1,
-			no_owner                 => 1,
-			pg_dumpall_dbprivs       => 1,
-			pg_dumpall_exclude       => 1,
-			role                     => 1,
-			schema_only              => 1,
-			section_post_data        => 1,
+			binary_upgrade               => 1,
+			clean                        => 1,
+			clean_if_exists              => 1,
+			clean_drop_cascade           => 1,
+			clean_if_exists_drop_cascade => 1,
+			createdb                     => 1,
+			defaults                     => 1,
+			exclude_dump_test_schema     => 1,
+			exclude_test_table           => 1,
+			exclude_test_table_data      => 1,
+			no_toast_compression         => 1,
+			no_blobs                     => 1,
+			no_privs                     => 1,
+			no_owner                     => 1,
+			pg_dumpall_dbprivs           => 1,
+			pg_dumpall_exclude           => 1,
+			role                         => 1,
+			schema_only                  => 1,
+			section_post_data            => 1,
 		},
 		unlike => {
 			only_dump_test_schema    => 1,
@@ -3074,6 +3103,32 @@ my %tests = (
 		like   => { clean_if_exists => 1, },
 	},
 
+	'DROP TYPE int42 CASCADE' => {
+		regexp => qr/^DROP TYPE dump_test.int42 CASCADE;/m,
+		like   => { clean => 1, clean_drop_cascade => 1 },
+	},
+	'DROP FUNCTION trigger_func CASCADE' => {
+		regexp => qr/^
+			\QDROP FUNCTION dump_test.trigger_func() CASCADE;\E
+			/xm,
+		like => { clean_drop_cascade => 1, },
+	},
+
+	'DROP LANGUAGE pltestlang' => {
+		regexp => qr/^DROP PROCEDURAL LANGUAGE pltestlang CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
+	'DROP SCHEMA dump_test' => {
+		regexp => qr/^DROP SCHEMA dump_test CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
+	'DROP TABLE test_table' => {
+		regexp => qr/^DROP TABLE dump_test\.test_table CASCADE;/m,
+		like   => { clean_drop_cascade => 1, },
+	},
+
 	'DROP ROLE regress_dump_test_role' => {
 		regexp => qr/^
 			\QDROP ROLE regress_dump_test_role;\E
-- 
2.25.1