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

What do you mean by "the executor simply iterates over the parameters
applying them sequentially to the prepared statement
"?

Reuse your example:
insert into foo (x,y) values (?,?)

Let me explain what happens in the extended protocol between the
client and the backend:

a) send Parse message, let pg backend parse the statement "insert into
foo (x,y) values ($1,$2) " (note that in PG, you must use $N to denote
the parameter, not ?), it would create a prepared-statement object at
the backend

b) send Bind message to bind parameters to the prepared-statement
object, here you must provides the same parameters to the parameter
placeholders, in this example, it's 2. That means for example, you
could bind "foo", "bar", but you could not bind "foo", "bar", "hello",
"world". If you need so, then in the a) step, you must tell the
backend to parse "insert into foo(x,y) values($1,$2),($3,$4)". The
Bind would create a portal object

c) send Execute message to execute a portal object


All in all, the number of parameter placeholders of one prepared
statement object is fixed after created.

No matter what forms of API, it has no way to violate the low-level
protocol, right?

So that's what I mean prepared statement should be created dynamically
depending how much you calls addBatch() before executeBatch().

There is some similar topics on stackoverflow:
golang, postgresql:
http://stackoverflow.com/a/25192138/5055251

golang, mysql:
http://stackoverflow.com/a/21112176/5055251

2016-04-25 18:45 GMT+08:00 Dave Cramer <pg@fastcrypt.com>:
> It doesn't work that way.
>
> What it does is this:
>
> insert into foo (x,y) values (?,?)
>
> creates a prepared statement, and
>
> Every time you call addBatch it queues the parameters then the executor
> simply iterates over the parameters applying them sequentially to the
> prepared statement
>
>
> Dave Cramer
>
> davec@postgresintl.com
> www.postgresintl.com
>
> On 25 April 2016 at 00:04, Jinhua Luo <luajit.io@gmail.com> wrote:
>>
>> 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
>> >
>> >
>
>