Prepared transactions with Postgres 8.4.3 on CentOS

Question

I have set 'max_prepared_transactions' to 20 in the local postgres.config and yet the transaction fails with the following error trace (but only on Linux). Since in Windows the same code works seamlessly I am wandering if this isn't an issue of permission. What would be the solution? Thanks Peter

372300 [Atomikos:7] WARN atomikos - XA resource 'XADBMS': rollback for XID '3137332E3230332E3132362E3139302E746D30303030313030303037:3137332E3230332E3132362E3139302E746D31' raised -3: the XA resource detected an internal error
org.postgresql.xa.PGXAException: Error rolling back prepared transaction
        at org.postgresql.xa.PGXAConnection.rollback(PGXAConnection.java:357)
        at com.atomikos.datasource.xa.XAResourceTransaction.rollback(XAResourceTransaction.java:873)
        at com.atomikos.icatch.imp.RollbackMessage.send(RollbackMessage.java:90)
        at com.atomikos.icatch.imp.PropagationMessage.submit(PropagationMessage.java:86)
        at com.atomikos.icatch.imp.Propagator$PropagatorThread.run(Propagator.java:62)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
        at java.lang.Thread.run(Thread.java:595)
Caused by: org.postgresql.util.PSQLException: ERROR: prepared transaction with identifier "1096044365_MTczLjIwMy4xMjYuMTkwLnRtMDAwMDEwMDAwNw==_MTczLjIwMy4xMjYuMTkwLnRtMQ==" does not exist
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:299)
        at org.postgresql.xa.PGXAConnection.rollback(PGXAConnection.java:347)

Answer 1

Edited to Help other who search for insight on the same symptoms

The error indicates that you're still surpassing your max_prepared_transactions limit.

Make sure that the config file you've edited is the one that is being used, and that you've told postgresql to reload its configuration to pick up your edited max_prepared_transactions.

You can query the database to find out what it is using for that setting with the SQL:

SHOW max_prepared_transactions;

Original Answer Follows (based on the assumption that max_prepared_transactions was set correctly):

---

Are you using setAutoCommit() at all? You may be experiencing this recently found bug:

http://archives.postgresql.org/pgsql-jdbc/2010-03/msg00013.php

This other post shows some small repeatable tests of preparing XA connections, which you could take a look at to see if you're doing anything similar:

http://archives.postgresql.org/pgsql-jdbc/2009-01/msg00025.php