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. [...]
I'm not convinced that this is the problem as MAX_BUFFERED_QUERIES is around 250 by default, and yet batches of 1000/2000 execute OK.
Note that I haven't seen much benefit to batch sizes above perhaps 50 on a LAN anyway. Batch sizes of 20,000 are almost certainly excessive (but the driver shouldn't fail!)
Are you executing the batch with autocommit on or off?
Anyway, there are two bits of debug output that would be useful to see:
1. A thread dump of the importing JVM when it gets stuck. On unix-like systems you'd do this by sending the JVM a SIGQUIT. I don't know how you'd do the same under Windows.
2. Driver debug output:
a. add "?logLevel=2" to the driver URL b. set a log writer on DriverManager at the start of the run:
// Point this somewhere else, e.g. to a file,
// if it's more convenient.
java.sql.DriverManager.setLogWriter(
new java.io.PrintWriter(System.err)))This should trace all the protocol messages being sent or received to System.err. If the driver is wedging at the protocol level, it should show up in that output. Send me this output off-list and compressed -- it'll be pretty big for 20,000+ queries!
-O
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
