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