Re: JDBC prepared statement: a 32767 limit of arguments number

Vladislav,

Have you tried PreparedStatement#addBatch + #executeBatch APIs and
reWriteBatchedInserts=true connection option?
It might yield the expected speedup without resorting to PG-specific APIs
and syntax like COPY.

David>You do have to deal with converting programming data types to their
textual representations for the COPY command but the driver should be
capable of helping with that

That's right, and the driver might add language-specific APIs for
converting data for COPY-FROM.
However,

1) JDBC specification has nothing for COPY. In other words, the code that
uses
COPY-specific APIs is hard to port to other databases.
Of course, pull requests for adding Java API on top of COPY are welcome,
however, nobody cared to add the wrapper APIs yet.

2) COPY FROM is either binary or text. It does not allow "fallback to text
format".
We might need to revise the decisions, however, I know pgjdbc always sends
date/timestamp-like
values as text even though it knows the way to encode and decode them.
Structs and arrays are not supported in binary coding yet :'(

I think COPY syntax could be relaxed here to allow per-field binary/text
format configuration.

3) COPY FROM is "insert-only", and it can't handle conflicts (COPY FROM
does not support insert on conflict / merge)

4) There's a connection option reWriteBatchedInserts=true so pgjdbc could
rewrite
batch calls for "insert into..." into a multi-values insert:

insert into table(c1, c2, c3, ...) values (....)
=>
insert into table(c1, c2, c3, ...) values (....), (...), (...)

It is a pity that the driver has to parse SQL and detect if it looks like
"insert into ..".
On the other hand, the optimization is compatible with the standard SQL
syntax,
and it does not require application changes except for not relying on the
"number of affected rows for each bound row".

Typically executeBatch() returns the exact row count for each batched row,
and the driver loses that information if it flips to "rewrite into
multivalues" mode.
So can't activate batch rewrite by default.

----

It might be interesting to check if the driver could rewrite simple insert
statements
into COPY FROM STDIN, however, that "autoconversion from INSERT to COPY"
would have to be disabled as soon as any non-trivial INSERT feature appears.

For instance, the driver can't convert literal values and expressions in
"values" into COPY:
INSERT INTO testbatch(id, value, comment) VALUES (?, ?, 'no comment');
INSERT INTO testbatch(id, value) VALUES (1+2, ?);

Vladimir