How to disable AutoCommit in Postgresql data source?

Five Stars

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)
Five Stars

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)
Five Stars

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

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read