postgresql - JackRabbit OAK：在 postgresql 中生成空闲 DB 事务

Question

我们在我们的项目中使用 JackRabbit OAK 实现与 postgresql DB 集成。下面是使用 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");
    }
}

我们的应用程序运行良好，但是当我们检查 postgresql 数据库统计信息时，我们可以看到在数据库端创建了许多空闲或空闲的事务连接。

 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

如何避免 JackRabbit 橡木实现中的这种空闲连接？我们可以直接杀死那些空闲的连接吗？

标签： postgresqljcrjackrabbitjackrabbit-oak