postgresql and jdbc/hibernate batch updates

A thing I always forget is to turn of batch updates when using postgresql db. Postgresql is a nice open source SQL database. It is less known then mysql but it offers true ACID semantics. Anyway, it is my favorite choice for RDBMS. But you need to turn off batch updates if you are using hibernate or JPA ( which is also implemented by hibernate ). So make sure you turn it off with the property

hibernate.jdbc.batch_size and set the value to 0.

Otherwise you get an exception sounding like:

09:00:46,017 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
 at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
 at org.hibernate.ejb.AbstractEntityManagerImpl$1.beforeCompletion(AbstractEntityManagerImpl.java:515)
 at org.jboss.tm.TransactionImpl.doBeforeCompletion(TransactionImpl.java:1491)
 at org.jboss.tm.TransactionImpl.beforePrepare(TransactionImpl.java:1110)
 at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:324)
 at org.jboss.tm.TxManager.commit(TxManager.java:240)
...

Could not enlist in transaction on entering meta-aware object!

This errormessage provided by the Jboss j2ee server gave me a major headance. In a project we are using 2 datasources:

• an oracle datasource being used by iBatis DAO layer
• a postgresql datasource being used by JPA DAO layer

After invoking both DAO layers I got this nice SystemException by the JTA transactionmanager.

Investigation showed me ( a few hours later ) that the oracle datasource is not XA by default. You have to configure it to use the XADataSource. You can find a example in the jboss distribution in docs/examples/jca/oracle-xa-ds.xml