Re: Data ingestion failing when using higher Batch size

Hi Adrian,

Please find anwer in line :

What is the SQL you are running?
-We have a table with about 167 columns and we are trying to run batch
updates on the table . I am attaching the part of code for your reference .
How is that being run in the code?
Yes , it is runs as a part of the code .

Define small vs large batch sizes?
smaller batch size of =3
larger batch size =100

Thanks & Regards,
Rupesh Choudhary

On 15 June 2016 at 04:56, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> On 06/14/2016 09:33 AM, Rupesh Choudhary wrote:
>
>> Hi Team ,
>>
>> We have been trying to insert data (batch inserts) using a JAVA app
>> which uses prepared statements with parameterized queries to insert
>> data .We are able to insert data with small batch size however when we
>> increase the batch size to a higher value data ingestion fails . We are
>> using pgbouncer for connection pooling on database side which is running
>> in session mode and server_reset_query set to "DISCARD ALL" . Please
>> suggest the how can we bulk load data with higher batch size .Below the
>> error logs that we are getting from the application end.
>>
>
> Interpreting the below is difficult without information on the actual
> code/SQL being run.
>
> So:
>
> What is the SQL you are running?
>
> How is that being run in the code?
>
> Define small vs large batch sizes?
>
> What is the tool chain you are using to go from Java to Postgres?
>
>
>> Below are the logs from application :-
>>
>> *2016-06-07T11:33:04.50-0700 [App/2]* OUT at
>>
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2959)
>> ~[postgresql-9.4-1205-jdbc41.jar!/:9.4]
>>
>> *2016-06-07T11:33:04.50-0700 [App/2]* OUT at
>>
>> com.exelon.drillinginfo.service.PdenDescService.createDrillingInfo(PdenDescService.java:337)
>> ~[app/:na]
>>
>> *2016-06-07T11:34:35.04-0700 [App/1]* OUT 2016-06-07 18:34:35.048
>> INFO 29 --- [io-64887-exec-1] c.e.d.service.PdenDescService :
>> Run id: 33842
>>
>> *2016-06-07T11:34:35.05-0700 [App/1]* OUT 2016-06-07 18:34:35.051
>> INFO 29 --- [io-64887-exec-1] c.e.d.service.PdenDescService :
>> running inserts
>>
>> ********---------------------------------------------------------------***********
>>
>> *2016-06-07T11:34:35.05-0700 [App/1]* OUT 2016-06-07 18:34:35.058
>> INFO 29 --- [io-64887-exec-1] c.e.d.service.PdenDescService :
>> running inserts
>>
>> ********---------------------------------------------------------------***********
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR
>> org.postgresql.util.PSQLException: ERROR: prepared statement "S_3" does
>> not exist
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR at
>>
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR at
>>
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR at
>>
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR at
>>
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2959)
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR at
>>
>> com.exelon.drillinginfo.service.PdenDescService.createDrillingInfo(PdenDescService.java:337)
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR at
>>
>> com.exelon.drillinginfo.service.PdenDescService$$FastClassBySpringCGLIB$$3080db7d.invoke(<generated>)
>>
>> *2016-06-07T11:34:35.06-0700 [App/1]* ERR at
>> org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>>
>>
>>
>> Thanks & Regards,
>> Rupesh Choudhary
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>