multi_values_inserts_dum_v1.patch

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 8286b2dddd..b9c0521e71 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -819,6 +819,17 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--multiple-row-inserts</option></term>
+      <listitem>
+       <para>
+        Dump data as multi values <command>INSERT</command> commands (rather than <command>COPY</command>). 
+        This will make the dump file smaller than <option>--inserts</option> and its faster to reload but lack 
+        per row data lost on error while reloading.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-comments</option></term>
       <listitem>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 94d76c30db..4a7533d052 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -357,6 +357,17 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--multiple-row-inserts</option></term>
+      <listitem>
+       <para>
+        Dump data as multi values <command>INSERT</command> commands (rather than <command>COPY</command>). 
+        This will make the dump file smaller than <option>--inserts</option> and its faster to reload but lack 
+        per row data lost on error while reloading.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-comments</option></term>
       <listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 42cf441aaf..5905d5ece9 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -87,6 +87,7 @@ typedef struct _restoreOptions
 	int			verbose;
 	int			aclsSkip;
 	const char *lockWaitTimeout;
+	int			dump_multi__row_insert;
 	int			include_everything;
 
 	int			tocSummary;
@@ -173,6 +174,7 @@ typedef struct _dumpOptions
 
 	int			sequence_data;	/* dump sequence data even in schema-only mode */
 	int			do_nothing;
+	int			dump_multi__row_insert;
 } DumpOptions;
 
 /*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9baf7b2fde..5a6053cc37 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -362,6 +362,7 @@ main(int argc, char **argv)
 		{"if-exists", no_argument, &dopt.if_exists, 1},
 		{"inserts", no_argument, &dopt.dump_inserts, 1},
 		{"lock-wait-timeout", required_argument, NULL, 2},
+		{"multiple-row-inserts", no_argument, &dopt.dump_multi__row_insert, 1},
 		{"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1},
 		{"quote-all-identifiers", no_argument, &quote_all_identifiers, 1},
 		{"load-via-partition-root", no_argument, &dopt.load_via_partition_root, 1},
@@ -586,9 +587,8 @@ main(int argc, char **argv)
 		exit_nicely(1);
 	}
 
-	/* --column-inserts implies --inserts */
-	if (dopt.column_inserts)
-		dopt.dump_inserts = 1;
+	if (dopt.column_inserts && !(dopt.dump_inserts || dopt.dump_multi__row_insert))
+		exit_horribly(NULL, "option --column-inserts requires option --inserts  or --multiple-row-inserts\n");
 
 	/*
 	 * Binary upgrade mode implies dumping sequence data even in schema-only
@@ -612,7 +612,14 @@ main(int argc, char **argv)
 
 	if (dopt.dump_inserts && dopt.oids)
 	{
-		write_msg(NULL, "options --inserts/--column-inserts and -o/--oids cannot be used together\n");
+		write_msg(NULL, "options --inserts and -o/--oids cannot be used together\n");
+		write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
+		exit_nicely(1);
+	}
+
+	if (dopt.dump_multi__row_insert && dopt.oids)
+	{
+		write_msg(NULL, "options --multiple-row-inserts and -o/--oids cannot be used together\n");
 		write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
 		exit_nicely(1);
 	}
@@ -620,8 +627,8 @@ 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");
+	if (dopt.do_nothing && !(dopt.dump_inserts || dopt.dump_multi__row_insert))
+		exit_horribly(NULL, "option --on-conflict-do-nothing requires option --inserts or --multiple-row-inserts\n");
 
 	/* Identify archive format to emit */
 	archiveFormat = parseArchiveFormat(format, &archiveMode);
@@ -899,6 +906,7 @@ main(int argc, char **argv)
 	ropt->no_security_labels = dopt.no_security_labels;
 	ropt->no_subscriptions = dopt.no_subscriptions;
 	ropt->lockWaitTimeout = dopt.lockWaitTimeout;
+	ropt->dump_multi__row_insert= dopt.dump_multi__row_insert;
 	ropt->include_everything = dopt.include_everything;
 	ropt->enable_row_security = dopt.enable_row_security;
 	ropt->sequence_data = dopt.sequence_data;
@@ -986,6 +994,7 @@ help(const char *progname)
 	printf(_("  --if-exists                  use IF EXISTS when dropping objects\n"));
 	printf(_("  --inserts                    dump data as INSERT commands, rather than COPY\n"));
 	printf(_("  --load-via-partition-root    load partitions via the root table\n"));
+	printf(_("  --multiple-row-inserts                    dump data as multirow values  INSERT commands, rather than COPY\n"));
 	printf(_("  --no-comments                do not dump comments\n"));
 	printf(_("  --no-publications            do not dump publications\n"));
 	printf(_("  --no-security-labels         do not dump security label assignments\n"));
@@ -2078,6 +2087,191 @@ dumpTableData_insert(Archive *fout, void *dcontext)
 	return 1;
 }
 
