Re: [GENERAL] How does postgresql jdbc driver implement prepared batch?

In fact, I'm curious about how to implement prepared batch using JDBC
like API. It's mostly related to the extended protocol of postgresql.

Here the "final statement" means the final prepared statement.

Because until executeBatch(), the JDBC does not know the final size of
argument set, right? So it has no way to create the prepared statement
in advance (of course, it could cache the previous statement, if the
size of argument set matches, it would reuse it).
That is, the JDBC has to create the prepared statement dynamically.
And if later usage does not match the previous size (the number of
addBatch() calls), it has to re-create it.

I think it's limited to the extended protocol of postgresql:

http://www.postgresql.org/docs/current/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

"The supplied parameter set must match those needed by the prepared statement. "

I just need to confirm it. In fact, when I use golang sql, I
implements the JDBC like API, creating and caching the prepared
statements dynamically just like what I describe above. The prepared
batch outperforms plain batch in my testing cases.

2016-04-25 7:13 GMT+08:00 Dave Cramer <pg@fastcrypt.com>:
> You will get considerably more response if you use the jdbc list.
>
> The driver does not change it to a "final statement" it creates the prepared
> statement then sets the parameters many times and executes it,
>
> Although the most recent code does it a bit differently now
>
> Dave Cramer
>
> davec@postgresintl.com
> www.postgresintl.com
>
> On 20 April 2016 at 07:08, Jinhua Luo <luajit.io@gmail.com> wrote:
>>
>> Hi,
>>
>> As document said, in the extended query protocol, "The query string
>> contained in a Parse message cannot include more than one SQL
>> statement".
>>
>> So to support batch in prepared statement, I think the only way is to
>> determine the batch size in advance and then create the appropriate
>> prepared statement, e.g.
>>
>> Given the batch size is fixed to 3, then prepare below statement:
>> -- create table foobar(a int, b text);
>> insert into foobar values($1, $2), ($3, $4), ($5, $6);
>>
>> Then this prepared statement must be bound with 3 set of arguments.
>>
>> The limitation is obvious: the batch size is fixed, so if you need to
>> do batch with size of 4, the previous prepared statement is useless
>> and you need to recreate it.
>>
>> On the other hand, in JDBC, it seems that you just need to prepare
>> below statement:
>>
>> insert into foobar values($1, $2);
>>
>> And then calls addBatch() repeatedly until you think the batch size is
>> enough.
>>
>> What's the final statement does postgresql jdbc driver convert to? I'm
>> so curious.
>>
>> I'm not familiar with jdbc, and although I check the driver source
>> codes, but I still cannot understand it.
>>
>> Anybody knows the answer? Thanks.
>>
>>
>> Regards,
>> Jinhua Luo
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>