Thread: Can I do anything to prevent " auto rollback in a transaction when an error occurs "

Can I do anything to prevent " auto rollback in a transaction when an error occurs "

From

"junzeng"

Date:

21 March 2003, 01:03:16

In a transaction , if a sql command invite an error , all commands before this command will be rollbacked , and the transaction reset and restart.

For example:

BEGIN

insert t1 values(1);

delete from t2

insert t1 values(2);

commit

if table t2 doesn't exist , "delete from t2" invites an error , and the insert will be rollbacked .

How can I prevent the rollback even when an error occurs ? Thanks

Re: Can I do anything to prevent " auto rollback in a transaction when an error occurs "

From

"Wayne Armstrong"

Date:

21 March 2003, 02:13:04

** Reply to message from "junzeng" <junzeng@netease.com> on Fri, 21 Mar 2003
11:00:39 +0800

Hi,
 If you set the dissalow premature option in the odbc driver it can reduce the
risk of this - but it still does happen and it's extremely dangerous :(.
 I have hacked the odbc driver (for the 7.72.0.5 level) to work with visualage
smalltalk, and have removed the rollback on error with no obvious bad side
effects. (there probably are some though).

Regards,
Wayne

> In a transaction , if a sql command invite an error , all commands before this
> command will be rollbacked , and the transaction reset and restart.
> For example:
>
> BEGIN
> insert  t1 values(1);
> delete from t2
> insert t1 values(2);
> commit
>
> if table t2 doesn't exist , "delete from t2" invites an error , and the insert will be rollbacked .
>
> How can I prevent the rollback even when an error occurs ? Thanks

Re: Can I do anything to prevent " auto rollback in a transaction when an error occurs "

From

Chris Gamache

Date:

21 March 2003, 11:51:05

There's no reason to go breaking transactions when you can SQL around this
problem. The whole point of transactions are to rollback on error! :)

Here's one way around it...

-- first independant transaction
BEGIN;
insert  t1 values(1);
COMMIT;
-- second independant transaction
BEGIN;
delete from t2
COMMIT;
-- third independant transaction
BEGIN;
insert t1 values(2);
COMMIT;

Here's another way around it

-- try to create the table
-- if it exists transaction will rollback
BEGIN;
create table t2 (myval int4);
COMMIT;

-- this transaction will commit now.
BEGIN;
insert  t1 values(1);
delete from t2
insert t1 values(2);
COMMIT;

You might even create a function called deleteallifexists('tablename') and have
it check the pg_tables.tablename field for the table in question, and if it
exists, then do a delete.

CG

--- Wayne Armstrong <wdarmst@bacchus.com.au> wrote:
> ** Reply to message from "junzeng" <junzeng@netease.com> on Fri, 21 Mar 2003
> 11:00:39 +0800
>
> Hi,
>  If you set the dissalow premature option in the odbc driver it can reduce
> the
> risk of this - but it still does happen and it's extremely dangerous :(.
>  I have hacked the odbc driver (for the 7.72.0.5 level) to work with
> visualage
> smalltalk, and have removed the rollback on error with no obvious bad side
> effects. (there probably are some though).
>
> Regards,
> Wayne
>
> > In a transaction , if a sql command invite an error , all commands before
> this
> > command will be rollbacked , and the transaction reset and restart.
> > For example:
> >
> > BEGIN
> > insert  t1 values(1);
> > delete from t2
> > insert t1 values(2);
> > commit
> >
> > if table t2 doesn't exist , "delete from t2" invites an error , and the
> insert will be rollbacked .
> >
> > How can I prevent the rollback even when an error occurs ? Thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html



__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com