Re: Postgres jdbc bulk insert stuck

Just a wild guess.
Does it stop on the first batch?
Have you got any triggers on the table?
Are there any notices sent back to the client? There is a certain
limitation related to client TCP read/write sequencing.

Best regards, Vitalii Tymchyshyn

Пт, 20 трав. 2016 о 10:47 dmachop <dmachop(at)gmail(dot)com> пише:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>