Bruce Momjian wrote:
> Greg Sabino Mullane wrote:
> > > The SQL-Standard itself says that errors inside transactions should only
> > > rollback the last statement, if possible. So why is that not implemented
> > > in
> > > PostgreSQL? What I read from past discussions here, is because it's just
> > > unsave and will lead to data-garbage if you aren't very careful.
> >
> > That's a good point: if that is indeed what the standard says, we should
> > probably see about following it. Rolling back to the last savepoint seems
> > a reasonable behavior to me.
>
> The question is what to make the default:
>
> o disable it by default for all sessions (current patch)
> o enable it by default only for interactive sessions, like AUTOCOMMIT
> o enable it by default for all sessions (breaks too many apps)
> o add a third mode called 'ttyonly' and figure out a default
Based on the comments I received, and the mention that ignoring errors
is part of the SQL standard, I chose the second option, patch attached:
$ psql test
Welcome to psql 8.1devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=> BEGIN;
BEGIN
test=> asdf;
ERROR: syntax error at or near "asdf" at character 1
LINE 1: asdf;
^
test=> SELECT 1;
?column?
----------
1
(1 row)
test=> COMMIT;
COMMIT
Can someone confirm that this is the way Oracle works as well? I
checked on IRC and isql does it. I am uncertain how applications
behave.
--
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 25 Apr 2005 20:01:05 -0000
***************
*** 2050,2055 ****
--- 2050,2075 ----
</varlistentry>
<varlistentry>
+ <indexterm>
+ <primary>rollback</primary>
+ <secondary>psql</secondary>
+ </indexterm>
+ <term><varname>ON_ERROR_ROLLBACK</varname></term>
+ <listitem>
+ <para>
+ When <literal>on</> (the default), in interactive mode,
+ ignore errors generated by commands in a transaction block,
+ rather than aborting the transaction. Ignoring errors never
+ happens in non-interactive mode or if the value is
+ <literal>off</>. 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 25 Apr 2005 20:01:08 -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,952 ----
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;
!
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))
{
--- 974,982 ----
SetCancelConn();
! transaction_status = PQtransactionStatus(pset.db);
!
! if (transaction_status == PQTRANS_IDLE &&
!GetVariableBool(pset.vars, "AUTOCOMMIT") &&
!command_no_begin(query))
{
***************
*** 987,992 ****
--- 990,1019 ----
}
PQclear(results);
}
+ else if (transaction_status == PQTRANS_INTRANS &&
+ pset.cur_cmd_interactive &&
+ GetVariableBool(pset.vars, "ON_ERROR_ROLLBACK"))
+ {
+ 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 ****
--- 1032,1072 ----
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));
Index: src/bin/psql/startup.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/startup.c,v
retrieving revision 1.113
diff -c -c -r1.113 startup.c
*** src/bin/psql/startup.c 22 Feb 2005 04:40:58 -0000 1.113
--- src/bin/psql/startup.c 25 Apr 2005 20:01:11 -0000
***************
*** 149,154 ****
--- 149,155 ----
/* Default values for variables */
SetVariableBool(pset.vars, "AUTOCOMMIT");
+ SetVariableBool(pset.vars, "ON_ERROR_ROLLBACK");
SetVariable(pset.vars, "VERBOSITY", "default");
SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1);
SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
