0001-Add-pg_dump-on-conflict-do-nothing-option-v4.patch

From 997672c92c23de2c0575aff9e753c41741a32d79 Mon Sep 17 00:00:00 2001
From: Thomas Munro <tmunro@postgresql.org>
Date: Thu, 12 Jul 2018 22:36:55 +1200
Subject: [PATCH] Add pg_dump --on-conflict-do-nothing option.

When dumping INSERT statements, provide a way to add ON CONFLICT DO NOTHING.

Author: Surafel Temesgen
Reviewed-by: Takeshi Ideriha, Nico Williams, Dilip Kumar
Discussion: https://postgr.es/m/CALAY4q-PQ9cOEzs2%2BQHK5ObfF_4QbmBaYXbZx6BGGN66Q-n8FA%40mail.gmail.com
---
 doc/src/sgml/ref/pg_dump.sgml    | 12 ++++++++++++
 doc/src/sgml/ref/pg_dumpall.sgml | 12 ++++++++++++
 src/bin/pg_dump/pg_backup.h      |  1 +
 src/bin/pg_dump/pg_dump.c        | 11 ++++++++++-
 src/bin/pg_dump/pg_dumpall.c     | 11 +++++++++++
 src/bin/pg_dump/t/001_basic.pl   | 13 ++++++++++++-
 6 files changed, 58 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index f402d46b0c..8286b2dddd 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -909,6 +909,18 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--on-conflict-do-nothing</option></term>
+      <listitem>
+       <para>
+        Add <literal>ON CONFLICT DO NOTHING</literal> to
+        <command>INSERT</command> commands.
+        This option is not valid unless <option>--inserts</option> or 
+        <option>--column-inserts</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--quote-all-identifiers</option></term>
       <listitem>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 22cb790703..94d76c30db 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -446,6 +446,18 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--on-conflict-do-nothing</option></term>
+      <listitem>
+       <para>
+        Add <literal>ON CONFLICT DO NOTHING</literal> to
+        <command>INSERT</command> commands.
+        This option is not valid unless <option>--inserts</option> or 
+        <option>--column-inserts</option> is also specified.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--quote-all-identifiers</option></term>
       <listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index ceedd481fb..42cf441aaf 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -172,6 +172,7 @@ typedef struct _dumpOptions
 	char	   *outputSuperuser;
 
 	int			sequence_data;	/* dump sequence data even in schema-only mode */
+	int			do_nothing;
 } DumpOptions;
 
 /*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 463639208d..98374c2127 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -378,6 +378,7 @@ main(int argc, char **argv)
 		{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
 		{"no-subscriptions", no_argument, &dopt.no_subscriptions, 1},
 		{"no-sync", no_argument, NULL, 7},
+		{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
 
 		{NULL, 0, NULL, 0}
 	};
@@ -619,6 +620,9 @@ main(int argc, char **argv)
 	if (dopt.if_exists && !dopt.outputClean)
 		exit_horribly(NULL, "option --if-exists requires option -c/--clean\n");
 
+	if (dopt.do_nothing && !(dopt.dump_inserts || dopt.column_inserts))
+		exit_horribly(NULL, "option --on-conflict-do-nothing requires option --inserts or --column-inserts\n");
+
 	/* Identify archive format to emit */
 	archiveFormat = parseArchiveFormat(format, &archiveMode);
 
@@ -989,6 +993,7 @@ help(const char *progname)
 	printf(_("  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs\n"));
 	printf(_("  --no-tablespaces             do not dump tablespace assignments\n"));
 	printf(_("  --no-unlogged-table-data     do not dump unlogged table data\n"));
+	printf(_("  --on-conflict-do-nothing     dump data as INSERT commands with ON CONFLICT DO NOTHING \n"));
 	printf(_("  --quote-all-identifiers      quote all identifiers, even if not key words\n"));
 	printf(_("  --section=SECTION            dump named section (pre-data, data, or post-data)\n"));
 	printf(_("  --serializable-deferrable    wait until the dump can run without anomalies\n"));
@@ -2047,7 +2052,11 @@ dumpTableData_insert(Archive *fout, void *dcontext)
 						break;
 				}
 			}
