Exposed: Batch Insert does not use "multirow VALUES syntax"

[gertvdijk]

I'm using Exposed 0.16.3 DSL's batchInsert on a PostgreSQL connection like this:

object MyTable : Table() {
    val myCol: Column<Long> = long("mycol")
    val otherCol: Column<Long> = long("othercol")
}

data class InsertItem(val myCol : Long, val otherCol: Long)

val batchInsertObjects = listOf(
    InsertItem(1234L, 3456L),
    InsertItem(1234L, 9999L)
)

transaction {
    MyTable.batchInsert(batchInsertObjects) {
            item: InsertItem ->
        this[MyTable.myCol] = item.myCol
        this[MyTable.otherCol] = item.otherCol
    }
}

(The code shown above is inspired by the single-column example from the docs. Please let me know if this approach is not okay.)

Expected query:

DEBUG Exposed - INSERT INTO MyTable (mycol, othercol) VALUES (1234, 3456),(1234, 9999)

N.B: using single statement "multirow VALUES syntax", for performance reasons.

As also stated in the Exposed documentation: Batch Insert, this should be in a single statement.

Batch Insert allow mapping a list of entities into DB raws in one sql statement.

(raws is a typo there?)

Actual query

As printed in logs:

DEBUG Exposed - INSERT INTO MyTable (mycol, othercol) VALUES (1234, 3456)
DEBUG Exposed - INSERT INTO MyTable (mycol, othercol) VALUES (1234, 9999)

[Tapac]

It's not an Exposed issued but Postgresql jdbc driver impementation.
Please look at reWriteBatchedInserts jdbc url parameter.
https://jdbc.postgresql.org/documentation/94/connect.html

[gertvdijk]

@Tapac Thanks for your response!

However, setting reWriteBatchedInserts=true as option in the database URL does not seem to work. Am I missing something? Testing locally:

val dbUrl = "jdbc:postgresql://${config.hostName}/${config.database}?reWriteBatchedInserts=${config.rewriteBatchInserts}"
logger.info { "DB URL: '$dbUrl'" }
val dbConnection = Database.connect(dbUrl,
    driver = "org.postgresql.Driver",
    user = config.user, password = config.password)

prints:

DB URL: 'jdbc:postgresql://localhost/postgres?reWriteBatchedInserts=true'
DEBUG Exposed - INSERT INTO MyTable (mycol, othercol) VALUES (1234, 3456)
DEBUG Exposed - INSERT INTO MyTable (mycol, othercol) VALUES (1234, 9999)

Or is this rewritten somewhere down the stream somewhere with this option and never seen in Exposed? If so, I don't understand what batched insertions actually are (in Exposed) compared to all individual inserts.

[Tapac]

As Exposed doesn't know anything about Postgres batch mechanism I guess it logs queries not very well.
Could you check the exact queries with and without reWriteBatchedInserts option?
To enable low-level logging please look at https://jdbc.postgresql.org/documentation/head/logging.html

[gertvdijk]

Thanks for the hint, I'll verify that later. But if the JDBC driver is rewriting individual INSERTs to multirow-value syntax, then it should already do that without using the batch API (MyTable.batchInsert) right?