Tuesday, April 23, 2013

Spring (3.0.5) + Transactions + postgreSQL/GreenPlum

The long and the short of it is that I am getting an exception when using declarative transactions with Spring + postgreSQL (GreenPlum variant): org.postgresql.util.PSQLException: Cannot change transaction read-only property in the middle of a transaction.

My stack is:
Java 1.7.0_17
Tomcat 7.0
Spring 3.0.5*
Apache DBCP 1.4
postgresql-9.0-801.jdbc4.jar
GreenPlum 4.2.2.4 (Commercial postgreSQL variant)
* I get this issue whether using declarative OR programmatic tx mgmt

My error is:
DEBUG o.s.j.d.DataSourceTransactionManager - Initiating transaction rollback
DEBUG o.s.j.d.DataSourceTransactionManager - Rolling back JDBC transaction on Connection [jdbc:postgresql://10.110.62.245/data, UserName=app_reports, PostgreSQL Native Driver]
DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization ended with unknown status for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2db1692e]
DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2db1692e]
DEBUG o.s.jdbc.datasource.DataSourceUtils - Resetting read-only flag of JDBC Connection [jdbc:postgresql://10.110.62.245/data, UserName=app_reports, PostgreSQL Native Driver]
DEBUG o.s.jdbc.datasource.DataSourceUtils - Could not reset JDBC Connection after transaction
org.postgresql.util.PSQLException: Cannot change transaction read-only property in the middle of a transaction.
 at org.postgresql.jdbc2.AbstractJdbc2Connection.setReadOnly(AbstractJdbc2Connection.java:617) ~[postgresql-9.0-801.jdbc4.jar:na]
 at org.apache.commons.dbcp.DelegatingConnection.setReadOnly(DelegatingConnection.java:377) ~[commons-dbcp-1.4.jar:1.4]
 at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setReadOnly(PoolingDataSource.java:338) ~[commons-dbcp-1.4.jar:1.4]
I will update this post with the pertinent configs/code but simply, I am using a VERY basic code example and ALSO there are numerous hits on google with the exact same issue. I highly suspect it is isolated to GreenPlum but may also be isolated to GreenPlum+DBCP. The problem is, none of the web posts have a solution... if you do, please comment on my post! :) Thanks