+/*
+ * Dump table data using multi values INSERT commands.
+ */
+static int
+dumpTableData_multi_row_insert(Archive *fout, void *dcontext)
+{
+	TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
+	TableInfo  *tbinfo = tdinfo->tdtable;
+	DumpOptions *dopt = fout->dopt;
+	PQExpBuffer q = createPQExpBuffer();
+	PQExpBuffer insertStmt = NULL;
+	PGresult   *res;
+	int			tuple;
+	int			nfields;
+	int			field;
+	int			ntuple;
+
+
+	appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
+					  "SELECT * FROM ONLY %s",
+					  fmtQualifiedDumpable(tbinfo));
+	if (tdinfo->filtercond)
+		appendPQExpBuffer(q, " %s", tdinfo->filtercond);
+
+	ExecuteSqlStatement(fout, q->data);
+
+	while (1)
+	{
+		res = ExecuteSqlQuery(fout, "FETCH 100 FROM _pg_dump_cursor",
+							  PGRES_TUPLES_OK);
+		nfields = PQnfields(res);
+		ntuple = PQntuples(res);
+
+			if (insertStmt == NULL)
+			{
+				TableInfo  *targettab;
+
+				insertStmt = createPQExpBuffer();
+
+				/*
+				 * When load-via-partition-root is set, get the root table
+				 * name for the partition table, so that we can reload data
+				 * through the root table.
+				 */
+				if (dopt->load_via_partition_root && tbinfo->ispartition)
+					targettab = getRootTableInfo(tbinfo);
+				else
+					targettab = tbinfo;
+
+				appendPQExpBuffer(insertStmt, "INSERT INTO %s ",
+								  fmtQualifiedDumpable(targettab));
+
+				/* corner case for zero-column table */
+				if (nfields == 0)
+				{
+					appendPQExpBufferStr(insertStmt, "DEFAULT VALUES;\n");
+				}
+				else
+				{
+					/* append the list of column names if required */
+					if (dopt->column_inserts)
+					{
+						appendPQExpBufferChar(insertStmt, '(');
+						for (field = 0; field < nfields; field++)
+						{
+							if (field > 0)
+								appendPQExpBufferStr(insertStmt, ", ");
+							appendPQExpBufferStr(insertStmt,
+												 fmtId(PQfname(res, field)));
+						}
+						appendPQExpBufferStr(insertStmt, ") ");
+					}
+
+					if (tbinfo->needs_override)
+						appendPQExpBufferStr(insertStmt, "OVERRIDING SYSTEM VALUE ");
+
+					appendPQExpBufferStr(insertStmt, "VALUES ");
+				}
+			}
+
+			archputs(insertStmt->data, fout)
+				;
+			for (tuple = 0; tuple < ntuple ; tuple++)
+			{
+
+			/* if it is zero-column table then we're done */
+			if (nfields == 0)
+				continue;
+			if (tuple == 0)
+				archputs("(", fout);
+			else
+				archputs(", (", fout);
+
+			for (field = 0; field < nfields; field++)
+			{
+				if (field > 0)
+					archputs(", ", fout);
+				if (PQgetisnull(res, tuple, field))
+				{
+					archputs("NULL", fout);
+					continue;
+				}
+
+				/* XXX This code is partially duplicated in ruleutils.c */
+				switch (PQftype(res, field))
+				{
+					case INT2OID:
+					case INT4OID:
+					case INT8OID:
+					case OIDOID:
+					case FLOAT4OID:
+					case FLOAT8OID:
+					case NUMERICOID:
+						{
+							/*
+							 * These types are printed without quotes unless
+							 * they contain values that aren't accepted by the
+							 * scanner unquoted (e.g., 'NaN').  Note that
+							 * strtod() and friends might accept NaN, so we
+							 * can't use that to test.
+							 *
+							 * In reality we only need to defend against
+							 * infinity and NaN, so we need not get too crazy
+							 * about pattern matching here.
+							 */
+							const char *s = PQgetvalue(res, tuple, field);
+
+							if (strspn(s, "0123456789 +-eE.") == strlen(s))
+								archputs(s, fout);
+							else
+								archprintf(fout, "'%s'", s);
+						}
+						break;
+
+					case BITOID:
+					case VARBITOID:
+						archprintf(fout, "B'%s'",
+								   PQgetvalue(res, tuple, field));
+						break;
+
+					case BOOLOID:
+						if (strcmp(PQgetvalue(res, tuple, field), "t") == 0)
+							archputs("true", fout);
+						else
+							archputs("false", fout);
+						break;
+
+					default:
+						/* All other types are printed as string literals. */
+						resetPQExpBuffer(q);
+						appendStringLiteralAH(q,
+											  PQgetvalue(res, tuple, field),
+											  fout);
+						archputs(q->data, fout);
+						break;
+				}
+			}
+			if (tuple < ntuple-1)
+				archputs(")\n", fout);
+
+		}
+		if (!dopt->do_nothing)
+			archputs(");\n", fout);
+		else
+			archputs(") ON CONFLICT DO NOTHING;\n", fout);
+
+		if (PQntuples(res) <= 0)
+		{
+			PQclear(res);
+			break;
+		}
+		PQclear(res);
+	}
+
+	archputs("\n\n", fout);
+
+	ExecuteSqlStatement(fout, "CLOSE _pg_dump_cursor");
+
+	destroyPQExpBuffer(q);
+	if (insertStmt != NULL)
+		destroyPQExpBuffer(insertStmt);
+
+	return 1;
+}
+
 /*
  * getRootTableInfo:
  *     get the root TableInfo for the given partition table.
@@ -2117,7 +2311,7 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
 	char	   *copyStmt;
 	const char *copyFrom;
 
-	if (!dopt->dump_inserts)
+	if (!dopt->dump_inserts &&  !dopt->dump_multi__row_insert)
 	{
 		/* Dump/restore using COPY */
 		dumpFn = dumpTableData_copy;
