Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0D0EB6322EF
	for <pgsql-jdbc-postgresql.org@mail.postgresql.org>;
	Tue,  2 Mar 2010 15:22:32 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 27398-04 for
	<pgsql-jdbc-postgresql.org@mail.postgresql.org>;
	Tue,  2 Mar 2010 19:22:21 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og126.obsmtp.com (exprod7og126.obsmtp.com [64.18.2.206])
	by mail.postgresql.org (Postfix) with SMTP id 9EE66632D33
	for <pgsql-jdbc@postgresql.org>; Tue,  2 Mar 2010 15:22:20 -0400 (AST)
Received: from source ([209.85.219.214]) by exprod7ob126.postini.com
	([64.18.6.12]) with SMTP
	ID DSNKS41las/pd8ZRd1ExabEy/FtGTcPHuusP@postini.com;
	Tue, 02 Mar 2010 11:22:20 PST
Received: by ewy6 with SMTP id 6so496858ewy.37
	for <pgsql-jdbc@postgresql.org>; Tue, 02 Mar 2010 11:22:18 -0800 (PST)
Received: by 10.213.1.210 with SMTP id 18mr5050978ebg.18.1267557737827;
	Tue, 02 Mar 2010 11:22:17 -0800 (PST)
Received: from [192.168.1.117] ([88.195.103.165])
	by mx.google.com with ESMTPS id 24sm10311308eyx.2.2010.03.02.11.22.11
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Tue, 02 Mar 2010 11:22:13 -0800 (PST)
Message-ID: <4B8D6561.3040902@enterprisedb.com>
Date: Tue, 02 Mar 2010 21:22:09 +0200
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.22 (X11/20090706)
MIME-Version: 1.0
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
CC: pgsql-jdbc@postgresql.org, jboss-user@lists.jboss.org
Subject: Re: Very Dangerous bug in XA connection pooling and SL EJBs
	with jboss-4.2.3 & latest postgresql-8.4-701.jdbc3.jar
References: <201003011731.15724.achill@matrix.gatewaynet.com>
	<201003021629.07368.achill@matrix.gatewaynet.com>
In-Reply-To: <201003021629.07368.achill@matrix.gatewaynet.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/2
X-Sequence-Number: 20342

Achilleas Mantzios wrote:
> Introduction
> ========
> This bug has the potential effect of xactions which under certain condition should rollback, to behave
> as if autocommit is true.
> The workaround is to use local-tx-datasource with the standard driver org.postgresql.Driver instead of 
> xa-datasource with org.postgresql.xa.PGXADataSource.
> 
> The description below is with FreeBSD diablo-1.5 , but its exactly the same with SLES 10 SP1 and 1.5.0_15.
> 
> configuration
> ==============
> server/default/deploy/postgres-xa-ds.xml
> -------------------------------------------------------------------
> <datasources>
>    <xa-datasource>
>      <jndi-name>pgsql</jndi-name>
> 
>      <use-java-context>true</use-java-context>
> 
>      <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
>      <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
>      <xa-datasource-property name="PortNumber">5432</xa-datasource-property>
>      <xa-datasource-property name="DatabaseName">dynacom</xa-datasource-property>
> 
>      <xa-datasource-property name="User">postgres</xa-datasource-property>
>      <xa-datasource-property name="Password">xxxxx</xa-datasource-property>
>      <xa-datasource-property name="Compatible">8.2</xa-datasource-property>
> 
>      <track-connection-by-tx/>
>     <min-pool-size>1</min-pool-size>
>      <max-pool-size>2</max-pool-size>
> 
>      <metadata>
>          <type-mapping>PostgreSQL 8.0</type-mapping>
>      </metadata>
>    </xa-datasource>
> </datasources>
> 
> Description
> ========
> Consider the following test case:
> This code is executed:
> 	StatusHome sthome = (StatusHome) ic.lookup("java:comp/env/ejb/Status");
> 	Status stat = sthome.create();
> 
> "Status" is a SL Session EJB, which is defined to run with @ejb.transaction type = "Required"
> Whenever the above is executed, i see in pgsql log:
> 
> postgres [23247] 2010-03-02 16:04:09.377 EET line:8 LOG:  execute S_1: BEGIN
> postgres [23247] 2010-03-02 16:04:09.378 EET line:9 LOG:  execute <unnamed>: select ....
> postgres [23247] 2010-03-02 16:04:09.380 EET line:11 LOG:  execute S_2: COMMIT
> 
> which is correct behaviour. Then the following code (lets name it sql_xact) is executed:
> sql_xact:
> try {
> 	DataSource ds = (javax.sql.DataSource) ic.lookup("java:comp/env/jdbc/DynacomDB");   
> 	con = ds.getConnection();
> 	con.setAutoCommit(false);
> 	st = con.prepareStatement("select now();");
> 	rs = st.executeQuery();
> 	rs.close();
> 	st.close();
>     con.commit();
> }
> catch (Exception e) {
>     out.println("<BR>\n");
>     out.println("Error: " + e.getMessage());
>     con.rollback();
>     e.printStackTrace();
> }
> finally {
>     if (con != null) con.close();
> }
> 
> If the above is executed on the same postgresql backend and immediately after the previous (EJB call) block of code, i see in pgsql log:
> 
> postgres [23247] 2010-03-02 16:07:24.088 EET line:24 LOG:  execute <unnamed>: select now()
> 
> which means no begin-commit/rollback block. at this point a xaction supposed to rollback, is semi-commited.
> Now, If however, i execute this block of code:
> 
> try {
>     DataSource ds = (javax.sql.DataSource) ic.lookup("java:comp/env/jdbc/DynacomDB");   
>     con = ds.getConnection();
>     con.setAutoCommit(true);
>     
>     st = con.prepareStatement("select now();");
> 	rs = st.executeQuery();
> 	rs.close();
> 	st.close();
> 
> }
> catch (Exception e) {
>     out.println("<BR>\n");
>     out.println("Error: " + e.getMessage());
>     e.printStackTrace();
> }
> finally {
>     if (con != null) con.close();
> }
> 
> and then sql_xact code again, then the connection gets back to normal, pgsql log looking like:
> postgres [23247] 2010-03-02 16:11:57.729 EET line:39 LOG:  execute S_1: BEGIN
> postgres [23247] 2010-03-02 16:11:57.729 EET line:40 LOG:  execute <unnamed>: select now()
> postgres [23247] 2010-03-02 16:11:57.730 EET line:41 LOG:  execute S_2: COMMIT

> Any thoughts as to where the problem might reside? The jboss camp or the pgsql-jdbc camp?

Hard to tell. Can you make a complete reproducable test case and send
that over? It's a bit hard to see what exactly is going on without that.

And/or if you could set logLevel property to DEBUG, and send the full
log produced when you run the test test case with that, that might help.

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