Re: XAResource implementation - Mailing list pgsql-jdbc

joël Winteregg wrote:
> I said that BTM TransactionManager was "mostly" working because when I
> was using the XA implementation, SQL BEGIN and COMMIT where generated
> arround each SQL query (even if several queries were located inside the
> same transaction). So at the end, I used the TransactionManager with the
> "Last Resource Commit Optimization" which allow the use of a pure jdbc
> driver during transaction (I only have a single DB (1 Phase Commit) so
> it is 100% safe to use this specificity). In this case, my BEGIN and
> COMMIT statement where just perfect:
> BEGIN
>   SELECT ..
>   SELECT ..
>   SELECT ..
> COMMIT
>
> So I was just wondering if my XA problem (BEGIN and COMMIT location when
> using XA) could come from the postgresql XA driver ?

Hmm. I downloaded the BTM newUserDemo.zip and modified it to run with
Postgres, and to run two queries in same transaction. Works for me.

Attached is the modified Test.java I used. To run:
0. Download newUserDemo.zip from
http://docs.codehaus.org/display/BTM/NewUserGuide
1. Put postgresql.jar in newUserDemo/lib
2. Copy the attached Test.java to newUserDemo/src/jtatest
3. Modify database/username/password in Test.java if necessary
4. Run the CREATE TABLE from derby-create.sql

Here's what I get in the Postgres log, with log_statements='all':

LOG:  execute <unnamed>: SELECT gid FROM pg_prepared_xacts
LOG:  execute S_1: BEGIN
LOG:  execute <unnamed>: insert into messages(content) values ($1)
DETAIL:  parameters: $1 = 'hello, world!'
LOG:  execute S_2: COMMIT
LOG:  execute S_1: BEGIN
LOG:  execute <unnamed>: select content from messages
LOG:  execute <unnamed>: select content from messages
LOG:  execute S_2: COMMIT

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
package jtatest;

import bitronix.tm.TransactionManagerServices;
import bitronix.tm.resource.jdbc.PoolingDataSource;

import javax.transaction.UserTransaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

public class Test {

    public static void main(String[] args) throws Exception {
        String who = "world";
        if (args.length > 0)
            who = args[0];

        PoolingDataSource derby1Ds = new PoolingDataSource();
        derby1Ds.setClassName("org.postgresql.xa.PGXADataSource");
        derby1Ds.setUniqueName("derby1");
        derby1Ds.setPoolSize(3);
        derby1Ds.getDriverProperties().setProperty("user", "postgres");
        derby1Ds.getDriverProperties().setProperty("databaseName", "postgres");
        derby1Ds.init();

        UserTransaction ut = TransactionManagerServices.getTransactionManager();
        ut.begin();
        try {
            Connection c = derby1Ds.getConnection();

            PreparedStatement stmt = c.prepareStatement("insert into messages(content) values (?)");
            stmt.setString(1, "hello, " + who + "!");
            stmt.executeUpdate();
            stmt.close();

            c.close();
            ut.commit();
        } catch (SQLException ex) {
            ex.printStackTrace();
            ut.rollback();
        }

        ut.begin();
        try {
            Connection c = derby1Ds.getConnection();

            PreparedStatement stmt = c.prepareStatement("select content from messages");
            ResultSet rs = stmt.executeQuery();
            while(rs.next())
                System.out.println(rs.getString(1));
            rs.close();
            stmt.close();

        /* Run another query */
             stmt = c.prepareStatement("select content from messages");
             rs = stmt.executeQuery();
            while(rs.next())
                System.out.println(rs.getString(1));
            rs.close();
            stmt.close();

            c.close();
            ut.commit();
        } catch (SQLException ex) {
            ex.printStackTrace();
            ut.rollback();
        }

        derby1Ds.close();
    }
}