The following bug has been logged online:
Bug reference: 1347
Logged by: Bahadur Singh
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0 Beta
Operating system: Windows XP
Description: Bulk Import stopps after a while ( 8.0.0. RC1)
Details:
Hello,
I have found a trivial problem during the bulk import of data.
I have a following situation.
- I have 2 big table on SQL Server 2000 with 163000 rows each.
- 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();
I waited one hours at the most to wait above instruction to finish and then
stopped manually.. I am not sure that is it JDBC error or PostgreSQL error.
I am using JDBC release of PosgresSQL 8.0.0 RC1 release.
Thanks
Bahadur
Include :
Log 1 : when does not work..
Log 2 : when works ..
------------------ Log 1 ----------------
WHEN BATCH SIZE = 2000
---------------
getMsSqlConnection() :: status = ACTIVE
getPgSqlConnection() :: status = ACTIVE
Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_S (
"transstep" int,
"transactionid" int,
"departmentnumber" char (4),
"plunumber" char (16),
"identnumber" char (16),
"quantity" int,
"quantitydecimals" int,
"packingunit" int,
"mmcode" int,
"amountsign" char (1),
"amountabsolute" int,
"code1" char (1),
"code2" char (1),
"code3" char (1),
"idcdate" char (14),
"originalitemprice" int,
PRIMARY KEY ( TransactionId,TransStep ) )
Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_S FINISHED
Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_W (
"transactionid" int,
"transstep" int,
"iteminfo" char (4),
"itemnumber" char (16),
"flag1" char (2),
"flag2" char (2),
"amount" int,
"flag3" char (2),
"code1" char (1),
"flag4" char (2),
"code2" char (1),
"code3" char (1),
"idcdate" char (14),
"plunumber" char (16),
"quantity" int,
PRIMARY KEY ( TransactionId,TransStep ) )
Fri Dec 10 16:47:23 CET 2004 Migration: CREATE TABLE EodIDC_W FINISHED
Fri Dec 10 16:47:23 CET 2004 Migration: DDL finished in 109 ms
Fri Dec 10 16:47:23 CET 2004 Migration: Exporting Table to PostgreSQL =
EodIDC_S
Fri Dec 10 16:47:23 CET 2004 Migration: Reading data from MS SQL Server
table ... EodIDC_S
Fri Dec 10 16:47:24 CET 2004 Migration: Batch sent upto = 20000
Fri Dec 10 16:47:24 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=20000
Fri Dec 10 16:47:31 CET 2004 Migration: EodIDC_S Records updated in DB=20000
Fri Dec 10 16:47:32 CET 2004 Migration: Batch sent upto = 40000
Fri Dec 10 16:47:32 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=20000
****** NO RESPONSE AFTER ******
Process stopped and restarted with batch size 2000 rows
------------------ Log 2 ----------------
WHEN BATCH SIZE = 2000
----------------------
Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_S (
"transstep" int,
"transactionid" int,
"departmentnumber" char (4),
"plunumber" char (16),
"identnumber" char (16),
"quantity" int,
"quantitydecimals" int,
"packingunit" int,
"mmcode" int,
"amountsign" char (1),
"amountabsolute" int,
"code1" char (1),
"code2" char (1),
"code3" char (1),
"idcdate" char (14),
"originalitemprice" int,
PRIMARY KEY ( TransactionId,TransStep ) )
Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_S FINISHED
Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_W (
"transactionid" int,
"transstep" int,
"iteminfo" char (4),
"itemnumber" char (16),
"flag1" char (2),
"flag2" char (2),
"amount" int,
"flag3" char (2),
"code1" char (1),
"flag4" char (2),
"code2" char (1),
"code3" char (1),
"idcdate" char (14),
"plunumber" char (16),
"quantity" int,
PRIMARY KEY ( TransactionId,TransStep ) )
Fri Dec 10 16:51:02 CET 2004 Migration: CREATE TABLE EodIDC_W FINISHED
Fri Dec 10 16:51:02 CET 2004 Migration: DDL finished in 94 ms
Fri Dec 10 16:51:02 CET 2004 Migration: Exporting Table to PostgreSQL =
EodIDC_S
Fri Dec 10 16:51:02 CET 2004 Migration: Reading data from MS SQL Server
table ... EodIDC_S
Fri Dec 10 16:51:03 CET 2004 Migration: Batch sent upto = 2000
Fri Dec 10 16:51:03 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=2000
Fri Dec 10 16:51:03 CET 2004 Migration: EodIDC_S Records updated in DB=2000
Fri Dec 10 16:51:03 CET 2004 Migration: Batch sent upto = 4000
Fri Dec 10 16:51:03 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=2000
Fri Dec 10 16:51:04 CET 2004 Migration: EodIDC_S Records updated in DB=2000
Fri Dec 10 16:51:04 CET 2004 Migration: Batch sent upto = 6000
Fri Dec 10 16:51:04 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=2000
Fri Dec 10 16:51:05 CET 2004 Migration: EodIDC_S Records updated in DB=2000
Fri Dec 10 16:51:05 CET 2004 Migration: Batch sent upto = 8000
..
..
..
..
Fri Dec 10 16:52:07 CET 2004 Migration: EodIDC_S Records updated in DB=2000
Fri Dec 10 16:52:07 CET 2004 Migration: Batch sent upto = 162000
Fri Dec 10 16:52:07 CET 2004 Migration: EodIDC_S Total SQLs sent to DB=2000
Fri Dec 10 16:52:08 CET 2004 Migration: EodIDC_S Records updated in DB=2000
Fri Dec 10 16:52:08 CET 2004 Migration: Complete Batch sent = 163492
Fri Dec 10 16:52:08 CET 2004 Migration: Total SQL=1492
Fri Dec 10 16:52:08 CET 2004 Migration: Records updated 1492
Fri Dec 10 16:52:08 CET 2004 Migration: Exporting Table to PostgreSQL =
EodIDC_S DONE
**** THE DATA WAS IMPORTED SUCCESSFULLY *****
************ JAVA METHOD ***************
private void insertBulkData (ArrayList insertSqlList, String tableName,
boolean save ) throws Exception {
// update
try {
Connection con = getPgSqlConnection();
con.setAutoCommit(false);
Statement stmt = con.createStatement();
for (Iterator sqls = insertSqlList.iterator();
sqls.hasNext(); ) {
String sql = (String)sqls.next();
stmt.addBatch(sql);
}
log( tableName + " Total SQLs sent to DB=" +
insertSqlList.size());
int n [] = stmt.executeBatch();
stmt.close();
log( tableName + " Records updated in DB=" + n.length
);
if (save) {
con.commit();
}
} catch (SQLException e) {
e.printStackTrace();
throw e;
} catch (Throwable te) {
te.printStackTrace();
}
}
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
