The reWriteBatchedInserts setting is not read properly when the DataSource is created by the connection pool

[mryan43]

I'm using postgresql 11.11 (driver 42.2.19) with tomcat-jdbc 10.0.12 for pooling, hibernate 5.4.28.Final and hypersistence optimizer 2.5.1. I also have another home-made jdbc proxy driver that we use at my org to detect some anti-patterns (similar to hypersistence, but with less features).

I'm enabling "reWriteBatchedInserts" at the driver level by passing the property to the tomcat pool like so :

@Bean(destroyMethod = "close")
public DataSource dataSource() {
    PoolConfiguration poolProperties = getPoolConfig();
    Properties dbProperties = new Properties();
    dbProperties.setProperty("reWriteBatchedInserts", "true");
    poolProperties.setDbProperties(dbProperties);
    return new DataSource(poolProperties);
}

I have enabled sql logging in hibernate and statement logging in postgres (to see if batch inserts are properly rewritten)

...
Hibernate:
insert
into
transactions_entries
(account_id, delta, transaction_entry_role_id, transaction_date, transaction_id, value_date, transaction_entry_id)
values
(?, ?, ?, ?, ?, ?, ?)
Hibernate:
insert
into
transactions_entries
(account_id, delta, transaction_entry_role_id, transaction_date, transaction_id, value_date, transaction_entry_id)
values
(?, ?, ?, ?, ?, ?, ?)
Hibernate:
insert
into
transactions_entries
(account_id, delta, transaction_entry_role_id, transaction_date, transaction_id, value_date, transaction_entry_id)
values
(?, ?, ?, ?, ?, ?, ?)
[20/04/22 15:52:15.674] DEBUG [ecutor-1] [] [BatchingBatch] Executing batch size: 24

and on DB side

2022-04-20 13:52:15.675 GMT [407] LOG:  execute <unnamed>: insert into transactions_entries (account_id, delta, transaction_entry_role_id, transaction_date, transaction_id, value_date, transaction_entry_id) values ($1, $2, $3, $4, $5, $6, $7),($8, $9, $10, $11, $12, $13, $14),($15, $16, $17, $18, $19, $20, $21),($22, $23, $24, $25, $26, $27, $28),($29, $30, $31, $32, $33, $34, $35),($36, $37, $38, $39, $40, $41, $42),($43, $44, $45, $46, $47, $48, $49),($50, $51, $52, $53, $54, $55, $56),($57, $58, $59, $60, $61, $62, $63),($64, $65, $66, $67, $68, $69, $70),($71, $72, $73, $74, $75, $76, $77),($78, $79, $80, $81, $82, $83, $84),($85, $86, $87, $88, $89, $90, $91),($92, $93, $94, $95, $96, $97, $98),($99, $100, $101, $102, $103, $104, $105),($106, $107, $108, $109, $110, $111, $112)

We can see that statement are properly re-written (yay), But unfortunately hypersistence optimizer keeps complaining that it's not enabled :

[20/04/22 15:50:59.275] WARN  [rtStop-1] [] [Hypersistence Optimizer] MAJOR - JdbcBatchToBulkInsertEvent - PostgreSQL can use multi-value inserts that can speed up batching as fewer statements must be executed by the database engine. To enable this feature set the [reWriteBatchedInserts] JDBC configuration property to the value of [true]. For more info about this event, check out this User Guide link - https://vladmihalcea.com/hypersistence-optimizer/docs/user-guide/#JdbcBatchToBulkInsertEvent

I'm going to use an EventFilter to silence it, but I just thought I'd let you know about the false positive :)

[vladmihalcea]

Thanks for letting me know. You can easily filter out any event using an EventFilter. Check out this link for an example.

I'll try to replicate your use case to see if I can get the issue so that I can fix it.

[vladmihalcea]

Fixed.