Attached is a patch the more clearly handles autocommit in pg_dump. I
had already fixed pg_dump for doing autocommit while dumping, but didn't
handle setting autocommit on restore.
I focused on the initial script file startup, every \\connect,
pg_restore, and pg_dumpall. I think I got them all.
New pg_dump output is:
--
-- PostgreSQL database dump
--
SET autocommit TO 'on';
\connect - postgres
SET autocommit TO 'on';
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I just checked and pg_dump -d _doesn't_ place the INSERT's in a
> > transsaction. Seems it should,
>
> I think this is a bad idea. If one were after speed, one would have
> used the COPY format in the first place. If one uses INSERTs, there
> may be a reason for it --- like, say, wanting each row insertion to
> succeed or fail independently. Put a begin/end around it, and you
> lose that.
>
> > and perhaps add a:
> > SET autocommit TO 'on'
> > as well.
>
> This is probably a good idea, since pg_dump scripts effectively assume
> that anyway.
>
> > Of course, that SET would fail when restoring to prior
> > releases,
>
> Irrelevant; current pg_dump scripts already issue a SET that pre-7.3
> servers won't recognize (search_path). A failed SET is harmless anyway,
> or should be. (What we really need is for someone to fix pg_restore to
> not abort on SQL errors...)
>
> regards, tom lane
>
--
Bruce Momjian | http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.67
diff -c -c -r1.67 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c 1 Feb 2003 22:06:59 -0000 1.67
--- src/bin/pg_dump/pg_backup_archiver.c 14 Feb 2003 19:39:08 -0000
***************
*** 206,212 ****
sav = SetOutput(AH, ropt->filename, ropt->compression);
ahprintf(AH, "--\n-- PostgreSQL database dump\n--\n\n");
!
/*
* Drop the items at the start, in reverse order
*/
--- 206,213 ----
sav = SetOutput(AH, ropt->filename, ropt->compression);
ahprintf(AH, "--\n-- PostgreSQL database dump\n--\n\n");
! ahprintf(AH, "SET autocommit TO 'on';\n\n");
!
/*
* Drop the items at the start, in reverse order
*/
***************
*** 2109,2115 ****
dbname ? fmtId(dbname) : "-");
appendPQExpBuffer(qry, " %s\n\n",
fmtId(user));
!
ahprintf(AH, qry->data);
destroyPQExpBuffer(qry);
--- 2110,2117 ----
dbname ? fmtId(dbname) : "-");
appendPQExpBuffer(qry, " %s\n\n",
fmtId(user));
! appendPQExpBuffer(qry, "SET autocommit TO 'on';\n\n");
!
ahprintf(AH, qry->data);
destroyPQExpBuffer(qry);
Index: src/bin/pg_dump/pg_backup_db.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_db.c,v
retrieving revision 1.45
diff -c -c -r1.45 pg_backup_db.c
*** src/bin/pg_dump/pg_backup_db.c 13 Feb 2003 04:54:15 -0000 1.45
--- src/bin/pg_dump/pg_backup_db.c 14 Feb 2003 19:39:09 -0000
***************
*** 213,218 ****
--- 213,233 ----
if (password)
free(password);
+ /* check for version mismatch */
+ _check_database_version(AH, true);
+
+ /* Turn autocommit on */
+ if (AH->public.remoteVersion >= 70300)
+ {
+ PGresult *res;
+
+ res = PQexec(AH->connection, "SET autocommit TO 'on'");
+ if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
+ die_horribly(AH, NULL, "SET autocommit TO 'on' failed: %s",
+ PQerrorMessage(AH->connection));
+ PQclear(res);
+ }
+
PQsetNoticeProcessor(newConn, notice_processor, NULL);
return newConn;
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.13
diff -c -c -r1.13 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 16 Jan 2003 15:27:59 -0000 1.13
--- src/bin/pg_dump/pg_dumpall.c 14 Feb 2003 19:39:09 -0000
***************
*** 190,195 ****
--- 190,196 ----
printf("-- PostgreSQL database cluster dump\n");
printf("--\n\n");
printf("\\connect \"template1\"\n\n");
+ printf("SET autocommit TO 'on';\n\n");
dumpUsers(conn);
dumpGroups(conn);
***************
*** 552,557 ****
--- 553,559 ----
fprintf(stderr, _("%s: dumping database \"%s\"...\n"),
progname, dbname);
printf("\\connect %s\n", fmtId(dbname));
+ printf("SET autocommit TO 'on';\n\n");
ret = runPgDump(dbname);
if (ret != 0)
{
***************
*** 676,681 ****
--- 678,691 ----
}
}
PQclear(res);
+
+ if (server_version >= 70300)
+ {
+ PGresult *res;
+
+ res = executeQuery(conn, "SET autocommit TO 'on';SELECT 1;");
+ PQclear(res);
+ }
return conn;
}
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
