Re: Connection hanging on INSERT apparently due to large batch size and 4 CPU cores

On Mon, 20 Oct 2008, jgassner(at)gmail(dot)com wrote:

> We are using a batched INSERT to perform the insert operation into
> this table. When we attempt to insert batches of size 600 rows, we
> observe this hang issue. However, we evetually found that lowering
> the batch size to 100 avoided the hang issue. While it is nice to
> apparently get around the issue using a lower batch size, it would be
> nice to understand what the actual problem is so we can be sure it
> won't pop up again. This is my question... Anyone know why lowering
> the batch size fixed the problem and why the problem occurred in the
> first place? Is there a maximum amount of data that can be inserted
> in one batch?

See the comments here starting at line #226.

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/jdbc/pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java?annotate=1.42

The driver is already breaking your batch into sub-batches of 256 queries.
We don't expect to get a large response from an insert statement, but
that's apparently not true in your case. Perhaps there is something
unusual about your inserts? Do they fail, resulting in large error
messages? Do they produce warnings or notices for things like
escape_string_warning? If you can reproduce this failure, then turning on
additional logging via the loglevel=2 URL parameter will show the exact
protocol level responses coming from the server and then we should be able
to see what's really happening. (You'd want to look at the protocol level
logging for both the success and failure cases, because on the failure
side, we never get around to processing the results.

Kris Jurka