Hello everyone I have a ktor application, and I'm ...

# server

[Mod]

Hello everyone
I have a ktor application, and I'm connecting to postgres DB and using pgJDBC and HikariDataSource, and using exposed ORM. what I'm trying to do is batch inserts for preformance, but I couldn't know what is the problem, it keeps running a separate insert statement. as per documenation in exposed https://github.com/JetBrains/Exposed/wiki/DSL#batch-insert
> NOTE: The batchInsert function will still create multiple INSERT statements when interacting with your database. You most likely want to couple this with the rewriteBatchedInserts=true (or rewriteBatchedStatements=true) option of your relevant JDBC driver, which will convert those into a single bulkInsert. You can find the documentation for this option for MySQL here and PostgreSQL here.
I checked postgres documentation https://jdbc.postgresql.org/documentation/use/
and I added this flag like this

private fun createHikariDataSource(
        url: String,
        driver: String,
        user: String,
        password: String,
        maxPoolSize: Int,
        autoCommit: Boolean
    ) = HikariDataSource(HikariConfig().apply {
        addDataSourceProperty("reWriteBatchedInserts", true)
        driverClassName = driver
        jdbcUrl = url
        username = user
        this.password = password
        maximumPoolSize = maxPoolSize
        isAutoCommit = autoCommit
        isReadOnly = false
        transactionIsolation = "TRANSACTION_REPEATABLE_READ"
        addDataSourceProperty("reWriteBatchedInserts", true)
        validate()
    })

and tried to pass the flag in the url like

val connectionPool = createHikariDataSource(
            url = "$jdbcURL/$defaultDatabase?reWriteBatchedInserts=true",
            driver = driverClassName,
            user = username,
            password = password,
            maxPoolSize.toInt(),
            autoCommit.toBoolean()
        )

and still the batch statement won't work

[Dominik Sandjaja]

Did you verify with Postgres' logging that the statements do not work? For me it works with those properties set, but I only see the differences in the Postgres log, NOT in the Exposed logs. The Exposed logging still shows two separate INSERT INTO ...

[Mod]

yes I believe the exposed logs is the problem

[Dominik Sandjaja]

This is specifically mentioned in the documentation:

If you want to check if the rewriteBatchedInserts + batchInsert is working correctly, check how to enable JDBC logging for your driver because Exposed will always show the non-rewritten multiple inserts. You can find the documentation for how to enable logging in PostgreSQL here.

[Mod]

thanks

[Dominik Sandjaja]

In case you want to validate that it works with testcontainers, I found this blog page helpful: https://maciejwalkowiak.com/blog/postgresql-testcontainers-sql-query-logs/

[Mod]

thanks, that is helpful