@@ -2145,6 +2339,12 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
 						  (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
 		copyStmt = copyBuf->data;
 	}
+	else if (dopt->dump_multi__row_insert)
+	{
+		/* Restore using multi values INSERT statment*/
+		dumpFn = dumpTableData_multi_row_insert;
+		copyStmt = NULL;
+	}
 	else
 	{
 		/* Restore using INSERT */
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index eb29d318a4..b8897f1f8b 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -68,6 +68,7 @@ static int	disable_dollar_quoting = 0;
 static int	disable_triggers = 0;
 static int	if_exists = 0;
 static int	inserts = 0;
+static int	multi_values_inserts = 0;
 static int	no_tablespaces = 0;
 static int	use_setsessauth = 0;
 static int	no_comments = 0;
@@ -126,6 +127,7 @@ main(int argc, char *argv[])
 		{"if-exists", no_argument, &if_exists, 1},
 		{"inserts", no_argument, &inserts, 1},
 		{"lock-wait-timeout", required_argument, NULL, 2},
+		{"multiple-row-inserts", no_argument, &multi_values_inserts, 1},
 		{"no-tablespaces", no_argument, &no_tablespaces, 1},
 		{"quote-all-identifiers", no_argument, &quote_all_identifiers, 1},
 		{"load-via-partition-root", no_argument, &load_via_partition_root, 1},
@@ -390,6 +392,8 @@ main(int argc, char *argv[])
 		appendPQExpBufferStr(pgdumpopts, " --disable-triggers");
 	if (inserts)
 		appendPQExpBufferStr(pgdumpopts, " --inserts");
+	if (multi_values_inserts)
+		appendPQExpBufferStr(pgdumpopts, " --multiple-row-inserts");
 	if (no_tablespaces)
 		appendPQExpBufferStr(pgdumpopts, " --no-tablespaces");
 	if (quote_all_identifiers)
@@ -616,6 +620,7 @@ help(void)
 	printf(_("  --disable-triggers           disable triggers during data-only restore\n"));
 	printf(_("  --if-exists                  use IF EXISTS when dropping objects\n"));
 	printf(_("  --inserts                    dump data as INSERT commands, rather than COPY\n"));
+	printf(_("  --multiple-row-inserts                    dump data as multirow values  INSERT commands, rather than COPY\n"));
 	printf(_("  --load-via-partition-root    load partitions via the root table\n"));
 	printf(_("  --no-comments                do not dump comments\n"));
 	printf(_("  --no-publications            do not dump publications\n"));
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index 17edf444b2..f8d4eb202a 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 => 70;
+use Test::More tests => 74;
 
 my $tempdir       = TestLib::tempdir;
 my $tempdir_short = TestLib::tempdir_short;
@@ -73,8 +73,8 @@ command_fails_like(
 
 command_fails_like(
 	[ 'pg_dump', '--inserts', '-o' ],
-	qr/\Qpg_dump: options --inserts\/--column-inserts and -o\/--oids cannot be used together\E/,
-	'pg_dump: options --inserts/--column-inserts and -o/--oids cannot be used together'
+	qr/\Qpg_dump: options --inserts and -o\/--oids cannot be used together\E/,
+	'pg_dump: options --inserts and -o/--oids cannot be used together'
 );
 
 command_fails_like(
@@ -124,8 +124,19 @@ command_fails_like(
 
 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');
+	qr/\Qpg_dump: option --on-conflict-do-nothing requires option --inserts or --multiple-row-inserts\E/,
+	'pg_dump: option --on-conflict-do-nothing requires option --inserts or --multiple-row-inserts');
+
+command_fails_like(
+	[ 'pg_dump', '--multiple-row-inserts', '-o' ],
+	qr/\Qpg_dump: options --multiple-row-inserts and -o\/--oids cannot be used together\E/,
+	'pg_dump: options --multiple-row-inserts and -o/--oids cannot be used together'
+);
+
+command_fails_like(
+	[ 'pg_dump', '--column-inserts' ],
+	qr/\Qpg_dump: option --column-inserts requires option --inserts  or --multiple-row-inserts\E/,
+	'pg_dump: option --column-inserts requires option --inserts  or --multiple-row-inserts');
 
 # pg_dumpall command-line argument checks
 command_fails_like(