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

我在JBoss 7.1.1 的server.log文件中看到以下stacktrace:


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日志文件,显示以下语句:


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

  • 未定义ISPN_MIXED_BINARY_TABLE_configCache

这个错误意味着什么,以及如何解决它?

时间:

我使用Java和PostgreSQL在表上进行插入时出现了这个错误,我演示如何重现此错误:


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

摘要:

出现此错误的原因是因为您输入了一个事务,并且您的一个SQL查询失败,吞噬了这个失败并忽略了它。你使用相同的连接,使用相同的事务运行另一个查询,异常在第二个时候被抛出,因为你使用了一个断开的事务来,默认情况下,PostgreSQL阻止你执行这个操作。

我使用: 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驱动程序是: postgresql-9.2-1000.jdbc4.jar

使用java版本:Java 1.7

下面是用于说明异常的createtable语句:


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语句失败,并且PostgreSQL将不会阻止你。

  2. 继续放在事务中,但是检测到第一个sql失败时,回滚/重新启动或重新启动事务,然后,你可以继续在该数据库连接上失败的多个sql查询。

  3. 不要捕获和忽略当sql语句失败时引发的异常,然后程序会在错误的查询上停止。

  4. 当你对事务内的连接失败并继续使用连接时,Oracle不会引发异常。

在语句产生的语句之前检查输出 current transaction is aborted这意味着数据库引发了代码忽略的异常,现在期望下一个查询返回一些数据。

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

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

 }
}

...