Fixes for autocommit = off - Mailing list pgsql-patches

Here are some patches I applied that attempt to fix some of our
utilities for the 'autocommit = off' world.  I still need to do more
work.

--
  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: pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.57
diff -c -c -r1.57 pg_backup_archiver.c
*** pg_dump/pg_backup_archiver.c    4 Sep 2002 20:31:34 -0000    1.57
--- pg_dump/pg_backup_archiver.c    16 Oct 2002 05:44:52 -0000
***************
*** 280,286 ****
                  /*
                   * If we can output the data, then restore it.
                   */
!                 if (AH->PrintTocDataPtr !=NULL && (reqs & REQ_DATA) != 0)
                  {
  #ifndef HAVE_LIBZ
                      if (AH->compression != 0)
--- 280,286 ----
                  /*
                   * If we can output the data, then restore it.
                   */
!                 if (AH->PrintTocDataPtr != NULL && (reqs & REQ_DATA) != 0)
                  {
  #ifndef HAVE_LIBZ
                      if (AH->compression != 0)
***************
*** 304,314 ****
                           */
                          if (!AH->CustomOutPtr)
                              write_msg(modulename, "WARNING: skipping large object restoration\n");
-
                      }
                      else
                      {
-
                          _disableTriggersIfNecessary(AH, te, ropt);

                          /*
--- 304,312 ----
***************
*** 362,372 ****
          te = AH->toc->next;
          while (te != AH->toc)
          {
-
              /* Is it table data? */
              if (strcmp(te->desc, "TABLE DATA") == 0)
              {
-
                  ahlog(AH, 2, "checking whether we loaded %s\n", te->tag);

                  reqs = _tocEntryRequired(te, ropt);
--- 360,368 ----
Index: pg_dump/pg_backup_db.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_db.c,v
retrieving revision 1.41
diff -c -c -r1.41 pg_backup_db.c
*** pg_dump/pg_backup_db.c    7 Sep 2002 16:14:33 -0000    1.41
--- pg_dump/pg_backup_db.c    16 Oct 2002 05:44:54 -0000
***************
*** 61,71 ****

      myversion = _parse_version(AH, PG_VERSION);

!     res = PQexec(conn, "SELECT version();");
      if (!res ||
          PQresultStatus(res) != PGRES_TUPLES_OK ||
          PQntuples(res) != 1)
-
          die_horribly(AH, modulename, "could not get version from server: %s", PQerrorMessage(conn));

      remoteversion_str = PQgetvalue(res, 0, 0);
--- 61,75 ----

      myversion = _parse_version(AH, PG_VERSION);

!     /*
!      *    Autocommit could be off.  We turn it off later but we have to check
!      *    the database version first.
!      */
!
!     res = PQexec(conn, "BEGIN;SELECT version();");
      if (!res ||
          PQresultStatus(res) != PGRES_TUPLES_OK ||
          PQntuples(res) != 1)
          die_horribly(AH, modulename, "could not get version from server: %s", PQerrorMessage(conn));

      remoteversion_str = PQgetvalue(res, 0, 0);
***************
*** 73,78 ****
--- 77,88 ----

      PQclear(res);

+     res = PQexec(conn, "COMMIT;");
+     if (!res ||
+         PQresultStatus(res) != PGRES_COMMAND_OK)
+         die_horribly(AH, modulename, "could not get version from server: %s", PQerrorMessage(conn));
+     PQclear(res);
+
      AH->public.remoteVersion = remoteversion;

      if (myversion != remoteversion
***************
*** 276,281 ****
--- 286,303 ----

      /* check for version mismatch */
      _check_database_version(AH, ignoreVersion);
+
+     /* 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(AH->connection, notice_processor, NULL);

Index: pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.302
diff -c -c -r1.302 pg_dump.c
*** pg_dump/pg_dump.c    9 Oct 2002 16:20:25 -0000    1.302
--- pg_dump/pg_dump.c    16 Oct 2002 05:45:12 -0000
***************
*** 549,570 ****
      g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport, username, force_password, ignore_version);

      /*
!      * Start serializable transaction to dump consistent data
       */
      {
          PGresult   *res;

!         res = PQexec(g_conn, "begin");
          if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
              exit_horribly(g_fout, NULL, "BEGIN command failed: %s",
                            PQerrorMessage(g_conn));
-
          PQclear(res);
!         res = PQexec(g_conn, "set transaction isolation level serializable");
          if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
              exit_horribly(g_fout, NULL, "could not set transaction isolation level to serializable: %s",
                            PQerrorMessage(g_conn));
-
          PQclear(res);
      }

--- 549,569 ----
      g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport, username, force_password, ignore_version);

      /*
!      * Start serializable transaction to dump consistent data.
       */
      {
          PGresult   *res;

!         res = PQexec(g_conn, "BEGIN");
          if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
              exit_horribly(g_fout, NULL, "BEGIN command failed: %s",
                            PQerrorMessage(g_conn));
          PQclear(res);
!
!         res = PQexec(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
          if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
              exit_horribly(g_fout, NULL, "could not set transaction isolation level to serializable: %s",
                            PQerrorMessage(g_conn));
          PQclear(res);
      }

Index: scripts/clusterdb
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/clusterdb,v
retrieving revision 1.4
retrieving revision 1.6
diff -c -c -r1.4 -r1.6
*** scripts/clusterdb    27 Sep 2002 17:51:10 -0000    1.4
--- scripts/clusterdb    16 Oct 2002 03:44:28 -0000    1.6
***************
*** 11,17 ****
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/clusterdb,v 1.4 2002/09/27 17:51:10 momjian Exp $
  #
  #-------------------------------------------------------------------------

--- 11,17 ----
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/clusterdb,v 1.6 2002/10/16 03:44:28 momjian Exp $
  #
  #-------------------------------------------------------------------------

***************
*** 176,182 ****
          idx=`echo $tabs | cut -d: -f3`
          query="$query CLUSTER $idx ON $nspc.$tab;"
      done
!     ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "$query" -d $db
      if [ "$?" -ne 0 ]
      then
          echo "$CMDNAME: While clustering $db, the following failed: $query" 1>&2
--- 176,182 ----
          idx=`echo $tabs | cut -d: -f3`
          query="$query CLUSTER $idx ON $nspc.$tab;"
      done
!     ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "SET autocommit TO 'on';$query" -d $db
      if [ "$?" -ne 0 ]
      then
          echo "$CMDNAME: While clustering $db, the following failed: $query" 1>&2
Index: scripts/createdb
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/createdb,v
retrieving revision 1.25
retrieving revision 1.27
diff -c -c -r1.25 -r1.27
*** scripts/createdb    3 Sep 2002 21:45:43 -0000    1.25
--- scripts/createdb    16 Oct 2002 03:44:28 -0000    1.27
***************
*** 12,18 ****
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/createdb,v 1.25 2002/09/03 21:45:43 petere Exp $
  #
  #-------------------------------------------------------------------------

--- 12,18 ----
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/createdb,v 1.27 2002/10/16 03:44:28 momjian Exp $
  #
  #-------------------------------------------------------------------------

***************
*** 189,195 ****
  [ "$TEMPLATE" ] &&   withstring="$withstring TEMPLATE = \"$TEMPLATE\""
  [ "$withstring" ] && withstring=" WITH$withstring"

! ${PATHNAME}psql $PSQLOPT -d template1 -c "CREATE DATABASE \"$dbname\"$withstring"
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: database creation failed" 1>&2
      exit 1
--- 189,195 ----
  [ "$TEMPLATE" ] &&   withstring="$withstring TEMPLATE = \"$TEMPLATE\""
  [ "$withstring" ] && withstring=" WITH$withstring"

! ${PATHNAME}psql $PSQLOPT -d template1 -c "SET autocommit TO 'on';CREATE DATABASE \"$dbname\"$withstring"
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: database creation failed" 1>&2
      exit 1
***************
*** 200,206 ****

  dbcomment=`echo "$dbcomment" | sed "s/'/\\\\\'/g"`

! ${PATHNAME}psql $PSQLOPT -d "$dbname" -c "COMMENT ON DATABASE \"$dbname\" IS '$dbcomment'"
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: comment creation failed (database was created)" 1>&2
      exit 1
--- 200,206 ----

  dbcomment=`echo "$dbcomment" | sed "s/'/\\\\\'/g"`

! ${PATHNAME}psql $PSQLOPT -d "$dbname" -c "SET autocommit TO 'on';COMMENT ON DATABASE \"$dbname\" IS '$dbcomment'"
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: comment creation failed (database was created)" 1>&2
      exit 1
Index: scripts/createlang.sh
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/createlang.sh,v
retrieving revision 1.39
retrieving revision 1.41
diff -c -c -r1.39 -r1.41
*** scripts/createlang.sh    24 Sep 2002 23:14:25 -0000    1.39
--- scripts/createlang.sh    16 Oct 2002 03:44:28 -0000    1.41
***************
*** 7,13 ****
  # Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  # Portions Copyright (c) 1994, Regents of the University of California
  #
! # $Header: /cvsroot/pgsql-server/src/bin/scripts/createlang.sh,v 1.39 2002/09/24 23:14:25 tgl Exp $
  #
  #-------------------------------------------------------------------------

--- 7,13 ----
  # Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  # Portions Copyright (c) 1994, Regents of the University of California
  #
! # $Header: /cvsroot/pgsql-server/src/bin/scripts/createlang.sh,v 1.41 2002/10/16 03:44:28 momjian Exp $
  #
  #-------------------------------------------------------------------------

***************
*** 270,276 ****
  # Create the call handler and the language
  # ----------
  if [ "$handlerexists" = no ]; then
!     sqlcmd="CREATE FUNCTION \"$handler\" () RETURNS LANGUAGE_HANDLER AS '$PGLIB/${object}' LANGUAGE C;"
      if [ "$showsql" = yes ]; then
          echo "$sqlcmd"
      fi
--- 270,276 ----
  # Create the call handler and the language
  # ----------
  if [ "$handlerexists" = no ]; then
!     sqlcmd="SET autocommit TO 'on';CREATE FUNCTION \"$handler\" () RETURNS LANGUAGE_HANDLER AS '$PGLIB/${object}'
LANGUAGEC;" 
      if [ "$showsql" = yes ]; then
          echo "$sqlcmd"
      fi
***************
*** 281,287 ****
      fi
  fi

! sqlcmd="CREATE ${trusted}LANGUAGE \"$langname\" HANDLER \"$handler\";"
  if [ "$showsql" = yes ]; then
      echo "$sqlcmd"
  fi
--- 281,287 ----
      fi
  fi

! sqlcmd="SET autocommit TO 'on';CREATE ${trusted}LANGUAGE \"$langname\" HANDLER \"$handler\";"
  if [ "$showsql" = yes ]; then
      echo "$sqlcmd"
  fi
***************
*** 297,303 ****
  # seems best to disable public USAGE for an untrusted one.
  # ----------
  if test -z "$trusted"; then
!     sqlcmd="REVOKE ALL ON LANGUAGE \"$langname\" FROM PUBLIC;"
      if [ "$showsql" = yes ]; then
          echo "$sqlcmd"
      fi
--- 297,303 ----
  # seems best to disable public USAGE for an untrusted one.
  # ----------
  if test -z "$trusted"; then
!     sqlcmd="SET autocommit TO 'on';REVOKE ALL ON LANGUAGE \"$langname\" FROM PUBLIC;"
      if [ "$showsql" = yes ]; then
          echo "$sqlcmd"
      fi
Index: scripts/createuser
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/createuser,v
retrieving revision 1.26
retrieving revision 1.28
diff -c -c -r1.26 -r1.28
*** scripts/createuser    20 Jun 2002 20:29:42 -0000    1.26
--- scripts/createuser    16 Oct 2002 03:44:28 -0000    1.28
***************
*** 9,15 ****
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/createuser,v 1.26 2002/06/20 20:29:42 momjian Exp $
  #
  # Note - this should NOT be setuid.
  #
--- 9,15 ----
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/createuser,v 1.28 2002/10/16 03:44:28 momjian Exp $
  #
  # Note - this should NOT be setuid.
  #
***************
*** 253,259 ****
  [ "$CanAddUser" = t ] &&  QUERY="$QUERY CREATEUSER"
  [ "$CanAddUser" = f ] &&  QUERY="$QUERY NOCREATEUSER"

! ${PATHNAME}psql -c "$QUERY" -d template1 $PSQLOPT
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: creation of user \"$NewUser\" failed" 1>&2
      exit 1
--- 253,259 ----
  [ "$CanAddUser" = t ] &&  QUERY="$QUERY CREATEUSER"
  [ "$CanAddUser" = f ] &&  QUERY="$QUERY NOCREATEUSER"

! ${PATHNAME}psql -c "SET autocommit TO 'on';$QUERY" -d template1 $PSQLOPT
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: creation of user \"$NewUser\" failed" 1>&2
      exit 1
Index: scripts/dropdb
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/dropdb,v
retrieving revision 1.16
retrieving revision 1.18
diff -c -c -r1.16 -r1.18
*** scripts/dropdb    20 Jun 2002 20:29:42 -0000    1.16
--- scripts/dropdb    16 Oct 2002 03:44:28 -0000    1.18
***************
*** 11,17 ****
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/dropdb,v 1.16 2002/06/20 20:29:42 momjian Exp $
  #
  #-------------------------------------------------------------------------

--- 11,17 ----
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/dropdb,v 1.18 2002/10/16 03:44:28 momjian Exp $
  #
  #-------------------------------------------------------------------------

***************
*** 138,144 ****

  dbname=`echo "$dbname" | sed 's/\"/\\\"/g'`

! ${PATHNAME}psql $PSQLOPT -d template1 -c "DROP DATABASE \"$dbname\""
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: database removal failed" 1>&2
      exit 1
--- 138,144 ----

  dbname=`echo "$dbname" | sed 's/\"/\\\"/g'`

! ${PATHNAME}psql $PSQLOPT -d template1 -c "SET autocommit TO 'on';DROP DATABASE \"$dbname\""
  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: database removal failed" 1>&2
      exit 1
Index: scripts/droplang
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/droplang,v
retrieving revision 1.24
retrieving revision 1.26
diff -c -c -r1.24 -r1.26
*** scripts/droplang    10 Aug 2002 16:57:32 -0000    1.24
--- scripts/droplang    16 Oct 2002 03:44:28 -0000    1.26
***************
*** 7,13 ****
  # Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  # Portions Copyright (c) 1994, Regents of the University of California
  #
! # $Header: /cvsroot/pgsql-server/src/bin/scripts/droplang,v 1.24 2002/08/10 16:57:32 petere Exp $
  #
  #-------------------------------------------------------------------------

--- 7,13 ----
  # Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  # Portions Copyright (c) 1994, Regents of the University of California
  #
! # $Header: /cvsroot/pgsql-server/src/bin/scripts/droplang,v 1.26 2002/10/16 03:44:28 momjian Exp $
  #
  #-------------------------------------------------------------------------

***************
*** 229,235 ****
  # ----------
  # Drop the language
  # ----------
! sqlcmd="DROP LANGUAGE \"$langname\";"
  if [ "$showsql" = yes ]; then
      echo "$sqlcmd"
  fi
--- 229,235 ----
  # ----------
  # Drop the language
  # ----------
! sqlcmd="SET autocommit TO 'on';DROP LANGUAGE \"$langname\";"
  if [ "$showsql" = yes ]; then
      echo "$sqlcmd"
  fi
***************
*** 256,262 ****
      exit 1
  fi

! sqlcmd="DROP FUNCTION \"$handler\" ();"
  if [ "$showsql" = yes ]; then
      echo "$sqlcmd"
  fi
--- 256,262 ----
      exit 1
  fi

! sqlcmd="SET autocommit TO 'on';DROP FUNCTION \"$handler\" ();"
  if [ "$showsql" = yes ]; then
      echo "$sqlcmd"
  fi
Index: scripts/dropuser
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/dropuser,v
retrieving revision 1.18
retrieving revision 1.20
diff -c -c -r1.18 -r1.20
*** scripts/dropuser    20 Jun 2002 20:29:42 -0000    1.18
--- scripts/dropuser    16 Oct 2002 03:44:28 -0000    1.20
***************
*** 9,15 ****
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/dropuser,v 1.18 2002/06/20 20:29:42 momjian Exp $
  #
  # Note - this should NOT be setuid.
  #
--- 9,15 ----
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/dropuser,v 1.20 2002/10/16 03:44:28 momjian Exp $
  #
  # Note - this should NOT be setuid.
  #
***************
*** 148,154 ****

  DelUser=`echo "$DelUser" | sed 's/\"/\\\"/g'`

! ${PATHNAME}psql $PSQLOPT -d template1 -c "DROP USER \"$DelUser\""

  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: deletion of user \"$DelUser\" failed" 1>&2
--- 148,154 ----

  DelUser=`echo "$DelUser" | sed 's/\"/\\\"/g'`

! ${PATHNAME}psql $PSQLOPT -d template1 -c "SET autocommit TO 'on';DROP USER \"$DelUser\""

  if [ "$?" -ne 0 ]; then
      echo "$CMDNAME: deletion of user \"$DelUser\" failed" 1>&2
Index: scripts/vacuumdb
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/scripts/vacuumdb,v
retrieving revision 1.23
retrieving revision 1.25
diff -c -c -r1.23 -r1.25
*** scripts/vacuumdb    10 Aug 2002 16:57:32 -0000    1.23
--- scripts/vacuumdb    16 Oct 2002 03:44:28 -0000    1.25
***************
*** 12,18 ****
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/vacuumdb,v 1.23 2002/08/10 16:57:32 petere Exp $
  #
  #-------------------------------------------------------------------------

--- 12,18 ----
  #
  #
  # IDENTIFICATION
! #    $Header: /cvsroot/pgsql-server/src/bin/scripts/vacuumdb,v 1.25 2002/10/16 03:44:28 momjian Exp $
  #
  #-------------------------------------------------------------------------

***************
*** 169,175 ****
  for db in $dbname
  do
          [ "$alldb" -a "$quiet" -ne 1 ] && echo "Vacuuming $db"
!     ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "VACUUM $full $verbose $analyze $table" -d $db
      if [ "$?" -ne 0 ]; then
          echo "$CMDNAME: vacuum $table $db failed" 1>&2
          exit 1
--- 169,175 ----
  for db in $dbname
  do
          [ "$alldb" -a "$quiet" -ne 1 ] && echo "Vacuuming $db"
!     ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "SET autocommit TO 'on';VACUUM $full $verbose $analyze $table" -d $db
      if [ "$?" -ne 0 ]; then
          echo "$CMDNAME: vacuum $table $db failed" 1>&2
          exit 1