How to disable AutoCommit in Postgresql data source?

One Star

How to disable AutoCommit in Postgresql data source?

Hi ,
I am using TOS 6.0 and facing an issue on Postgresql autocommit. 
I have setup a postgresql data source via PAX JDBC in Karaf and configuration file is:
osgi.jdbc.driver.class=org.postgresql.Driver-pool-xa
serverName=192.168.100.121
databaseName=MobileCRM
portNumber=5432
user=*******
password=*******
dataSourceName=MobileCRMDS
pool.maxIdle=5
pool.maxTotal=10
pool.minIdle=2

And in my Talend Job, I have tPostgresqlConnection to use this data source with "Auto Commit" unselected. 
However, after tPostgresqlOutput successfully inserts records, tPostgresqlCommit throw an exception:
karaf@trun()> Exception in component tPostgresqlCommit_1
org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:811)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

I tried to add xa.defaultAutoCommit to data source config file, but JDBC (org.postgresql.jdbc41:9.4.0.build-1200) failed to recognize this parameter with below exception:
18:28:42,339 | ERROR | f8-fcc56774aa6a) | ds.DbcpXAPooledDataSourceFactory   85 | 317 - org.ops4j.pax.jdbc.pool.dbcp2 - 0.7.0 | Error creating pooled datasourceUnsupported property name: xa.defaultAutoCommit
org.postgresql.util.PSQLException: Unsupported property name: xa.defaultAutoCommit
at org.postgresql.ds.common.BaseDataSource.setProperty(BaseDataSource.java:993)
at org.postgresql.osgi.PGDataSourceFactory.configureBaseDataSource(PGDataSourceFactory.java:90)
at org.postgresql.osgi.PGDataSourceFactory.createXADataSource(PGDataSourceFactory.java:167)
One Star

Re: How to disable AutoCommit in Postgresql data source?

OPS4J PAX JDBC documentation has issues....it says config file should use "ka." prefix for defaultAutoCommit, however, it should be "factory."
After I correct config file as below, data source can be created
osgi.jdbc.driver.class=org.postgresql.Driver-pool-xa
serverName=192.168.100.121
databaseName=MobileCRM
portNumber=5432
user=mobilecrmuser
password=*******
dataSourceName=MobileCRMDS
pool.maxIdle=5
pool.maxTotal=10
pool.minIdle=2
factory.defaultAutoCommit=false

 however i still get same autoCommit is enabled exception.
karaf@trun()> Exception in component tPostgresqlCommit_1
org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:811)
One Star

Re: How to disable AutoCommit in Postgresql data source?

I believe this is a bug in TOS 6.0 now.
Here is a part of codes from tPostgresqlConnection:
				java.sql.Connection conn_tPostgresqlConnection_1 = null;
if ((null == globalMap.get(KEY_DB_DATASOURCES))
|| "".equals("MobileCRMDS")) {
String driverClass_tPostgresqlConnection_1 = "org.postgresql.Driver";
java.lang.Class
.forName(driverClass_tPostgresqlConnection_1);
conn_tPostgresqlConnection_1 = java.sql.DriverManager
.getConnection(url_tPostgresqlConnection_1,
dbUser_tPostgresqlConnection_1,
dbPwd_tPostgresqlConnection_1);
globalMap.put("conn_tPostgresqlConnection_1",
conn_tPostgresqlConnection_1);
}
if (null != conn_tPostgresqlConnection_1) {
conn_tPostgresqlConnection_1.setAutoCommit(false);
}
globalMap.put("schema_" + "tPostgresqlConnection_1",
(String) globalMap.get("DBSchema"));
globalMap.put("conn_" + "tPostgresqlConnection_1",
conn_tPostgresqlConnection_1);

We can find this component does not create connection when there's a valid data source, therefore, other tPostgresql**** component will try to obtain a new connection from matched data source in globalMap, so we will lose control on transaction because each DB component might using different DB connections. 
I raised one issue in Jira TESB-17097