Reset (autoCommit) on connection

Users
1

hi XTDB team, I’m upgrading XTDB from 1.20 to the latest 1.24. After the upgrade, when I start a node, I find the log flushing with below message:

2023-08-18 12:59:47.687 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:47.798 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:47.910 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.024 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.134 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.246 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.356 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.465 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.574 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.685 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.795 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:48.905 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a
2023-08-18 12:59:49.014 [xtdb-tx-subscription-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1352cd6a

With some experiments, I also found that this behavior starting from 1.22.0 (In my test, I only upgrade xtdb version from 1.21.0 to 1.22.0, above issue happened).

This is my config.edn:

{:xtdb.jdbc/connection-pool {
                             :dialect {
                                       :xtdb/module xtdb.jdbc.psql/->dialect
                              },
                             :db-spec {
                                       :jdbcUrl "jdbc:postgresql://xxxxx"
                             },
},
 :xtdb.rocksdb/block-cache {:xtdb/module xtdb.rocksdb/->lru-block-cache
			    :cache-size 536870912},
 :xtdb/index-store {:kv-store {:xtdb/module xtdb.rocksdb/->kv-store
                               :db-dir "/var/tmp/txs"
                               :block-cache :xtdb.rocksdb/block-cache}},
:xtdb/document-store {:xtdb/module xtdb.jdbc/->document-store,
:connection-pool :xtdb.jdbc/connection-pool},
:xtdb/tx-log {:xtdb/module xtdb.jdbc/->tx-log,
:connection-pool :xtdb.jdbc/connection-pool}}

Then if I set autoCommit to false in pool-opts, above log message disappear:

:pool-opts { :autoCommit false },

My question is:

  1. What does it mean for the message and is it a problem?
  2. Can I set autoCommit to false and is there any concern?

Thanks,
-BS

1 Like
2

image
image1602×745 109 KB

Looks like it’s caused by this change?

3

Hey @blshao84 thanks for raising this. I don’t understand the details enough right now to give an accurate answer, but we’ll investigate and confirm soon,

Jeremy

cc @wotbrew

4

Thanks Jeremy! Let me know, if you guys need anything else

5

Hello @blshao84,

First of all these messages should not cause you concern, it means the auto commit state of the connection is reset to its original value when returning to the pool (otherwise the auto commit state of an connection from a pool would be a bit of a lottery). When Hikari does this it logs a message as DEBUG.

Still, we can reset the state for Hikari and avoid the log message.

I’ve raised an issue here: For postgres connection pools when changing autocommit, change back before release to reduce DEBUG logs generated by hikari · Issue #2762 · xtdb/xtdb · GitHub, this should make it into the next XTDB release (its a simple change).

Thanks again for the report :pray: