We are using JackRabbit OAK implementation in our Project with postgresql DB integration. Below is the code snippets for repository creation using postgresql.
private static Repository createRepo(final Map<String, String> dbDetails)
throws DataStoreException {
try {
final RDBOptions options =
new RDBOptions().tablePrefix(dbDetails.get(DB_TABLE_PREFIX)).dropTablesOnClose(
false);
final DataSource ds =
RDBDataSourceFactory.forJdbcUrl(
dbDetails.get("db_URL"),
dbDetails.get("db_User"),
dbDetails.get("db_Password"));
final Properties properties = buildS3Properties(dbDetails);
final S3DataStore s3DataStore = buildS3DataStore(properties);
final DataStoreBlobStore dataStoreBlobStore = new DataStoreBlobStore(s3DataStore); // NOSONAR
final Whiteboard wb = new DefaultWhiteboard();
bapRegistration =
wb.register(
BlobAccessProvider.class,
(BlobAccessProvider) dataStoreBlobStore,
properties);
documentNodeStore =
new RDBDocumentNodeStoreBuilder()
.setBlobStore(dataStoreBlobStore)
.setBundlingDisabled(true)
.setRDBConnection(ds, options)
.build();
repository = new Jcr(documentNodeStore).with(wb).createRepository();
return repository;
} catch (final DataStoreException e) {
log.error("Error." + e);
throw new DataStoreException("Error");
}
}
Our application is running fine, but when we are checking postgresql DB statistics, we could see lots of idle or idle in transactions connection are getting created at DB end.
xxxxx | 6 days 05:56:12.75259 | select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, VERSION, SDTYPE, SDMAXREVTIME, DATA, BDATA from mango_NODES where SDTYPE in ($1, $2, $3) and SDMAXREVTIME <= $4 and VERSION >= $5 | idle in transaction
xxxxx | 5 days 22:05:38.855597 | select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, VERSION, SDTYPE, SDMAXREVTIME, DATA, BDATA from mango_NODES where DELETEDONCE = $1 and MODIFIED < $2 and MODIFIED >= $3 | idle in transaction
xxxxx | 25 days 23:02:38.845462 | SET application_name = 'PostgreSQL JDBC Driver' | idle
xxxxx | 25 days 23:02:38.829477 | SET application_name = 'PostgreSQL JDBC Driver' | idle
xxxxx | 5 days 06:21:58.12896 | COMMIT | idle
xxxxx | 6 days 15:15:29.972181 | select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, VERSION, SDTYPE, SDMAXREVTIME, DATA, BDATA from mango_NODES where SDTYPE in ($1, $2, $3) and SDMAXREVTIME <= $4 and VERSION >= $5 | idle in transaction
xxxxx | 6 days 19:27:06.133048 | select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, VERSION, SDTYPE, SDMAXREVTIME, DATA, BDATA from mango_NODES where DELETEDONCE = $1 and MODIFIED < $2 and MODIFIED >= $3 | idle in transaction
xxxxx | 6 days 12:17:47.585068 | select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, VERSION, SDTYPE, SDMAXREVTIME, DATA, BDATA from mango_NODES where DELETEDONCE = $1 and MODIFIED < $2 and MODIFIED >= $3 | idle in transaction
xxxxx | 1 day 20:54:32.433188 | SET application_name = 'PostgreSQL JDBC Driver'
postgres xxxxx 4210 1 Nov18 ? 01:51:26 postgres: main: cms cms_prod 10.x.x.x(xxxxx) idle in transaction
postgres xxxxx 4210 0 Nov18 ? 00:42:05 postgres: main: cms cms_prod 10.x.x.x(xxxxx) idle in transaction
postgres xxxxx 4210 3 Nov18 ? 06:18:13 postgres: main: cms cms_prod 10.x.x.x(xxxxx) idle
postgres xxxxx 4210 3 Nov18 ? 05:46:57 postgres: main: cms cms_prod 10.x.x.x(xxxxx) idle
postgres xxxxx 4210 0 Nov18 ? 00:24:22 postgres: main: cms cms_prod 10.x.x.x(xxxxx) idle in transaction
postgres xxxxx 4210 0 Nov18 ? 00:08:08 postgres: main: cms cms_prod 10.x.x.x(xxxxx) idle in transaction
postgres xxxxx 4210 0 Nov18 ? 00:27:06 postgres: main: cms cms_prod 10.x.x.x(xxxxx) idle in transaction
postgres xxxxx 4210 0 Nov17 ? 00:00:00 postgres: main: cms cms_prod 10.xx.xxx.xx(xxxxx) idle
How to avoid this idle connections in JackRabbit oak implementation? Can we directly kill those idle connections?