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)

Surge

I've also checked the settings at /proc/sys/kernel. They all exceed the limits described at Postgresql manual 'Resource Consumption' – Surge

Can you run your command that is causing the issue from psql prompt on the linux box to get the java layer out of the way to see if it works? – Hamlett

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

Bunion

Thanks for replay. I have fixed the problem. The thing is that I've recently upgrade from Postgresql 8.1 to 8.4.3 to have support for distributed transaction. During that process, I have left a 'glitch' in the /etc/init.d/postgresql in that the app was still using the configuration from the old version at start. In the old configuration 'max_prepared_transactions=0' meaning no xtransaction with postgresql – Surge