我得到这个错误使用Java和PostgreSQL做一个表上的插入。我将说明如何重现这个错误:

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

简介:

您得到这个错误的原因是因为您输入了一个事务，其中一个SQL查询失败了，您接受了这个失败并忽略了它。但这还不够，然后使用相同的连接，使用相同的TRANSACTION运行另一个查询。在第二个格式正确的查询上抛出异常，因为您正在使用一个损坏的事务来执行额外的工作。PostgreSQL默认阻止你这样做。

我使用的是:PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu，由gcc (gcc) 4.7.2 20120921 (Red Hat 4.7.2-2)编译，64位”。

我的PostgreSQL驱动是:PostgreSQL -9.2-1000.jdbc4.jar

使用Java版本:Java 1.7

下面是表创建语句来说明异常:

CREATE TABLE moobar
(
    myval   INT
);

Java程序导致错误:

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

}

上面的代码为我产生了以下输出:

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

解决方法:

你有几个选择:

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. 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. Don't catch and ignore the Exception that is thrown when a SQL statement fails. Then the program will stop on the malformed query. 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.

为了捍卫PostgreSQL这样做的决定…神谕让你在中间变得软弱，让你做一些愚蠢的事情，而忽略它。

在我的情况下，我得到这个错误，因为我的文件是腐败的。而迭代文件的记录，它给我同样的错误。

也许将来它对任何人都有帮助。这是我发布这个答案的唯一原因。

我认为最好的解决方案是使用java.sql.Savepoint。

在执行可能引发SQLException的查询之前，使用Connection.setSavepoint()方法，如果抛出异常，则只回滚到该保存点，而不是整个事务。

示例代码:

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

   }
}

在postgresql JDBC驱动程序上做了一些工作，与此行为相关: 参见https://github.com/pgjdbc/pgjdbc/pull/477

现在可以通过设置autosave=always in the connection(参见https://jdbc.postgresql.org/documentation/head/connect.html)来避免“当前事务已终止”综合征。由于处理语句执行周围的保存点而产生的开销非常低(详情请参阅上面的链接)。

我得到这个错误使用Java和PostgreSQL做一个表上的插入。我将说明如何重现这个错误:

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

简介:

您得到这个错误的原因是因为您输入了一个事务，其中一个SQL查询失败了，您接受了这个失败并忽略了它。但这还不够，然后使用相同的连接，使用相同的TRANSACTION运行另一个查询。在第二个格式正确的查询上抛出异常，因为您正在使用一个损坏的事务来执行额外的工作。PostgreSQL默认阻止你这样做。

我使用的是:PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu，由gcc (gcc) 4.7.2 20120921 (Red Hat 4.7.2-2)编译，64位”。

我的PostgreSQL驱动是:PostgreSQL -9.2-1000.jdbc4.jar

使用Java版本:Java 1.7

下面是表创建语句来说明异常:

CREATE TABLE moobar
(
    myval   INT
);

Java程序导致错误:

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

}

上面的代码为我产生了以下输出:

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

解决方法:

你有几个选择:

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. 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. Don't catch and ignore the Exception that is thrown when a SQL statement fails. Then the program will stop on the malformed query. 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.

为了捍卫PostgreSQL这样做的决定…神谕让你在中间变得软弱，让你做一些愚蠢的事情，而忽略它。

出现这个错误的原因是之前有其他数据库的错误操作导致当前数据库的操作无法进行(我使用谷歌翻译将我的中文翻译成英文)