Message-ID: <gh-pgjdbc-pgjdbc-3195@github.com>
From: "vlsi (@vlsi)" <noreply+vlsi@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Mon, 08 Apr 2024 11:02:00 +0000
Subject: [pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 213894
X-GitHub-Author-Login: vlsi
X-GitHub-Issue: 3195
X-GitHub-Labels: enhancement
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3195
Content-Type: text/plain; charset=utf-8

**Describe the issue**

Currently we rewrite the select to multi-values insert.
It requires multiple statements, and we use power-of-two packing to alleviate the overhead.

There is an alternative option:

```sql
insert into table(id, name, ...)
 select unnest(?), unnest(?), ...
```

Then we could bind the values as a set of arrays.

It might have the following improvements:
1) We would no longer require "power-of-two" logic
2) The query will be shorter
3) It might be faster
4) There will be no limit of "65535 binds"

TODO:
* Measure how `select unnest(?), unnest(?)` compares with `values(...), (...), (...)`
* Test if it would work with "on conflict", "merge" and so on
* Figure out if we could support `values(DEFAULT, ...)`. It might be `DEFAULT` would require adjusting the column list in the `into` clause
* Figure out if we could support expressions in `values` like `values(?+?, ?-?)`