Re: Query error=Implicit ABORT?

** Reply to message from Eric D Nielsen <nielsene(at)MIT(dot)EDU> on Sat, 24 May 2003
11:56:17 -0400
Hi,
Here is an example of when the implicit rollback is just dangerous.

Here is a snippet from a postgres log :-
2003-05-24 23:09:14 [1525] LOG: duration: 0.134503 sec
2003-05-24 23:09:14 [1525] LOG: query: select nspname from pg_namespace n, p
_class c where c.relnamespace=n.oid and c.oid='select'::regclass
2003-05-24 23:09:14 [1525] ERROR: Relation "select" does not exist
2003-05-24 23:09:14 [1525] LOG: statement: select nspname from pg_namespace
, pg_class c where c.relnamespace=n.oid and c.oid='select'::regclass
2003-05-24 23:09:14 [1525] LOG: query: ROLLBACK
2003-05-24 23:09:14 [1525] LOG: duration: 0.000538 sec

The rollback here is generated by the odbc driver to clear an error created by
the odbc driver incorrectly parsing the select from a subselect statement as a
table name. The application is totally unaware an error has occured and a
rollback has been issued. This is most likely to lead to data loss, unless,
basically, an application using odbc is in autocommit mode or commits after
every sql statement issued. This concerns me to the piont where I would really
recommend not using the parse statement option in the odbc driver (even though
that reduces the odbc drivers ability to mimic a prepare which is also
problematic :) unless you are using autocommit.

Basically I think - implicit rollbacks on error should not occur as soon as
autocommit is off !

Regards,
Wayne Armstorng
Bacchus Management Systems
http://www.bacchus.com.au

> If in a transaction block, one of the queries generates an ERROR
> then the transaction is aborted prior to a COMMIT/ROLLBACK being ordered,
> but still requires a COMMIT/ROLLBACK to officially end the transaction.
>
> I was expecting to have to programmaticly check for the error(s) and either
> attempt to fix the issue or explicitly ROLLBACK. While the behavoir
> is extremely convienent for simple transactions, it seems to create a great
> deal of extra work in cases where you want to try to recover from error rather
> that aborting. To do so now, I would have to
> 1) issue query
> 2) check for error
> No Error) save the query to some log
> Error) issue the explict rollback to end the transaction
> program logic to recover
> start new transaction
> issue all saved query from the log
> issue recovered version of query
>
> This is a fair bit of work. However, I do realize that its rarely needed.
> If its possible to programmatically recover from an error, its
> should be possible to detect the error in advance 99.9% of the time and
> avoid generating the error in the first place. However, in cases where errors
> are very rarely expected, and testing for errors in advance is a performance
> issue, the above pattern would seem to be appropriate. Is there a way to
> disable the implict abort when it makes sense?
>
> This behavoir is alluded to in the docs, but not stated explicity; it is
> raised directly in one of the user-provided notes on BEGIN and is noted
> to be at odds with other DBMS's. Can this behavoir be counted on for the
> forseeable future versions of PostGreSQL?
>
> --- begin demonstration script ---
> CREATE TABLE foo (
> bar int primary key,
> baz int
> );
> BEGIN WORK;
> INSERT INTO foo (bar,baz) VALUES (1,2);
> SELECT joe FROM foo;
> COMMIT WORK;
> SELECT * FROM foo;
> --- end demonstration script ---
>
> Flipping the INSERT/SELECT in the transaction block raises the NOTICE
> referenced in the subject line and clued me in to what was happening.
>
> Thank you.
> Eric Nielsen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster