Re: foreign table batch inserts

On Wed, May 18, 2016 at 12:27 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 18 May 2016 at 06:08, Michael Paquier <michael.paquier@gmail.com> wrote:
>> > Wouldn’t it make sense to do the insert batch wise e.g. 100 rows ?
>>
>> Using a single query string with multiple values, perhaps, but after
>> that comes into consideration query string limit particularly for
>> large text values... The query used for the insertion is a prepared
>> statement since writable queries are supported in 9.3, which makes the
>> code quite simple actually.
>
> This should be done how PgJDBC does batches. It'd require a libpq
> enhancement, but it's one we IMO need anyway: allow pipelined query
> execution from libpq.

That's also something that would be useful for the ODBC driver. Since
it is using libpq as a hard dependency and does not speak the protocol
directly, it is doing additional round trips to the server for this
exact reason when preparing a statement.

> [design follows]
> This would require libpq to be smarter about how it tracks queries. Right
> now it keeps track of current query, query results, etc directly in the
> connection object, and it sends a Sync after each operation then expects to
> wait in a busy state until it gets the results from that operation.

Yep.

> Instead we'd have to have a FIFO queue of messages libpq expects responses
> for. Variants of PQsendPrepare, PQsendQueryPrepared, PQsendDescribePrepared,
> etc would not  send a Sync message and would append an entry to the expected
> result queue instead of setting the current query, etc on the connection.
> They'd still mark the connection as busy, so no non-queue-aware calls could
> be run until the queue is consumed and empty.

Yep. That's exactly the ODBC regression, which become a huge problem
with more latency.
--
Michael