This patch makes pg_dump wrap insert dumps (-d, -D) in a transaction to
speed the restore process. It also adds an option to disable these
transactions for doing things like copying only newly inserted values from
one database to another (assuming old ones will fail on an unique
constraint).
Kris Jurka
? src/bin/pg_dump/.deps
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.82
diff -c -r1.82 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 17 Mar 2006 16:02:47 -0000 1.82
--- doc/src/sgml/ref/pg_dump.sgml 12 Apr 2006 22:54:04 -0000
***************
*** 168,174 ****
<listitem>
<para>
Dump data as <command>INSERT</command> commands (rather
! than <command>COPY</command>). This will make restoration very slow;
it is mainly useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases. Note that
the restore may fail altogether if you have rearranged column order.
--- 168,174 ----
<listitem>
<para>
Dump data as <command>INSERT</command> commands (rather
! than <command>COPY</command>). This will make restoration slower;
it is mainly useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases. Note that
the restore may fail altogether if you have rearranged column order.
***************
*** 187,193 ****
column names (<literal>INSERT INTO
<replaceable>table</replaceable>
(<replaceable>column</replaceable>, ...) VALUES
! ...</literal>). This will make restoration very slow; it is mainly
useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases.
</para>
--- 187,193 ----
column names (<literal>INSERT INTO
<replaceable>table</replaceable>
(<replaceable>column</replaceable>, ...) VALUES
! ...</literal>). This will make restoration slower; it is mainly
useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases.
</para>
***************
*** 490,495 ****
--- 490,508 ----
</varlistentry>
<varlistentry>
+ <term><option>-X disable-insert-transactions</></term>
+ <term><option>--disable-insert-transactions</></term>
+ <listitem>
+ <para>
+ Prevent the <command>INSERT</command> statements for
+ <option>-d</option> and <option>-D</option> from being wrapped in a
+ transaction. This is useful for compatibility with other
+ databases or for partial restores to work.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-Z <replaceable
class="parameter">0..9</replaceable></option></term>
<term><option>--compress=<replaceable
class="parameter">0..9</replaceable></option></term>
<listitem>
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.55
diff -c -r1.55 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml 1 Nov 2005 21:09:50 -0000 1.55
--- doc/src/sgml/ref/pg_dumpall.sgml 12 Apr 2006 22:54:04 -0000
***************
*** 103,109 ****
<listitem>
<para>
Dump data as <command>INSERT</command> commands (rather
! than <command>COPY</command>). This will make restoration very slow;
it is mainly useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases. Note that
the restore may fail altogether if you have rearranged column order.
--- 103,109 ----
<listitem>
<para>
Dump data as <command>INSERT</command> commands (rather
! than <command>COPY</command>). This will make restoration slower;
it is mainly useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases. Note that
the restore may fail altogether if you have rearranged column order.
***************
*** 122,128 ****
column names (<literal>INSERT INTO
<replaceable>table</replaceable>
(<replaceable>column</replaceable>, ...) VALUES
! ...</literal>). This will make restoration very slow; it is mainly
useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases.
</para>
--- 122,128 ----
column names (<literal>INSERT INTO
<replaceable>table</replaceable>
(<replaceable>column</replaceable>, ...) VALUES
! ...</literal>). This will make restoration slower; it is mainly
useful for making dumps that can be loaded into
non-<productname>PostgreSQL</productname> databases.
</para>
***************
*** 287,292 ****
--- 287,305 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-X disable-insert-transactions</></term>
+ <term><option>--disable-insert-transactions</></term>
+ <listitem>
+ <para>
+ Prevent the <command>INSERT</command> statements for
+ <option>-d</option> and <option>-D</option> from being wrapped in a
+ transaction. This is useful for compatibility with other
+ databases or for partial restores to work.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.433
diff -c -r1.433 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 5 Mar 2006 15:58:50 -0000 1.433
--- src/bin/pg_dump/pg_dump.c 12 Apr 2006 22:54:04 -0000
***************
*** 114,119 ****
--- 114,122 ----
/* flag to turn on/off dollar quoting */
static int disable_dollar_quoting = 0;
+ /* flag to turn on/off transactions around insert dumps */
+ static int disable_insert_transactions = 0;
+
static void help(const char *progname);
static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
***************
*** 249,254 ****
--- 252,258 ----
{"disable-dollar-quoting", no_argument,
&disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
{"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
+ {"disable-insert-transactions", no_argument,
&disable_insert_transactions, 1},
{NULL, 0, NULL, 0}
};
***************
*** 403,408 ****
--- 407,414 ----
disable_triggers = 1;
else if (strcmp(optarg,
"use-set-session-authorization") == 0)
use_setsessauth = 1;
+ else if (strcmp(optarg,
"disable-insert-transactions") == 0)
+ disable_insert_transactions = 1;
else
{
fprintf(stderr,
***************
*** 462,467 ****
--- 468,480 ----
exit(1);
}
+ if (dumpInserts == false && disable_insert_transactions)
+ {
+ write_msg(NULL, "option --disable-insert-transactions cannot be
used without\n");
+ write_msg(NULL, "an insert option (-d, -D).\n");
+ exit(1);
+ }
+
/* open the output file */
switch (format[0])
{
***************
*** 701,706 ****
--- 714,722 ----
printf(_(" -X use-set-session-authorization,
--use-set-session-authorization\n"
" use SESSION AUTHORIZATION
commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" -X disable-insert-transactions,
--disable-insert-transactions\n"
+ " don't wrap inserts in
dumps created with\n"
+ " --inserts in a
transaction\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket
directory\n"));
***************
*** 996,1001 ****
--- 1012,1020 ----
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
+ if (!disable_insert_transactions)
+ archprintf(fout, "START TRANSACTION;\n");
+
do
{
PQclear(res);
***************
*** 1095,1100 ****
--- 1114,1121 ----
PQclear(res);
+ if (!disable_insert_transactions)
+ archprintf(fout, "COMMIT;\n");
archprintf(fout, "\n\n");
do_sql_command(g_conn, "CLOSE _pg_dump_cursor");
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.74
diff -c -r1.74 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 7 Apr 2006 21:26:29 -0000 1.74
--- src/bin/pg_dump/pg_dumpall.c 12 Apr 2006 22:54:04 -0000
***************
*** 71,76 ****
--- 71,77 ----
static bool ignoreVersion = false;
/* flags for -X long options */
+ static int disable_insert_transactions = 0;
static int disable_dollar_quoting = 0;
static int disable_triggers = 0;
static int use_setsessauth = 0;
***************
*** 118,123 ****
--- 119,125 ----
{"disable-dollar-quoting", no_argument,
&disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
{"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
+ {"disable-insert-transactions", no_argument,
&disable_insert_transactions, 1},
{NULL, 0, NULL, 0}
};
***************
*** 266,271 ****
--- 268,275 ----
appendPQExpBuffer(pgdumpopts, " -X
disable-triggers");
else if (strcmp(optarg,
"use-set-session-authorization") == 0)
/* no-op, still allowed for
compatibility */ ;
+ else if (strcmp(optarg,
"disable-insert-transactions") == 0)
+ appendPQExpBuffer(pgdumpopts, " -X
disable-insert-transactions");
else
{
fprintf(stderr,
***************
*** 292,297 ****
--- 296,303 ----
appendPQExpBuffer(pgdumpopts, " -X disable-triggers");
if (use_setsessauth)
appendPQExpBuffer(pgdumpopts, " -X
use-set-session-authorization");
+ if (disable_insert_transactions)
+ appendPQExpBuffer(pgdumpopts, " -X
disable-insert-transactions");
if (optind < argc)
{
***************
*** 383,388 ****
--- 389,397 ----
printf(_(" -X use-set-session-authorization,
--use-set-session-authorization\n"
" use SESSION AUTHORIZATION
commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" -X disable-insert-transactions,
--disable-insert-transactions\n"
+ " don't wrap inserts in
dumps created with\n"
+ " --inserts in a
transaction\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket
directory\n"));
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
