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

On Wed, Mar 9, 2022 at 12:38 PM Vladislav Malyshkin <mal(at)gromco(dot)com> wrote:

> On 09/03/2022 10.03, Tom Lane wrote:
>
>
> I concur with David's opinion that if you think you need more
> parameters, you're doing it wrong. One idea to consider is
> aggregating similar values into an array parameter.
>
>
> I disagree:
>

Ok. If PostgreSQL ever does end up releasing a new protocol version I
would say it is at least in the realm of possibility that this limit would
be increased. But releasing a new protocol version is hard to get
consensus to do given the incompatibilities doing so creates. The curse of
a mature and widely used application. It isn't like this limitation has
gone unnoticed until now; yet still we haven't released a new protocol
version in many years nor presently have concrete plans to do so.

>
> 1. It is extremely convenient to insert multiple values in a single SQL
> insert:
>
> *INSERT INTO table_name (f1,f2,f3) VALUES (1,"text",2.4),
> (2,"text2",2.5),...*
> Setting all values as JDBC parameters is the easiest way to integrate
> java/scala and SQL.
> A single insert of 10000 records is several orders of magnitudes *faster*
> than 10000 separate inserts, not to mention transaction simplification.
>

PostgreSQL would expect that you would use the COPY API in this kind of
situation.

> 2. For automatic scala<->jdbc integration tools such as
> https://github.com/mal19992/sqlps a number of JDBC arguments can be
> generated by an automatic transformation, the SQL and JDBC arguments are
> autogenerated and can be a very large number.
> Suggested by David approach "like using a temp table and a join instead of
> an IN operator." is extremely inconvenient for automatic tools.
>
>
This is too vague to comment upon or be convinced by.

David J.