Re: [HACKERS] Continue transactions after errors in psql

pgman wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Tom Lane wrote:
> > >> Well, that's just a matter of choosing good (ie short) names for the
> > >> backslash commands.  I was trying to be clear rather than proposing
> > >> names I would actually want to use ;-).  Any suggestions?
> >
> > > Well, if we allowed ON_ERROR_ROLLBACK to work in non-interactive
> > > sessions we could just do:
> >
> > >     \set ON_ERROR_ROLLBACK on
> > >     DROP TABLE foo;
> > >     \set ON_ERROR_ROLLBACK off
> >
> > That isn't the same thing at all.  The syntax I was proposing allows the
> > script writer to define a savepoint covering multiple statements,
> > whereas the above does not.
>
> Well, it fits the use case posted, that is to conditionally roll back a
> _single_ failed query.  I don't see the need to add a new
> infrastructure/command unless people have a use case for rolling back a
> group of statements on failure.  I have no seen such a description yet.

OK, updated patch that allows for 'on/interactive/off'.  Seems there are
enough use cases to add an 'interactive' option.

--
  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: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.134
diff -c -c -r1.134 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    14 Mar 2005 06:19:01 -0000    1.134
--- doc/src/sgml/ref/psql-ref.sgml    28 Apr 2005 03:35:00 -0000
***************
*** 2050,2055 ****
--- 2050,2077 ----
        </varlistentry>

        <varlistentry>
+       <indexterm>
+        <primary>rollback</primary>
+        <secondary>psql</secondary>
+       </indexterm>
+         <term><varname>ON_ERROR_ROLLBACK</varname></term>
+         <listitem>
+         <para>
+         When <literal>on</>, if a statement in a transaction block
+         generates an error, the error is ignored and the transaction
+         continues. When <literal>interactive</>, such errors are only
+         ignored in interactive sessions, and not when reading script
+         files. When <literal>off</> (the default), a statement in a
+         transaction block that generates an error aborts the entire
+         transaction. The on_error_rollback-on mode works by issuing an
+         implicit <command>SAVEPONT</> for you, just before each command
+         that is in a transaction block, and rolls back to the savepoint
+         on error.
+         </para>
+         </listitem>
+       </varlistentry>
+
+       <varlistentry>
          <term><varname>ON_ERROR_STOP</varname></term>
          <listitem>
          <para>
Index: src/bin/psql/common.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/common.c,v
retrieving revision 1.96
diff -c -c -r1.96 common.c
*** src/bin/psql/common.c    22 Feb 2005 04:40:52 -0000    1.96
--- src/bin/psql/common.c    28 Apr 2005 03:35:01 -0000
***************
*** 941,951 ****
  bool
  SendQuery(const char *query)
  {
!     PGresult   *results;
!     TimevalStruct before,
!                 after;
!     bool        OK;
!
      if (!pset.db)
      {
          psql_error("You are currently not connected to a database.\n");
--- 941,953 ----
  bool
  SendQuery(const char *query)
  {
!     PGresult     *results;
!     TimevalStruct before, after;
!     bool OK, on_error_rollback_savepoint = false;
!     PGTransactionStatusType transaction_status;
!     static bool        on_error_rollback_warning = false;
!     const char *rollback_str;
!
      if (!pset.db)
      {
          psql_error("You are currently not connected to a database.\n");
***************
*** 973,979 ****

      SetCancelConn();

!     if (PQtransactionStatus(pset.db) == PQTRANS_IDLE &&
          !GetVariableBool(pset.vars, "AUTOCOMMIT") &&
          !command_no_begin(query))
      {
--- 975,983 ----

      SetCancelConn();

!     transaction_status = PQtransactionStatus(pset.db);
!
!     if (transaction_status == PQTRANS_IDLE &&
          !GetVariableBool(pset.vars, "AUTOCOMMIT") &&
          !command_no_begin(query))
      {
***************
*** 987,992 ****
--- 991,1023 ----
          }
          PQclear(results);
      }
+     else if (transaction_status == PQTRANS_INTRANS &&
+              (rollback_str = GetVariable(pset.vars, "ON_ERROR_ROLLBACK")) != NULL &&
+              /* !off and !interactive is 'on' */
+              pg_strcasecmp(rollback_str, "off") != 0 &&
+              (pset.cur_cmd_interactive ||
+               pg_strcasecmp(rollback_str, "interactive") != 0))
+     {
+         if (on_error_rollback_warning == false && pset.sversion < 80000)
+         {
+             fprintf(stderr, _("The server version (%d) does not support savepoints for ON_ERROR_ROLLBACK.\n"),
+                 pset.sversion);
+             on_error_rollback_warning = true;
+         }
+         else
+         {
+             results = PQexec(pset.db, "SAVEPOINT pg_psql_temporary_savepoint");
+             if (PQresultStatus(results) != PGRES_COMMAND_OK)
+             {
+                 psql_error("%s", PQerrorMessage(pset.db));
+                 PQclear(results);
+                 ResetCancelConn();
+                 return false;
+             }
+             PQclear(results);
+             on_error_rollback_savepoint = true;
+         }
+     }

      if (pset.timing)
          GETTIMEOFDAY(&before);
***************
*** 1005,1010 ****
--- 1036,1076 ----

      PQclear(results);

+     /* If we made a temporary savepoint, possibly release/rollback */
+     if (on_error_rollback_savepoint)
+     {
+         transaction_status = PQtransactionStatus(pset.db);
+
+         /* We always rollback on an error */
+         if (transaction_status == PQTRANS_INERROR)
+             results = PQexec(pset.db, "ROLLBACK TO pg_psql_temporary_savepoint");
+         /* If they are no longer in a transaction, then do nothing */
+         else if (transaction_status != PQTRANS_INTRANS)
+             results = NULL;
+         else
+         {
+             /*
+              *    Do nothing if they are messing with savepoints themselves:
+              *    If the user did RELEASE or ROLLBACK, our savepoint is gone.
+              *    If they issued a SAVEPOINT, releasing ours would remove theirs.
+              */
+             if (strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 ||
+                 strcmp(PQcmdStatus(results), "RELEASE") == 0 ||
+                 strcmp(PQcmdStatus(results), "ROLLBACK") ==0)
+                 results = NULL;
+             else
+                 results = PQexec(pset.db, "RELEASE pg_psql_temporary_savepoint");
+         }
+         if (PQresultStatus(results) != PGRES_COMMAND_OK)
+         {
+             psql_error("%s", PQerrorMessage(pset.db));
+             PQclear(results);
+             ResetCancelConn();
+             return false;
+         }
+         PQclear(results);
+     }
+
      /* Possible microtiming output */
      if (OK && pset.timing)
          printf(_("Time: %.3f ms\n"), DIFF_MSEC(&after, &before));