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

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(at)postgresintl(dot)com
www.postgresintl.com

On 25 April 2016 at 00:04, Jinhua Luo <luajit(dot)io(at)gmail(dot)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(at)fastcrypt(dot)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(at)postgresintl(dot)com
> > www.postgresintl.com
> >
> > On 20 April 2016 at 07:08, Jinhua Luo <luajit(dot)io(at)gmail(dot)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(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> >
>