defaultAutoCommit problem with glassfish

["Douglas Hammond" <wizhippo(at)gmail(dot)com>]

I have been trying to setup a dataSource in glassfish that needs to
have the defaultAutoCommit = false using
org.postgresql.ds.PGConnectionPoolDataSource.

I am using the postgresql-jdbc-8.3dev-600 driver.

It does not work for me.  It seams that glassfish is relying on the
driver to set and retain the defaultAutoCommit state.  I do not know
if the driver should retian this or the pool manager should.

Here is what I have done to find that the postgres jdbc driver does
not retain the defaultAutoCommit state for new connections.  At least
I think this is what is happening.

Since you can't use org.postgresql.ds.PGConnectionPoolDataSource directly
This is what I did.

      <bean id="dsAdapter" destroy-method="close"
              class="org.postgresql.ds.PGConnectionPoolDataSource">
              <property name="serverName" value="localhost" />
              <property name="databaseName" value="jasperserver" />
              <property name="portNumber" value="5432" />
              <property name="user" value="jasperserver" />
              <property name="password" value="xxxx" />
              <property name="defaultAutoCommit" value="false" />
      </bean>

      <bean id="dataSource"
              class="org.apache.commons.dbcp.datasources.SharedPoolDataSource">
              <property name="connectionPoolDataSource">
                      <ref local="dsAdapter"></ref>
              </property>
              <property name="maxActive" value="5" />
              <property name="maxIdle" value="4" />
              <property name="maxWait" value="10000" />
      </bean>


This does NOT work

I thought I would try

      <bean id="dsAdapter" destroy-method="close"
              class="org.postgresql.ds.PGConnectionPoolDataSource">
              <property name="serverName" value="localhost" />
              <property name="databaseName" value="jasperserver" />
              <property name="portNumber" value="5432" />
              <property name="user" value="jasperserver" />
              <property name="password" value="hippo" />
      </bean>

      <bean id="dataSource"
              class="org.apache.commons.dbcp.datasources.SharedPoolDataSource">
              <property name="connectionPoolDataSource">
                      <ref local="dsAdapter"></ref>
              </property>
              <property name="defaultAutoCommit" value="false" />
              <property name="maxActive" value="5" />
              <property name="maxIdle" value="4" />
              <property name="maxWait" value="10000" />
      </bean>

I moved the defaultAutoCommit into the
org.apache.commons.dbcp.datasources.SharedPoolDataSource.

It WORKS.

It looks like postgresql jdbc driver is relying on the pool manager to
set or keep the autoCommit state.

I do not know if this is proper behavier or not.

Should this be submitted as a bug?