Re: pg_dump and inserts - Mailing list pgsql-hackers

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 <pgman@candle.pha.pa.us> 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
  pgman@candle.pha.pa.us               |  (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;
  }