Re: BUG #1347: Bulk Import stopps after a while ( 8.0.0. - Mailing list pgsql-bugs

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);