Hello,
According to the documentation –inserts option is mainly useful for making
dumps that can be loaded into non-PostgreSQL databases and to reduce the
amount of rows that might lost during error in reloading but multi values
insert command are equally portable and compact and also faster to reload
than single row statement. I think it deserve an option of its own
The patch attached add additional option for multi values insert statement
with a default values of 100 row per statement so the row lose during error
is at most 100 rather than entire table.
Comments?
Regards
Surafel
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, "e_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, "e_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(
