postgresql PSQLException: 当前事务被中止，命令被忽略，直到事务块结束

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

提问by Jimidy

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

我在 JBoss 7.1.1 Final 的 server.log 文件中看到以下（截断的）堆栈跟踪：

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:

检查 Postgres 日志文件会发现以下语句：

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.

我正在使用 JBoss 7.1.1 Final 附带的 Infinispan，即 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 TABLEstatement.
• 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.

• Infinispan 尝试运行该SELECT count(*)...语句以查看 ; 中是否有任何记录ISPN_MIXED_BINARY_TABLE_configCache。
• 出于某种原因，Postgres 不喜欢这种说法。
• Infinispan 忽略了这一点并继续CREATE TABLE发表声明。
• Postgres barfs 因为它仍然认为它是同一个事务，Infinispan 没有回滚它，并且这个事务是从第一个SELECT count(*)...语句开始的。

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

这个错误是什么意思以及如何解决它？

回答by Eric Leschinski

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

我使用 Java 和 postgresql 在表上插入时遇到此错误。我将说明如何重现此错误：

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.

您收到此错误的原因是因为您输入了一个事务并且其中一个 SQL 查询失败，而您吞噬了该失败并忽略了它。但这还不够，然后您使用相同的连接，使用 SAME TRANSACTION 运行另一个查询。在第二个正确形成的查询中抛出异常，因为您正在使用损坏的事务来执行其他工作。默认情况下，Postgresql 会阻止您执行此操作。

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

我正在使用：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

我的 postgresql 驱动程序是：postgresql-9.2-1000.jdbc4.jar

Using java version:Java 1.7

使用java版本：Java 1.7

Here is the table create statement to illustrate the Exception:

这是用于说明异常的 table create 语句：

CREATE TABLE moobar
(
    myval   INT
);

Java program causes the error:

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

}

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.

1. 最简单的解决方案：不要参与交易。将 设置connection.setAutoCommit(false);为connection.setAutoCommit(true);。它之所以有效，是因为失败的 SQL 只是作为失败的 sql 语句被忽略。欢迎您随意使 sql 语句失败，postgresql 不会阻止您。

2. 保持在事务中，但是当您检测到第一个 sql 失败时，回滚/重新启动或提交/重新启动事务。然后，您可以根据需要继续在该数据库连接上失败尽可能多的 sql 查询。

3. 不要捕获并忽略 sql 语句失败时抛出的异常。然后程序将在格式错误的查询上停止。

4. 而是获取 Oracle，当您在事务中的连接上的查询失败并继续使用该连接时，Oracle 不会引发异常。

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

在PostgreSQL的决定，这样做事的防御......甲骨文是使你的软你做愚蠢的东西中间出租，可俯瞰它。

回答by vyegorov

Check the output beforethe statement that caused current transaction is aborted. This typically means that database threw an exception that your code had ignored and now expecting next queries to return some data.

检查输出之前导致该声明current transaction is aborted。这通常意味着数据库抛出了一个您的代码忽略的异常，现在期待下一个查询返回一些数据。

So you now have a state mismatch between your application, which considers things are fine, and database, that requires you to rollback and re-start your transaction from the beginning.

因此，您的应用程序（认为一切正常）和数据库（需要您回滚并从头开始重新启动事务）之间存在状态不匹配。

You should catch all exceptions and rollback transactions in such cases.

在这种情况下，您应该捕获所有异常并回滚事务。

Here's a similar issue.

这是一个类似的问题。

回答by Micha? Orliński

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

我认为最好的解决方案是使用 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.

在执行可以抛出 SQLException 的查询之前，使用方法 Connection.setSavepoint() 并且如果抛出异常，您只能回滚到此保存点而不回滚所有事务。

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

   }
}

回答by thedanotto

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.

在 Ruby on Rails PG 中，我创建了一个迁移，迁移了我的数据库，但忘记重新启动我的开发服务器。我重新启动了我的服务器并且它工作了。

回答by thierry masson

There's been some work done on the postgresql JDBC Driver, related to this behaviour:
see https://github.com/pgjdbc/pgjdbc/pull/477

在 postgresql JDBC 驱动程序上已经完成了一些与此行为相关的工作：
请参阅https://github.com/pgjdbc/pgjdbc/pull/477

It is now possible, by setting

现在可以通过设置

autosave=always

在连接中（见 https://jdbc.postgresql.org/documentation/head/connect.htmlhttps://jdbc.postgresql.org/documentation/head/connect.html）以避免“当前事务中止”综合症。


由于处理语句执行周围的保存点而导致的开销非常低（有关详细信息，请参见上面的链接）。

回答by u7235046

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）

出现这个错误的原因是之前有其他数据库操作错误导致当前数据库操作无法进行（我用google翻译把我的中文翻译成英文）

回答by S.Perera

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.

我遇到了同样的问题，但后来意识到数据库中有一个同名的表。删除后，我能够导入文件。

回答by Dan Berindei

The issue has been fixed in Infinispan 5.1.5.CR1: ISPN-2023

该问题已在 Infinispan 5.1.5.CR1: ISPN-2023 中得到修复

回答by Mariano L

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:

你需要回滚。JDBC Postgres 驱动程序非常糟糕。但是如果你想保留你的事务，只是回滚那个错误，你可以使用保存点：

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

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

回答by al0

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.

这是 PostgreSQL 非常奇怪的行为，它甚至不“符合 PostgreSQL 强制用户将所有内容都明确化的哲学”——因为异常被明确地捕获并忽略。所以即使是这种防御也不成立。在这种情况下，Oracle 的行为更加用户友好并且（就我而言）正确 - 它为开发人员留下了选择。