Re: [HACKERS] Continue transactions after errors in psql

Applied.

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

pgman wrote:
> pgman wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073