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

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

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

babu_moshay
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
Reply | Threaded
Open this post in threaded view
|

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

Oliver Jowett
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
Reply | Threaded
Open this post in threaded view
|

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

im i
In reply to this post by babu_moshay
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 <[hidden email]> 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
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

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

babu_moshay
In reply to this post by Oliver Jowett

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


Reply | Threaded
Open this post in threaded view
|

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

babu_moshay
In reply to this post by babu_moshay

Thanks!!!

It works fine now. Savepoints were saviours :-)

Amaresh Wakkar

"=?ISO-8859-1?Q?Reg=E1sz_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
>


Indiatimes Email now powered by APIC Advantage. Help!
" >" >Help


Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-8
In reply to this post by babu_moshay
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



Reply | Threaded
Open this post in threaded view
|

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

Oliver Jowett
In reply to this post by babu_moshay
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

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

babu_moshay
In reply to this post by Oliver Jowett

Sure,

So

try{

1. Set autocommit off at the beginning of transaction

2.Do all bulky DML operations including statements which may fail e.g unique constraint violation, but do not amount to an transaction failure(for specific errors like 23505) from functional point of view.

3.
} catch(Exceptions){

Rollback fully for all other exceptions which were not chosen to be ignored.

}

**Must** an atomic transaction use savepoints to recover from acceptable errors? There is no stopping in between, we either succeed or fail fully. Ideally,in current scenario there is no need to rollback to a savepoint , statement just needed to be ignored and next statement executed. If I am not wrong then savepoints are best utilised when we do not want to lose the heavy dbms activity carried out in executing SQL statements just because one simple DML statement threw error. The focus is on successfully **committing** changes to DB and in that process sometimes we have to rollback to a savepoint and then try an alternative route to successful completion of block of statements. In my code I am not rolling back to a savepoint for any of these reasons, I am rolling back to avoid getting 25P02 and losing transaction completely.

Cheers,

Amaresh

 

 

 

Amaresh


"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!
" target=_blank>" order="0" b!="">Help




Indiatimes Email now powered by APIC Advantage. Help!
" >" >Help


Reply | Threaded
Open this post in threaded view
|

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

babu_moshay
In reply to this post by Oliver Jowett


"Oliver Jowett" wrote:


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

>>>>Ok, Thanks!!

> 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).
>>>>> Once a statement fails, to continue further, savepoints need to be used so that postgreSQL implicit rollback is not done. If implicit rollback by postgreSQL is done, I will get 25P02 next time I try some query so I have lost the transaction anyway unless there is a way to recover from 25P02.

Amaresh 

 

-O



Indiatimes Email now powered by APIC Advantage. Help!
" >" >Help


Reply | Threaded
Open this post in threaded view
|

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

Philip Yarra
In reply to this post by Dave Cramer-8
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.

import java.sql.*;
import java.util.*;
import java.io.*;
import java.text.SimpleDateFormat;

class main
{
        static Connection conn = null;
        static Properties prop = null;
       
