Re: 25P02, current transaction is aborted, commands ignored

Sure,

So

try{

1. Set autocommit off at the beginning of transaction

2.Do all bulky DML operations including statements which may fail e.g unique constraint violation, but do not amount to an transaction failure(for specific errors like 23505) from functional point of view.

3.
} catch(Exceptions){

Rollback fully for all other exceptions which were not chosen to be ignored.

}

**Must** an atomic transaction use savepoints to recover from acceptable errors? There is no stopping in between, we either succeed or fail fully. Ideally,in current scenario there is no need to rollback to a savepoint , statement just needed to be ignored and next statement executed. If I am not wrong then savepoints are best utilised when we do not want to lose the heavy dbms activity carried out in executing SQL statements just because one simple DML statement threw error. The focus is on successfully **committing** changes to DB and in that process sometimes we have to rollback to a savepoint and then try an alternative route to successful completion of block of statements. In my code I am not rolling back to a savepoint for any of these reasons, I am rolling back to avoid getting 25P02 and losing transaction completely.

Cheers,

Amaresh

Amaresh

"Dave Cramer" wrote:

Well,

The concept of an atomic transaction means that it must either succeed completely or fail completely. PostgreSQL does this.?

Dave

On 2-Apr-06, at 8:08 AM, Amaresh Wakkar wrote:

Thanks!!

The code?works fine after I added savepoints around??"Okay,even if fails" statement.

What is the idea behind taking this route(i.e all statements ignored till end of block) though? Is there any archive thread discussing this? In this particular case, the autocommit is off and the statement has failed so there is no risk of changes being made permanent(presumable PG would rollback at database level the changed buffers etc.,) unless the commit() method is invoked. Why then explicit rollback is needed? Is it not best if it is left to programmer to handle this in exception handling code?

I just had to add two lines of savepoints but I added them for making my code work rather than using them for some?application logic?purpose.

Cheers!!

Amaresh Wakkar

"Oliver Jowett" wrote:

babu_moshay wrote:

> In my opinion, if there are reasons to throw 25P02 and abort transaction unilaterally, then there are also good reasons not to abort it and let programmer take the decision. A switching mechanism would have been ideal.

Create a savepoint before the possibly-failing query. If the query fails
in the way you were expecting, roll back to the savepoint and continue.

-O

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Indiatimes Email now powered by APIC Advantage. Help!

" target=_blank" order="0" b!=""Help

Indiatimes Email now powered by APIC Advantage. Help!
" " Help