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

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(at)bacchus(dot)com(dot)au> wrote:
> ** Reply to message from "junzeng" <junzeng(at)netease(dot)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