Re: Problem with committing in XA mode

Vlastimil Havranek wrote:
> yes the log_min_messages is set to notice. I've just rechecked it.

Ok, thanks.

One theory is that WebLogic, or your application code, is calling 
setAutoCommit on the XA connection. I can reproduce a similar error with 
a stand-alone test case by doing that:

         XAConnection xaconn1 = ds.getXAConnection();
         XAResource xares1 = xaconn1.getXAResource();
         Connection conn1 = xaconn1.getConnection();

         xares1.start(initxid, XAResource.TMNOFLAGS);
         Statement stmt = conn1.createStatement();
         stmt.executeUpdate("INSERT INTO foo VALUES (1234)");
         // 1. conn1.setAutoCommit(true);
         xares1.end(initxid, XAResource.TMSUCCESS);
         // 2. conn1.setAutoCommit(true);
         xares1.prepare(initxid);
         xares1.commit(initxid, false);

Uncommenting either of the commented lines will cause a similar error as 
you're seeing. Since the end() call is made by WebLogic, the 1st case 
would be an explicit setAutoCommit call in your application. You don't 
have one, do you? That's forbidden by the JTA spec.

The 2nd call would be a call made by WebLogic. We've seen WebLogic do 
strange call patterns before (see 
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php), so I 
wouldn't be surprised if it was doing that. It's allowed by the JTA 
spec, but not by the PostgreSQL driver because we don't support 
transaction interleaving. We could try modifying the driver to tolerate 
it like the aforementioned "end then join" case, by not passing the 
setAutoCommit call immediately to the underlying connection object, but 
delay it until prepare-call. Whether that would work depends on what 
else the application server is trying to do with the connection between 
end and prepare.

Calling commit or rollback at those points will also trigger the same 
error. JTA spec also forbids setSavePoint, but I'm not sure if that 
would actually be a problem for PostgreSQL.

There's some options in WebLogic to dumb-down the interaction with the 
JDBC XA driver, see 
http://edocs.bea.com/wls/docs100/ConsoleHelp/pagehelp/JDBCjdbcdatasourcesjdbcdatasourceconfigtransactiontitle.html. 
At least "Keep Connection Open On Release" is required for PostgreSQL 
driver, and setting the other settings marked with "Use this setting to 
work around specific problems with JDBC XA drivers" might help too. 
Please make sure you have those set.


Now, it would be very good if we threw an error immediately at the 
offending call, regardless of whether it's allowed by JTA or not. I've 
been trying to figure out how to do that. We could create a proxy for 
the Connection object handed to the application in PGXAConnection, and 
throw an error if you call one of those functions while an 
XA-transaction is open. That would be fairly simple, but wouldn't catch 
the pattern:

start(xid)
// do stuff in XA transaction
executeUpdate("UPDATE ...");
end(xid)
// do stuff outside the XA transaction
executeUpdate("UPDATE ...");
prepare(xid);

It would be better than nothing, but it would be even better if we could 
catch all the cases we can't handle properly. If we could inject a proxy 
to check that at lower level, around QueryExecutor, it would be more 
robust, but I don't see any easy way to do that.


Another check that we really ought to do is to check the command tag 
that PREPARE TRANSACTION returns. If it returns ROLLBACK, the driver 
should throw an error. I'll write a patch for that.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com