hi,
On Sun, Nov 4, 2018 at 1:18 PM Fabien COELHO <coe...@cri.ensmp.fr> wrote:
> Patch does not seem to apply anymore, could you rebase?
>
>
The attached patch is a rebased version and work by ‘inserts=100’ as
Stephen suggest
regards
Surafel
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 790e81c32c..6cc15de2d8 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -789,6 +789,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--inserts=100</option></term>
+ <listitem>
+ <para>
+ Dump data as 100 values <command>INSERT</command> commands (rather than <command>COPY</command>).
+ This will make the dump file smaller than <option>--inserts</option> and faster to reload but lack
+ single row data lost on error while reloading rather entire 100 rows data lost.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--load-via-partition-root</option></term>
<listitem>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index c51a130f43..c08cc80732 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -326,6 +326,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--inserts=100</option></term>
+ <listitem>
+ <para>
+ Dump data as 100 values <command>INSERT</command> commands (rather than <command>COPY</command>).
+ This will make the dump file smaller than <option>--inserts</option> and faster to reload but lack
+ single row data lost on error while reloading rather entire 100 rows data lost.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--load-via-partition-root</option></term>
<listitem>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index ba798213be..2fd48cf2f2 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -72,6 +72,7 @@ typedef struct _restoreOptions
int dropSchema;
int disable_dollar_quoting;
int dump_inserts;
+ int dump_inserts_hundred;
int column_inserts;
int if_exists;
int no_comments; /* Skip comments */
@@ -145,6 +146,7 @@ typedef struct _dumpOptions
/* flags for various command-line long options */
int disable_dollar_quoting;
int dump_inserts;
+ int dump_inserts_hundred;
int column_inserts;
int if_exists;
int no_comments;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c8d01ed4a4..6df1fc2409 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -359,7 +359,7 @@ main(int argc, char **argv)
{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
{"exclude-table-data", required_argument, NULL, 4},
{"if-exists", no_argument, &dopt.if_exists, 1},
- {"inserts", no_argument, &dopt.dump_inserts, 1},
+ {"inserts", optional_argument, NULL, 8},
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1},
{"quote-all-identifiers", no_argument, "e_all_identifiers, 1},
@@ -562,6 +562,21 @@ main(int argc, char **argv)
dosync = false;
break;
+ case 8: /* inserts option */
+ if (optarg)
+ {
+ if (atoi(optarg) != 100)
+ {
+ write_msg(NULL, "insert values must be 100\n");
+ exit_nicely(1);
+ }
+ dopt.dump_inserts_hundred = 1;
+
+ }
+ else
+ dopt.dump_inserts = 1;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
@@ -609,9 +624,9 @@ main(int argc, char **argv)
exit_nicely(1);
}
- if (dopt.dump_inserts && dopt.oids)
+ if ((dopt.dump_inserts || dopt.dump_inserts_hundred) && dopt.oids)
{
- write_msg(NULL, "options --inserts/--column-inserts and -o/--oids cannot be used together\n");
+ write_msg(NULL, "options --inserts --column-inserts --inserts=100 and -o/--oids cannot be used together\n");
write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
exit_nicely(1);
}
@@ -619,8 +634,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");
+ if (dopt.do_nothing && !(dopt.dump_inserts || dopt.column_inserts ||
+ dopt.dump_inserts_hundred))
+ exit_horribly(NULL, "option --on-conflict-do-nothing requires option --inserts , --column-inserts or --inserts=100\n");
/* Identify archive format to emit */
archiveFormat = parseArchiveFormat(format, &archiveMode);
@@ -889,6 +905,7 @@ main(int argc, char **argv)
ropt->use_setsessauth = dopt.use_setsessauth;
ropt->disable_dollar_quoting = dopt.disable_dollar_quoting;
ropt->dump_inserts = dopt.dump_inserts;
+ ropt->dump_inserts_hundred = dopt.dump_inserts_hundred;
ropt->no_comments = dopt.no_comments;
ropt->no_publications = dopt.no_publications;
ropt->no_security_labels = dopt.no_security_labels;
@@ -980,6 +997,7 @@ help(const char *progname)
printf(_(" --exclude-table-data=TABLE do NOT dump data for the named table(s)\n"));
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
+ printf(_(" --inserts=100 dump data as 100 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"));
@@ -2073,6 +2091,191 @@ dumpTableData_insert(Archive *fout, void *dcontext)
return 1;
}
+/*
+ * Dump table data using hundred values INSERT commands.
+ */
+static int
+dumpTableData_insert_hundred(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;
+ int ltuple;
+
+ 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);
+ ltuple = ntuple-1;
+
+ 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 < ltuple)
+ 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.
@@ -2112,7 +2315,7 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
char *copyStmt;
const char *copyFrom;
- if (!dopt->dump_inserts)
+ if (!dopt->dump_inserts && !dopt->dump_inserts_hundred)
{
/* Dump/restore using COPY */
dumpFn = dumpTableData_copy;
@@ -2140,6 +2343,12 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
copyStmt = copyBuf->data;
}
+ else if (dopt->dump_inserts_hundred)
+ {
+ /* Restore using hundred values INSERT */
+ dumpFn = dumpTableData_insert_hundred;
+ 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 5176626476..082d453da3 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 inserts_hundred = 0;
static int no_tablespaces = 0;
static int use_setsessauth = 0;
static int no_comments = 0;
@@ -125,6 +126,7 @@ main(int argc, char *argv[])
{"disable-triggers", no_argument, &disable_triggers, 1},
{"if-exists", no_argument, &if_exists, 1},
{"inserts", no_argument, &inserts, 1},
+ {"inserts-hundred", no_argument, &inserts_hundred, 1},
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &no_tablespaces, 1},
{"quote-all-identifiers", no_argument, "e_all_identifiers, 1},
@@ -390,6 +392,8 @@ main(int argc, char *argv[])
appendPQExpBufferStr(pgdumpopts, " --disable-triggers");
if (inserts)
appendPQExpBufferStr(pgdumpopts, " --inserts");
+ if (inserts_hundred)
+ appendPQExpBufferStr(pgdumpopts, " --inserts=100");
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(_(" --inserts=100 dump data as 100 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..f409a1030a 100644
--- a/src/bin/pg_dump/t/001_basic.pl
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -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 --column-inserts --inserts=100 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,9 @@ 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 , --column-inserts or --inserts=100\E/,
+ 'pg_dump: option --on-conflict-do-nothing requires option --inserts , --column-inserts or --inserts=100');
+
# pg_dumpall command-line argument checks
command_fails_like(
