Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 4E613633206
	for <pgsql-jdbc-postgresql.org@mail.postgresql.org>;
	Tue,  2 Mar 2010 10:29:31 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 97754-03 for
	<pgsql-jdbc-postgresql.org@mail.postgresql.org>;
	Tue,  2 Mar 2010 14:29:12 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smadev.internal.net (host3.dynacom.ondsl.gr [62.103.35.211])
	by mail.postgresql.org (Postfix) with ESMTP id A13C0633774
	for <pgsql-jdbc@postgresql.org>; Tue,  2 Mar 2010 10:29:19 -0400 (AST)
Received: from smadev.internal.net (localhost [127.0.0.1])
	by smadev.internal.net (8.14.2/8.14.2) with ESMTP id o22ET8Fl023531;
	Tue, 2 Mar 2010 16:29:08 +0200 (EET)
	(envelope-from achill@matrix.gatewaynet.com)
Received: from localhost (localhost [[UNIX: localhost]])
	by smadev.internal.net (8.14.2/8.14.2/Submit) id o22ET7tB023530;
	Tue, 2 Mar 2010 16:29:07 +0200 (EET)
	(envelope-from achill@matrix.gatewaynet.com)
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-jdbc@postgresql.org, jboss-user@lists.jboss.org
Subject: Very Dangerous bug in XA connection pooling and SL EJBs with
	jboss-4.2.3 & latest postgresql-8.4-701.jdbc3.jar
Date: Tue, 2 Mar 2010 16:29:06 +0200
User-Agent: KMail/1.9.7
References: <201003011731.15724.achill@matrix.gatewaynet.com>
In-Reply-To: <201003011731.15724.achill@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201003021629.07368.achill@matrix.gatewaynet.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.402 tagged_above=-10 required=5 tests=AWL=-0.662,
	BAYES_20=-0.74
X-Spam-Level: 
X-Archive-Number: 201003/1
X-Sequence-Number: 20341

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

I dont think that the usage of XA connections are critical for our case, so i can live with local connections (although i guess
i have a lot of garbage data since the above setup was deployed some 2 years ago.... imagine if i were selling open source to some bank)

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

-- 
Achilleas Mantzios