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

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(at)indiatimes(dot)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 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>