postgresql - until - sqlalchemy internal error current transaction is aborted




PSQLException: current transaction is aborted, commands ignored until end of transaction block (10)

I got this error using Java and postgresql doing an insert on a table. I will illustrate how you can reproduce this error:

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

Summary:

The reason you get this error is because you have entered a transaction and one of your SQL Queries failed, and you gobbled up that failure and ignored it. But that wasn't enough, THEN you used that same connection, using the SAME TRANSACTION to run another query. The exception gets thrown on the second, correctly formed query because you are using a broken transaction to do additional work. Postgresql by default stops you from doing this.

I'm using: PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.2 20120921 (Red Hat 4.7.2-2), 64-bit".

My postgresql driver is: postgresql-9.2-1000.jdbc4.jar

Using java version: Java 1.7

Here is the table create statement to illustrate the Exception:

CREATE TABLE moobar
(
    myval   INT
);

Java program causes the error:

public void postgresql_insert()
{
    try  
    {
        connection.setAutoCommit(false);  //start of transaction.

        Statement statement = connection.createStatement();

        System.out.println("start doing statement.execute");

        statement.execute(
                "insert into moobar values(" +
                "'this sql statement fails, and it " +
                "is gobbled up by the catch, okfine'); ");

        //The above line throws an exception because we try to cram
        //A string into an Int.  I Expect this, what happens is we gobble 
        //the Exception and ignore it like nothing is wrong.
        //But remember, we are in a TRANSACTION!  so keep reading.

        System.out.println("statement.execute done");

        statement.close();

    }
    catch (SQLException sqle)
    {
        System.out.println("keep on truckin, keep using " +
                "the last connection because what could go wrong?");
    }

    try{
        Statement statement = connection.createStatement();

        statement.executeQuery("select * from moobar");

        //This SQL is correctly formed, yet it throws the 
        //'transaction is aborted' SQL Exception, why?  Because:
        //A.  you were in a transaction.
        //B.  You ran a sql statement that failed.
        //C.  You didn't do a rollback or commit on the affected connection.

    }
    catch (SQLException sqle)
    {
        sqle.printStackTrace();
    }   

}

The above code produces this output for me:

start doing statement.execute

keep on truckin, keep using the last connection because what could go wrong?

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

Workarounds:

You have a few options:

  1. Simplest solution: Don't be in a transaction. Set the connection.setAutoCommit(false); to connection.setAutoCommit(true);. It works because then the failed SQL is just ignored as a failed sql statement. You are welcome to fail sql statements all you want and postgresql won't stop you.

  2. Stay being in a transaction, but when you detect that the first sql has failed, either rollback/re-start or commit/restart the transaction. Then you can continue failing as many sql queries on that database connection as you want.

  3. Don't catch and ignore the Exception that is thrown when a sql statement fails. Then the program will stop on the malformed query.

  4. Get Oracle instead, Oracle doesn't throw an exception when you fail a query on a connection within a transaction and continue using that connection.

In defense of postgresql's decision to do things this way... Oracle was making you soft in the middle letting you do dumb stuff and overlooking it.

I am seeing the following (truncated) stacktrace in the server.log file of JBoss 7.1.1 Final:

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

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.6.0_23]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [rt.jar:1.6.0_23]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [rt.jar:1.6.0_23]
at java.lang.reflect.Method.invoke(Method.java:597) [rt.jar:1.6.0_23]
at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:455)
at $Proxy49.executeUpdate(Unknown Source)   at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:371)
at org.infinispan.loaders.jdbc.TableManipulation.executeUpdateSql(TableManipulation.java:154) [infinispan-cachestore-jdbc-5.1.2.FINAL.jar:5.1.2.FINAL]
... 154 more

Inspecting the Postgres log file reveals the following statements:

STATEMENT:  SELECT count(*) FROM ISPN_MIXED_BINARY_TABLE_configCache
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  CREATE TABLE ISPN_MIXED_BINARY_TABLE_configCache(ID_COLUMN VARCHAR(255) NOT NULL, DATA_COLUMN BYTEA, TIMESTAMP_COLUMN BIGINT, PRIMARY KEY (ID_COLUMN))
ERROR:  relation "ispn_mixed_binary_table_configcache" does not exist at character 22

I am using the Infinispan shipped with JBoss 7.1.1 Final, which is 5.1.2.Final.

So this is what I think is happening:

  • Infinispan attempts to run the SELECT count(*)... statement in order to see if there are any records in the ISPN_MIXED_BINARY_TABLE_configCache;
  • Postgres, for some reason, does not like this statement.
  • Infinispan ignores this and plows ahead with the CREATE TABLE statement.
  • Postgres barfs because it still thinks it's the same transaction, which Infinispan has failed to roll back, and this transaction is shafted from the first SELECT count(*)... statement.

What does this error mean and any idea how to work around it?


Change the isolation level from repeatable read to read committed.


I am using JDBI with Postgres, and encountered the same problem, i.e. after a violation of some constraint from a statement of previous transaction, subsequent statements would fail (but after I wait for a while, say 20-30 seconds, the problem goes away).

After some research, I found the problem was I was doing transaction "manually" in my JDBI, i.e. I surrounded my statements with BEGIN;...COMMIT; and it turns out to be the culprit!

In JDBI v2, I can just add @Transaction annotation, and the statements within @SqlQuery or @SqlUpdate will be executed as a transaction, and the above mentioned problem doesn't happen any more!


I had the same issue but then realised there is a table with the same name in the database. After deleting that I was able to import the file.


I think that the best solution is use java.sql.Savepoint.

Before execute query which can throw SQLException use method Connection.setSavepoint() and if exception will be throw you only rollback to this savepoint not rollback all transaction.

Example code:

Connection conn = null;
Savepoint savepoint = null;
try {
    conn = getConnection();
    savepoint = conn.setSavepoint();
    //execute some query
} catch(SQLException e) {
    if(conn != null && savepoint != null) {
        conn.rollback(savepoint);
    }
} finally {
   if(conn != null) {
      try {
          conn.close();
      } catch(SQLException e) {}

   }
}

In Ruby on Rails PG, I had created a migration, migrated my DB, but forgot to restart my development server. I restarted my server and it worked.


The reason for this error is that there are other database before the wrong operation led to the current database operation can not be carried out(i use google translation to translate my chinese to english)



This is very weird behavior of PostgreSQL, it is even not " in-line with the PostgreSQL philosophy of forcing the user to make everything explicit" - as the exception was caught and ignored explicitly. So even this defense does not hold. Oracle in this case behaves much more user-friendly and (as for me) correctly - it leaves a choice to the developer.


You need to rollback. The JDBC Postgres driver is pretty bad. But if you want to keep your transaction, and just rollback that error, you can use savepoints:

try {
_stmt = connection.createStatement();
_savePoint = connection.setSavepoint("sp01");
_result = _stmt.executeUpdate(sentence) > 0;
} catch (Exception e){
 if (_savePoint!=null){
 connection.rollback(_savePoint);
}
}

Read more here:

http://www.postgresql.org/docs/8.1/static/sql-savepoint.html





infinispan