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 theISPN_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?
Best Answer
I got this error using Java and PostgreSQL doing an insert on a table. I will illustrate how you can reproduce this error:
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:
Java program causes the error:
The above code produces this output for me:
Workarounds:
You have a few options:
Simplest solution: Don't be in a transaction. Set the
connection.setAutoCommit(false);
toconnection.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.
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.