Re: BUG #1347: Bulk Import stopps after a while ( 8.0.0.

On Mon, 13 Dec 2004, PostgreSQL Bugs List wrote:

>
> The following bug has been logged online:
>
> Bug reference: 1347
> PostgreSQL version: 8.0 Beta
> Operating system: Windows XP
> Description: Bulk Import stopps after a while ( 8.0.0. RC1)
>
> - I have written a java program to transfer data from SQL Server 2000 to
> PosgresSQL 8.0.0 RC1 release. I am updating the data in batches.
> If my batch size is 1000/2000 records at a time.. This works fine.. And if I
> change this size to say 20,000, it does only finishes one loop.. and then
> stays idle. The CPU usage down to 10 % which was before 100 % while applying
> the first batch of 20, 000 records.
>
>
> The execution of program is halting just at
> int n [] = stmt.batchUpdate();
>

This may be a problem with the JDBC driver deadlocking as described in the
below code comment. When originally written I asked Oliver about the
estimate of MAX_BUFFERED_QUERIES and he felt confident in that number. It
would be good to know if lowering this number fixes your problem.

Kris Jurka

// Deadlock avoidance:
//
// It's possible for the send and receive streams to get
// "deadlocked" against each other since we do not have a separate
// thread. The scenario is this: we have two streams:
//
// driver -> TCP buffering -> server
// server -> TCP buffering -> driver
//
// The server behaviour is roughly:
// while true:
// read message
// execute message
// write results
//
// If the server -> driver stream has a full buffer, the write will
// block. If the driver is still writing when this happens, and the
// driver -> server stream also fills up, we deadlock: the driver is
// blocked on write() waiting for the server to read some more data,
// and the server is blocked on write() waiting for the driver to read
// some more data.
//
// To avoid this, we guess at how many queries we can send before the
// server -> driver stream's buffer is full (MAX_BUFFERED_QUERIES).
// This is the point where the server blocks on write and stops
// reading data. If we reach this point, we force a Sync message and
// read pending data from the server until ReadyForQuery,
// then go back to writing more queries unless we saw an error.
//
// This is not 100% reliable -- it's only done in the batch-query case
// and only at a reasonably high level (per query, not per message),
// and it's only an estimate -- so it might break. To do it correctly
// in all cases would seem to require a separate send or receive
// thread as we can only do the Sync-and-read-results operation at
// particular points, and also as we don't really know how much data
// the server is sending.

// Assume 64k server->client buffering and 250 bytes response per
// query (conservative).
private static final int MAX_BUFFERED_QUERIES = (64000 / 250);