HeuristicMixedException with PostgreSQL

Just a quick post in case someone else ever encounters this issue. I got the following error when migrating an unchanged web application to CentOS 6.3 from an earlier version of CentOS:

2012-12-18 15:57:09,675 WARN [com.arjuna.ats.jta.logging.loggerI18N] (WorkManager(2)-7) [com.arjuna.ats.internal.jta.resources.arjunacore.preparefailed] [com.arjuna.ats.internal.jta.resources.arjunacore.preparefailed] XAResourceRecord.prepare - prepare failed with exception XAException.XAER_RMERR 2012-12-18 15:57:09,676 WARN [com.arjuna.ats.arjuna.logging.arjLoggerI18N] (WorkManager(2)-7) [com.arjuna.ats.arjuna.coordinator.BasicAction_36] - BasicAction.End() - prepare phase of action-id -53e50a38:d833:50d0d48d:bf failed. 2012-12-18 15:57:09,676 WARN [com.arjuna.ats.arjuna.logging.arjLoggerI18N] (WorkManager(2)-7) [com.arjuna.ats.arjuna.coordinator.BasicAction_38] - Action Aborting 2012-12-18 15:57:09,692 WARN [com.arjuna.ats.jta.logging.loggerI18N] (WorkManager(2)-7) [com.arjuna.ats.internal.jta.resources.arjunacore.rollbackxaerror] [com.arjuna.ats.internal.jta.resources.arjunacore.rollbackxaerror] XAResourceRecord.rollback - xa error XAException.XAER_RMERR 2012-12-18 15:57:09,693 WARN [com.arjuna.ats.arjuna.logging.arjLoggerI18N] (WorkManager(2)-7) [com.arjuna.ats.arjuna.coordinator.BasicAction_52] - Top-level abort of action -53e50a38:d833:50d0d48d:bf received heuristic decision: TwoPhaseOutcome.HEURISTIC_HAZARD 2012-12-18 15:57:09,703 ERROR [org.jboss.resource.adapter.jms.inflow.JmsServerSession] (WorkManager(2)-7) org.jboss.resource.adapter.jms.inflow.JmsServerSession@37515772 failed to commit/rollback javax.transaction.HeuristicMixedException at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1422) at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:135) at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:75) at org.jboss.resource.adapter.jms.inflow.JmsServerSession$XATransactionDemarcationStrategy.end(JmsServerSession.java:657) at org.jboss.resource.adapter.jms.inflow.JmsServerSession.run(JmsServerSession.java:243) at org.jboss.resource.work.WorkWrapper.execute(WorkWrapper.java:213) at org.jboss.util.threadpool.BasicTaskWrapper.run(BasicTaskWrapper.java:260) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662)

Checking the PostgreSQL logs I found:

ERROR: prepared transactions are disabled HINT: Set max_prepared_transactions to a nonzero value. STATEMENT: PREPARE TRANSACTION '131075_MS0tNTNlNTBhMzg6ZDgzMzo1MGQwZDQ4ZDo2ZQ==_LTUzZTUwYTM4OmQ4MzM6NTBkMGQ0OGQ6NzM='

The issue turns out to be that the new version of PostgreSQL does not have support for prepared statements turned on out of the box. To enable them, edit data/postgresql.conf as follows:

max_prepared_transactions = 10 # zero disables the feature

Don’t forget to restart PostgreSQL after changing the configuration.