Re: [HACKERS] Continue transactions after errors in psql

Applied.

---------------------------------------------------------------------------

pgman wrote:
> 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));

--
  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