        public static void main(String [] args)
        {
                try
                {
                        if(args.length > 0) prop = loadProp(args[0]);
                        else usage();
                        String url = prop.getProperty("url");
                        String user = prop.getProperty("user");
                        String pass = prop.getProperty("pass");
                        String driver = prop.getProperty("driver");
                        Class.forName(driver);
                        conn = DriverManager.getConnection(url,user,pass);
                        conn.setAutoCommit(false);
                        createTable();
                        executeInsert(1,"expected to succeed");
                        executeInsert(1,"expected to fail");
                        executeInsert(2,"expected to fail");
                        conn.commit();
                        dumpTable("tempextest");
                        conn.commit();
                        dropTable();
                        conn.commit();
                        conn.close();
                        log("all done");
                }

                catch(Exception ex)
                {
                        log(ex);
                        System.exit(1);
                }
        }

        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);
                }
        }

        static void createTable()
        {
                log("Creating table tempextest");
                PreparedStatement stmt = null;
                try {
                        stmt = conn.prepareStatement("CREATE TABLE tempextest(id INT PRIMARY KEY, msg VARCHAR(20))");
                        stmt.executeUpdate();
                } catch (SQLException sqlex) {
                        log(sqlex);
                        log("error creating table tempextest, can't proceed");
                        closeStatement(stmt);
                        System.exit(1);

                }
                log("Table tempextest created");
        }

        static void dropTable()
        {
                log("Dropping table tempextest");
                PreparedStatement stmt = null;
                try {
                        stmt = conn.prepareStatement("DROP TABLE tempextest");
                        stmt.executeUpdate();
                } catch (SQLException sqlex) {
                        log(sqlex);
                        closeStatement(stmt);
                }
                log("Table tempextest dropped");
        }

        static Properties loadProp(String fileName)
        {
                try{
                Properties prop = new Properties();
                FileInputStream fis = new FileInputStream(fileName);
                prop.load(fis);
                return prop;
                } catch (Exception ex) {
                        log("exception loading properties: " + ex);
                        usage();
                }
                return null;
        }

        static void usage()
        {
                System.err.println("Usage: java main propfile");
                System.exit(1);
        }

        static void log(String msg)
        {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S");
                System.out.println(sdf.format(new java.util.Date()) + ":" + msg);
        }

        static void log(Exception ex)
        {
                log(ex.toString());
                ex.printStackTrace();
        }

        static void log(Object o)
        {
                log(o.toString());
        }

        static void closeStatement(Statement st)
        {
                try{
                        st.close();
                }catch(SQLException sqlex) {
                        log(sqlex);
                }
        }

        static void dumpTable(String tableName)
        {
                log("dumping contents of table " + tableName);
                try{
                        Statement s = conn.createStatement();        
                        ResultSet rs = s.executeQuery("SELECT * FROM " + tableName);
                        ResultSetMetaData rsmd = rs.getMetaData();
                        for(int i = 1; i <= rsmd.getColumnCount(); i++)
                        {
                                System.out.print(rsmd.getColumnName(i) + "\t");
                                        if(i != rsmd.getColumnCount())
                                                System.out.print("|");
                        }
                        System.out.println("\n---------------------------------------------------------------------");
                        while(rs.next())
                        {
                                for(int i = 1; i <= rsmd.getColumnCount(); i++)
                                {
                                        System.out.print(rs.getString(i) + "\t");
                                                if(i != rsmd.getColumnCount())
                                                        System.out.print("|");
                                }
                                System.out.println();
                        }
                        rs.close();
                        s.close();
                }catch(SQLException sqlex) {
                        System.err.println(sqlex);
                }
        }

}


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

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

Oliver Jowett
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

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

Oliver Jowett
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

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

Oliver Jowett
In reply to this post by Philip Yarra
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

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

Philip Yarra
In reply to this post by Oliver Jowett
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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

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

Philip Yarra
In reply to this post by Oliver Jowett
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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

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

sangeetha-4
In reply to this post by babu_moshay
Dear all,
  Actually i am trying to insert some values within a transaction. I am
also carrying out some data select operations after opening the
transaction. But i am getting the above mentioned error. But i want the
insert to happen. Please reply back to me as early as possible. i would
be very thankful to you guys. I am sending through mail because i am
unable to post my issue.

Regards,
Sangeetha
(Team lead/GBBPR Soft).

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-8
Any error in your transaction causes it to abort.

Dave
On 13-May-08, at 8:50 AM, sangeetha wrote:

> Dear all,
> Actually i am trying to insert some values within a transaction. I  
> am also carrying out some data select operations after opening the  
> transaction. But i am getting the above mentioned error. But i want  
> the insert to happen. Please reply back to me as early as possible.  
> i would be very thankful to you guys. I am sending through mail  
> because i am unable to post my issue.
>
> Regards,
> Sangeetha
> (Team lead/GBBPR Soft).
>
> --
> Sent via pgsql-jdbc mailing list ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|

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

Albe Laurenz *EXTERN*
In reply to this post by sangeetha-4
sangeetha wrote:
>   Actually i am trying to insert some values within a transaction. I am
> also carrying out some data select operations after opening the
> transaction. But i am getting the above mentioned error. But i want the
> insert to happen. Please reply back to me as early as possible. i would
> be very thankful to you guys. I am sending through mail because i am
> unable to post my issue.

One of the statements within you transaction caused an error.
Every following statement in the transaction will raise the error
message you get until you ROLLBACK the whole transaction.

To preempt the question what you can do if you do not want to rollback
the whole transaction because of an error, I recommend the use of
savepoints:
http://www.postgresql.org/docs/current/static/sql-savepoint.html

Just set one before every statement that might cause an error,
and ROLLBACK TO SAVEPOINT if you get one.

Yours,
Laurenz Albe

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc