Re: Postgres jdbc bulk insert stuck - Mailing list pgsql-jdbc

Hi,

I have got a large number (about a million) of records that is being sent to
Postgres 8.4 table (with around 250+ columns; 25+ text columns).  I decided
to split it into batches of 1000 records. A strange thing I observed is that
the batch insert is stuck while individual inserts are working. Enabled the
log to check what's happening on the server. It seems to be stuck at this
phase (shorted the statement):

2016-05-20 10:06:38 EDT LOG:  duration: 1.334 ms  parse S_102: INSERT INTO
Test (<col_names>) VALUES($1,..., $290)

AFAIK: It used to parse, bind with params and execute with params that is
printed to the log.

A sample snippet where the issue occurs:
try
{
        dbUtil.connection.setAutoCommit(false);
        for(...)
        {
                batchCount++;
                if (batchCount % BATCHSIZE == 0)
                {
                        //dbUtil.nonBatch(insertSql, Utility.listtoArray(recordList));
                        dbUtil.batch(insertSql, Utility.listtoArray(recordList));
                        recordList.clear();
                }
        }

        dbUtil.commit();
}

finally
{
        dbUtil.connection.setAutoCommit(true);
}

These are the things that I have tried:
1. Maybe I'm having a batch size that is too big to handle. So, I reduced
the size to 256 and the issue still persists.
2. Data issue? Skipped a few rows in between to check whether the particular
row is in question? Issue still appears.
3. OS Specific? I was running the application on linux box to be sent to
postgres installed on linux. Tried running the same application on Windows
sending to postgres installed on linux.
*Surprisingly, this worked since I ran this application on windows.*
4. Commit in between to ensure the rows are inserted immediately. Still no
avail.



*Details:*
Box details:
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Red Hat Enterprise Linux Server release 6.5 (Santiago)
Java:
Java(TM) SE Runtime Environment (build 1.8.0_45-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.45-b02, mixed mode)
Postgres driver:
// http://mvnrepository.com/artifact/org.postgresql/postgresql
compile group: 'org.postgresql', name: 'postgresql', version:
'9.4.1208.jre7'




--
View this message in context: http://postgresql.nabble.com/Postgres-jdbc-bulk-insert-stuck-tp5904350.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc