Thread: BUG #2370: 25P02,current transaction is aborted, commands ignored until end of transaction block

The following bug has been logged online:

Bug reference:      2370
Logged by:          Amaresh Wakkar
Email address:      babu_moshay@indiatimes.com
PostgreSQL version: 8.X
Operating system:   Windows XP
Description:        25P02,current transaction is aborted, commands ignored
until end of transaction block
Details:

Deal all,
I am trying to port my application from MySql to PostgreSQL 8.X. It seems
PostgreSQL does not give full control to programmer even when they have
opted to handle the transaction themselves by setting AUTOCOMMIT=FALSE in
code.


Scenario:
1.Several records are to be inserted in different tables as a result of
taking an order via web form. This activity must be atomic i.e it must
succeed or fail completely.

2. Autocommit is set to off before an anonymous transaction is started in
JDBC.

3.In one of the inserts we must ignore the failure due to duplicate rows i.e
Error state 23505 and continue with remainder set of inserts. Presence of
duplicate rows will not be treated as an error condition in this context and
system must function normally without aborting the whole transaction (This
is what PostgreSQL is doing currently after first failure I get 25P02 and
all other inserts are ignored completely). I get following error:

org.postgresql.util.PSQLException: ERROR: current transaction is aborted,
commands ignored until end of transaction block

Technical details:
I don't think this is specific to hardware/software version I am currently
using but giving here for the sake of completeness:
PostgreSQL version tried : 8.1.3 and 8.0, 8.0.1
JDBC: 8.2dev-501.jdbc3,   8.0-315.jdbc3,       8.1-404.jdbc3
JDK: 1.4 and 1.5
OS: Windows XP

Case:
The general expectation is that when a programmer is setting autocommit
explicitly to off then they want to be in control of the transactions and
'THEY' should decided when to rollback or commit instead of others. This is
the case with Oracle, SQL Server and MySQL and I think this is a fair
expectation. I am sure this is fairly common scenario with people who do
frequent backend coding.

I went through archives as it seems this was not a problem in version 8.0.1,
unfortunately I have tried with 3 different 8.x.x versions available and
similarly for JDBC drivers and the problem/condition still exists.

By looking at the error code 25P02, I feel this may not be treated as
problem/error by PostgreSQL community. What is the work-around in such
event?

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.

Any help/suggestion much appreciated!!!

Best regards,
Amaresh Wakkar

Amaresh Wakkar wrote:

> 3.In one of the inserts we must ignore the failure due to duplicate rows i.e
> Error state 23505 and continue with remainder set of inserts. Presence of
> duplicate rows will not be treated as an error condition in this context and
> system must function normally without aborting the whole transaction (This
> is what PostgreSQL is doing currently after first failure I get 25P02 and
> all other inserts are ignored completely). I get following error:
>
> org.postgresql.util.PSQLException: ERROR: current transaction is aborted,
> commands ignored until end of transaction block

Sure.  You can set a SAVEPOINT before trying the insert; if it fails,
ROLLBACK TO said savepoint and you can continue merrily with the rest of
your transaction.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support