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

Deal all,
I am trying to port my application from MySql to PostgreSQL 8.X. It seems PostgreSQL does not give full control to
programmereven 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
mustbe 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
remainderset of inserts. Presence of duplicate rows will not be treated as an error condition in this context and
systemmust function normally without aborting the whole transaction (This is what PostgreSQL is doing currently after
firstfailure 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
controlof the transactions and 'THEY' should decided when to rollback or commit instead of others. This is the case
withOracle, SQL Server and MySQL and I think this is a fair expectation. I am sure this is fairly common scenario with
peoplewho 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
different8.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
thework-around in such event? 

In my opinion, if there are reasons to throw 25P02 and abort transaction unilaterally,  then there are also good
reasonsnot 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

babu_moshay wrote:

> In my opinion, if there are reasons to throw 25P02 and abort transaction unilaterally,  then there are also good
reasonsnot 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

You need use SAVEPOINT in transaction:

do {
  String savepointName = "any string"+class.hasCode();
  Savepoint savePoint = conn.setSavepoint(savepointName);
  try {
     stmt.execute();
     success = true;
  } catch (SQLException sqle) {
      conn.rollback(savePoint);
  }
} while (!success);
http://www.postgresql.org/docs/8.1/interactive/sql-savepoint.html
http://www.postgresql.org/docs/8.1/interactive/sql-rollback-to.html


On 4/2/06, babu_moshay <babu_moshay@indiatimes.com> wrote:
> Deal all,
> I am trying to port my application from MySql to PostgreSQL 8.X. It seems PostgreSQL does not give full control to
programmereven 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
mustbe 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
remainderset of inserts. Presence of duplicate rows will not be treated as an error condition in this context and
systemmust function normally without aborting the whole transaction (This is what PostgreSQL is doing currently after
firstfailure 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
controlof the transactions and 'THEY' should decided when to rollback or commit instead of others. This is the case
withOracle, SQL Server and MySQL and I think this is a fair expectation. I am sure this is fairly common scenario with
peoplewho 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
different8.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
thework-around in such event? 
>
> In my opinion, if there are reasons to throw 25P02 and abort transaction unilaterally,  then there are also good
reasonsnot 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

Thanks!!!

It works fine now. Savepoints were saviours :-)

Amaresh Wakkar

"Regász Imre" wrote:

You need use SAVEPOINT in transaction:

do {
String savepointName = "any string"+class.hasCode();
Savepoint savePoint = conn.setSavepoint(savepointName);
try {
stmt.execute();
success = true;
} catch (SQLException sqle) {
conn.rollback(savePoint);
}
} while (!success);
http://www.postgresql.org/docs/8.1/interactive/sql-savepoint.html
http://www.postgresql.org/docs/8.1/interactive/sql-rollback-to.html


On 4/2/06, babu_moshay wrote:
> 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 w! eb 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 thr o! w 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Help