Barry Lind <blind@xythos.com> writes:
> When an application needs to do a lot of the same thing (i.e
> insert a thousand rows), the applicaiton tells the driver to insert a
> 'batch' of 1000 rows instead of performing 1000 regular inserts. This
> allows the driver to optimize this operation as one network roundtrip
> instead of 1000 roundtrips.
> ... How could this be accomplished with the
> new FE/BE protocol "extended query" facility?
Well, as far as network roundtrips go, it's always been true that you
don't really have to wait for the backend's response before sending the
next command. The proposal to decouple SYNC from individual commands
should make this easier: you fire off N commands "blind", then a SYNC.
When the sync response comes back, it's done. If any of the commands
fail, all else up to the SYNC will be ignored, so you don't have the
problem of commands executing against an unexpected state.
(I'm not sure it'd be bright to issue thousands of commands with no
SYNC, but certainly reasonable-size batches would be sensible.)
As for lots of instances of the same kind of command, you could PARSE
the SQL insert command itself just once (with parameter placeholders for
the data values), then repeat BIND/EXECUTE pairs as often as you want.
That's probably about as efficient as you're going to get without
switching to COPY mode.
Does that address your concern, or is there more to do?
regards, tom lane