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

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!
" " Help

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!
> Help
>

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?

There should be tons, it comes up regularly whenever someone ports an
app from Oracle..

> 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?

There are arguments both ways. To some extent it depends on who you
trust more to get the code right: the DB developer (once) or the app
developer (once per query).

-O

I always assumed what Dave just said, but porting from Oracle & Sybase
to PostgreSQL, we ran into exactly the same issue - we also solved it
with savepoints. However, I threw together the attached sample app to
test *precisely* what ends up in the database when auto-commit is off.
For the impatient, it sets auto-commit off, and tries to insert 3 rows.
The first succeeds, the second violates a unique index, so fails, and
the third is issued after the second, so should also fail. We ignore the
exceptions, then commit. The results puzzle me somewhat:

Oracle 10g: first and third inserts are in the DB
Sybase ASE 12.5: first and third inserts are in the DB
PostgreSQL 8.1.1: first insert is in the DB

Now I agree that Oracle and Sybase have this kind of wrong - the third
insert should not succeed. However, reading Dave's statement "The
concept of an atomic transaction means that it must either succeed
completely or fail completely. PostgreSQL does this." makes me wonder if
the first insert should be in the DB either? Or am I making some sort of
mistake here? From my results, it looks more like PostgreSQL's behaviour
is "Everything up the first failure can be committed" which isn't quite
the same thing as an indivisible unit of work that succeeds or fails
completely.

Anyway, I'd be curious about people's feedback on this, as it has sort
of nagged at me since I tested it.

Regards, Philip.

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!
>> <http://infinite.indiatimes.com/apic/>
>> My Presence
>> <http://imaround.indiatimes.com/IMaround/presencefr.mss?userid=<!--User
>> //-->>Help <http://infinite.indiatimes.com/apic/userpage.html>
>> ------------------------------------------------------------------------
>>
>> <http://www.indiatimes.chikka.com>
>
>
>
> ----------------
> This message has been scanned for viruses and
> dangerous content by *the Utiba Mail Server* <http://www.utiba.com/>,
> and is
> believed to be clean.

Philip Yarra wrote:
> I always assumed what Dave just said, but porting from Oracle & Sybase
> to PostgreSQL, we ran into exactly the same issue - we also solved it
> with savepoints. However, I threw together the attached sample app to
> test *precisely* what ends up in the database when auto-commit is off.
> For the impatient, it sets auto-commit off, and tries to insert 3 rows.
> The first succeeds, the second violates a unique index, so fails, and
> the third is issued after the second, so should also fail. We ignore the
> exceptions, then commit. The results puzzle me somewhat:
>
> Oracle 10g: first and third inserts are in the DB
> Sybase ASE 12.5: first and third inserts are in the DB
> PostgreSQL 8.1.1: first insert is in the DB
>
> Now I agree that Oracle and Sybase have this kind of wrong - the third
> insert should not succeed. However, reading Dave's statement "The
> concept of an atomic transaction means that it must either succeed
> completely or fail completely. PostgreSQL does this." makes me wonder if
> the first insert should be in the DB either? Or am I making some sort of
> mistake here? From my results, it looks more like PostgreSQL's behaviour
> is "Everything up the first failure can be committed" which isn't quite
> the same thing as an indivisible unit of work that succeeds or fails
> completely.

Can we see your testcase? The behaviour you describe is not what I'd expect.

-O

Oliver Jowett wrote:
> Can we see your testcase? The behaviour you describe is not what I'd
> expect.

And 2 seconds after hitting send I see it's attached to your mail .. sorry!

-O

Oliver Jowett wrote:
> And 2 seconds after hitting send I see it's attached to your mail .. sorry!

Been there, done that before :-)

I have access to Sybase and Oracle DBs here. If you want, I can also
send output from running this test case against all 3 DB flavours I
mentioned originally. From your comments, though, I'm betting I've
goofed my test case somehow, and I just can't see it.

Regards, Philip.

Philip Yarra wrote:
> I always assumed what Dave just said, but porting from Oracle & Sybase
> to PostgreSQL, we ran into exactly the same issue - we also solved it
> with savepoints. However, I threw together the attached sample app to
> test *precisely* what ends up in the database when auto-commit is off.
> For the impatient, it sets auto-commit off, and tries to insert 3 rows.
> The first succeeds, the second violates a unique index, so fails, and
> the third is issued after the second, so should also fail. We ignore the
> exceptions, then commit. The results puzzle me somewhat:

> static void executeInsert(int id, String msg)
> {
> log("executeInsert, id[" + id + "] msg[" + msg + "]");
> PreparedStatement stmt = null;
> try{
> stmt = conn.prepareStatement("INSERT INTO tempextest(id,msg) VALUES(?,?)");
> stmt.setInt(1,id);
> stmt.setString(2,msg);
> stmt.executeUpdate();
> conn.commit();
> } catch (SQLException sqlex) {
> log(sqlex);
> closeStatement(stmt);
> }
> }

Um, your testcase is committing after every insert, not once at the end.
So the behaviour you see sounds correct.

-O

Oliver Jowett wrote:
> Um, your testcase is committing after every insert, not once at the end.
> So the behaviour you see sounds correct.

*sigh* so it is. Sorry about that, Oliver.

With the bogus commit removed, yes, PostgreSQL does exactly what it is
supposed to.

For the record: Sybase and Oracle still happily end up with inserts one
and three in the DB.

Regards, Philip.