Re: XAResource implementation - Mailing list pgsql-jdbc

Hello Heikki,

Many thanks for your answer and for the time you took to do some
testing ! As you can see I put Ludovic Orban as Cc because he is the BTM
developer and he seems to be interested to this issue too...

> 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.
>

Ah Yes, you're right, your transaction just looks perfect !

> 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

On my side, I tryed the following example:
http://docs.codehaus.org/download/attachments/9240687/HibernateBTM.zip?version=2

(see attachement to see my modifications)

and my pg logs look like this for the following code:
userTransaction.begin();
loopCountQuery(sf); //many select count queries
userTransaction.commit();


2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 6'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 7'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 8'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 9'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 10'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT

When I use the pure JDBC driver (not the XA implementation), I don't
have this behaviour (BEGIN and COMMIT are related to userTransaction
from application level) and not anymore around each query:

2007-11-13 08:19:55 CET LOG:  execute S_1: BEGIN
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 0'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 1'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 2'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 3'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 4'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 5'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 6'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 7'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 8'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 9'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 10'
2007-11-13 08:19:55 CET LOG:  execute S_2: COMMIT


As I'm not a JTA and JDBC expert at all, I'm not able to provide you
more information, I'm sorry about that... I hope this behaviour is not
coming from my miss-understanding !


Thanks for your help !

Regards,

Joël

On Mon, 2007-11-12 at 09:59 +0000, Heikki Linnakangas wrote:
> 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
>