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

问题描述

我在 JBoss 7.1.1 Final 的 server.log 文件中看到以下(截断的)堆栈跟踪:

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

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

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

我使用的是 JBoss 7.1.1 Final 附带的 Infinispan，即 5.1.2.Final.

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 尝试运行 SELECT count(*)... 语句以查看 ISPN_MIXED_BINARY_TABLE_configCache 中是否有任何记录；
• 出于某种原因，Postgres 不喜欢这种说法.
• Infinispan 忽略了这一点并继续使用 CREATE TABLE 语句.
• Postgres barfs 因为它仍然认为它是同一个事务，Infinispan 没有回滚它，并且这个事务是从第一个 SELECT count(*)... 语句开始的.

• 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?

推荐答案

我在使用 Java 和 PostgreSQL 在表上执行插入时遇到此错误.我将说明如何重现此错误:

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

总结:

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

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.

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

解决方法:

您有几个选择:

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

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.

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

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.

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

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

改为获取 Oracle，当您对事务中的连接的查询失败并继续使用该连接时，Oracle 不会抛出异常.

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 以这种方式做事的决定辩护...... Oracle 正在让你在中间变得柔软，让你做愚蠢的事情并忽略它.

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.

这篇关于PSQLException: 当前事务被中止，命令被忽略直到事务块结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！