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

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads