topic Re: How to disable AutoCommit in Postgresql data source? in Talend Studio

How to disable AutoCommit in Postgresql data source?

Anonymous — Sat, 16 Nov 2024 10:47:11 GMT

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)

Re: How to disable AutoCommit in Postgresql data source?

Anonymous — Mon, 22 Feb 2016 15:21:18 GMT

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)

Re: How to disable AutoCommit in Postgresql data source?

Anonymous — Tue, 23 Feb 2016 07:02:37 GMT

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