I order to support PostgreSQL reconnecting after failover when using client side node selection I replaced C3P0 with HikariCP. HikariCP is an actively maintained, Apache licensed connection pool with substantially better performance and better resilience to failure (DB disconnects, etc.) then C3P0. Read more about it here: https://github.com/brettwooldridge/HikariCP (including comparison and benchmarks with C3P0). During the development of HikariCP a lot of care was spend on making HikariCP behave as correctly and predictable as possible.
Let me know what you think!
Aside from HikariCP the following changes were made:
Cleaned up the getCachedConnection method. With HikariCP there is no need to retry to obtain a connection since getting an connection will block until a free connection is available or until a timeout is reached (default 30 seconds) at which point an SQLException is thrown. This also removed calling Runtime.getRuntime().runFinalization();. HikariCP is currently configured to detect / log leaks when a connection hasn't returned to the pool for longer then 5 minutes.
Initialization of the datasource must happen in the getDataSource() method because at object construction not all JDBC config is known. However this method could (as far as I could tell) be called concurrently from multiple threads but had no mechanism to prevent initializing the DB pool multiple times. The variable in which the pool itself was stored (m_ds) also was not marked volatile or immutable which could lead to visibility issues. Instead of lazy initialization of the pool in the getDataSource() method the pool could probably better be initialized at object construction. However I wasn't able to achieve that without breakage therefor I made the initialization mechanism work correctly with concurrent invocations.
The MaxStatementsPerConnection option was removed because HikariCP doesn't support it.
reopening to set the defaults for oracle same as they were on c3p0
Jasper Siepkes
October 16, 2023 at 5:42 AM
@Hiep Lq I never tried with iDempiere to use HikariCP as an OSGi bundle. I know from my own experience (with another application) that HikariCP and the PostgreSQL JDBC driver can work as OSGi bundles.
Hiep Lq
October 16, 2023 at 5:07 AM
i try move HikariCP to osgi bundle success for postgres by move postgres to osgi also (postgres jdbc lib also ready for osgi)
oracle jdbc isn’t ready for osgi maybe org.compiere.db.oracle.provider need to export something to work
Hiep Lq
October 16, 2023 at 2:50 AM
@Jasper Siepkes i saw HikariCP is a bundle bundle but moment use as pure java do you try use it as osgi bundle?
Copying from https://github.com/idempiere/idempiere/pull/926
Mattermost discussion:
https://mattermost.idempiere.org/idempiere/pl/fxubzbobxtnt7czaxor8z7bf6r