-			archputs(");\n", fout);
+
+			if (!dopt->do_nothing)
+				archputs(");\n", fout);
+			else
+				archputs(") ON CONFLICT DO NOTHING;\n", fout);
 		}
 
 		if (PQntuples(res) <= 0)
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 54db0cd174..d730b477b2 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -78,6 +78,7 @@ static int	no_unlogged_table_data = 0;
 static int	no_role_passwords = 0;
 static int	server_version;
 static int	load_via_partition_root = 0;
+static int	on_conflict_do_nothing = 0;
 
 static char role_catalog[10];
 #define PG_AUTHID "pg_authid"
@@ -137,6 +138,7 @@ main(int argc, char *argv[])
 		{"no-subscriptions", no_argument, &no_subscriptions, 1},
 		{"no-sync", no_argument, NULL, 4},
 		{"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1},
+		{"on-conflict-do-nothing", no_argument, &on_conflict_do_nothing, 1},
 
 		{NULL, 0, NULL, 0}
 	};
@@ -367,6 +369,12 @@ main(int argc, char *argv[])
 		exit_nicely(1);
 	}
 
+	if (on_conflict_do_nothing && !(inserts || column_inserts))
+	{
+		fprintf(stderr, _("%s: option --on-conflict-do-nothing requires option --inserts or --column-inserts\n"),
+				progname);
+		exit_nicely(1);
+	}
 	/*
 	 * If password values are not required in the dump, switch to using
 	 * pg_roles which is equally useful, just more likely to have unrestricted
@@ -390,6 +398,8 @@ main(int argc, char *argv[])
 		appendPQExpBufferStr(pgdumpopts, " --inserts");
 	if (no_tablespaces)
 		appendPQExpBufferStr(pgdumpopts, " --no-tablespaces");
+	if (on_conflict_do_nothing)
+		appendPQExpBufferStr(pgdumpopts, " --on-conflict-do-nothing");
 	if (quote_all_identifiers)
 		appendPQExpBufferStr(pgdumpopts, " --quote-all-identifiers");
 	if (load_via_partition_root)
@@ -621,6 +631,7 @@ help(void)
 	printf(_("  --no-sync                    do not wait for changes to be written safely to disk\n"));
 	printf(_("  --no-tablespaces             do not dump tablespace assignments\n"));
 	printf(_("  --no-unlogged-table-data     do not dump unlogged table data\n"));
+	printf(_("  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands\n"));
 	printf(_("  --quote-all-identifiers      quote all identifiers, even if not key words\n"));
 	printf(_("  --use-set-session-authorization\n"
 			 "                               use SET SESSION AUTHORIZATION commands instead of\n"
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index 8be5770ba4..8eaf556df6 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 => 68;
+use Test::More tests => 72;
 
 my $tempdir       = TestLib::tempdir;
 my $tempdir_short = TestLib::tempdir_short;
@@ -122,6 +122,11 @@ command_fails_like(
 	qr/\Qpg_restore: unrecognized archive format "garbage";\E/,
 	'pg_dump: unrecognized archive format');
 
+command_fails_like(
+	[ 'pg_dump', '--on-conflict-do-nothing' ],
+	qr/\Qpg_dump: option --on-conflict-do-nothing requires option --inserts or --column-inserts\E/,
+	'pg_dump: option --on-conflict-do-nothing requires option --inserts or --column-inserts');
+
 # pg_dumpall command-line argument checks
 command_fails_like(
 	[ 'pg_dumpall', '-g', '-r' ],
@@ -145,3 +150,9 @@ command_fails_like(
 	[ 'pg_dumpall', '--if-exists' ],
 	qr/\Qpg_dumpall: option --if-exists requires option -c\/--clean\E/,
 	'pg_dumpall: option --if-exists requires option -c/--clean');
+
+command_fails_like(
+	[ 'pg_dumpall', '--on-conflict-do-nothing' ],
+	qr/\Qpg_dumpall: option --on-conflict-do-nothing requires option --inserts or --column-inserts\E/,
+	'pg_dumpall: option --on-conflict-do-nothing requires option --inserts or --column-inserts'
+);
-- 
2.17.0