Errors using JDBC batchUpdate with plpgsql function

From: Nanker Phelge <n(dot)phelge01(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Errors using JDBC batchUpdate with plpgsql function
Date: 2015-05-03 21:33:02
Message-ID: CAF20xuFkjQAY=u4r_Z9XR2HgPsnxfM3hH-BtMpkEn-mtug0ZZw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am attempting to setup a Spring Batch ItemWriter to call a function in
PostgreSQL to insert the provided objects. I posted the details to
stackoverflow a month ago (
http://stackoverflow.com/questions/28971220/spring-batch-itemwriter-error-with-postgresql-function)
with no answers, and I just found this mailing list. The Java code for the
ItemWriter is:

public class TestUserDbItemWriter implements ItemWriter<TestUser>
{
private JdbcTemplate jdbcTemplate;
private int jobId;

@Override
public void write(final List<? extends TestUser> chunk) throws
Exception {

String sql = "select test_user_result_insert_func(?, ?, ?);";
try
{
getJdbcTemplate().setSkipResultsProcessing(true);
getJdbcTemplate().setSkipUndeclaredResults(true);
getJdbcTemplate().batchUpdate(sql,
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i)
throws SQLException {
TestUser testUser = chunk.get(i);
ps.setInt(1, testUser.getId());
ps.setString(2, testUser.getSsn());
ps.setInt(3, getJobId());
}
@Override
public int getBatchSize() {
return chunk.size();
}
});
}
catch(org.springframework.dao.DataIntegrityViolationException ex)
{
System.out.println("data integrity ex="+ex.getMessage());
Throwable innerex = ex.getMostSpecificCause();
if(innerex instanceof java.sql.BatchUpdateException)
{
java.sql.BatchUpdateException batchex =
(java.sql.BatchUpdateException) innerex ;
SQLException current = batchex;
int count=1;
do {

System.out.println("inner ex " + count + " =" +
current.getMessage());
count++;

} while ((current = current.getNextException()) != null);
}

throw ex;
}
catch(Exception ex)
{
System.out.println("ex="+ex.getMessage());
throw ex;
}
}

And the database function is a simple insert:

CREATE OR REPLACE FUNCTION test_user_result_insert_func(
p_id NUMERIC,
p_ssn CHARACTER VARYING(9),
p_job_id NUMERIC
)
RETURNS VOID AS
$BODY$

BEGIN
INSERT INTO test_user_result (test_user_result_sys_id,ssn,job_id)
VALUES (p_id,p_ssn,p_job_id);
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Running this code gives the following errors from my println() statements
:

data integrity ex=PreparedStatementCallback; SQL [select
test_user_result_insert_func(?, ?, ?);]; Batch entry 0 select
test_user_result_insert_func(3, '333333333', 0) was aborted. Call
getNextException to see the cause.; nested exception is
java.sql.BatchUpdateException: Batch entry 0 select
test_user_result_insert_func(3, '333333333', 0) was aborted. Call
getNextException to see the cause.
inner ex 1 =Batch entry 0 select test_user_result_insert_func(3,
'333333333', 0) was aborted. Call getNextException to see the cause.
inner ex 2 =A result was returned when none was expected.
data integrity ex=PreparedStatementCallback; SQL [select
test_user_result_insert_func(?, ?, ?);]; Batch entry 0 select
test_user_result_insert_func(2, '222222222', 0) was aborted. Call
getNextException to see the cause.; nested exception is
java.sql.BatchUpdateException: Batch entry 0 select
test_user_result_insert_func(2, '222222222', 0) was aborted. Call
getNextException to see the cause.
inner ex 1 =Batch entry 0 select test_user_result_insert_func(2,
'222222222', 0) was aborted. Call getNextException to see the cause.
inner ex 2 =A result was returned when none was expected.

If I change the sql string in the Java code to just be the INSERT statement
contained within the function, it executes successfully. So, the error
seems to be due to how some part of JDBC is interpreting the SELECT
statement used to call the function. In this case the INSERT statement is
simple, but this is just an example - in my actual code, the INSERT
statements will be more complicated, and I would rather have that detail
within a database function. Is there something I'm missing with how
batchUpdate() can be used with a database function? This is occurring
using JDK 1.7, PostgreSQL JDBC driver JAR postgresql-9.3-1102.jdbc4,
PostgreSQL 8.2.15 (under Greenplum 4.2.8.1 build 2), Spring Batch 2.2.1,
and Spring Framework 3.0.5. Here is the full stack trace, if that would be
helpful:

20150503154455679 ERROR [org.springframework.batch.core.step.AbstractStep]
- Encountered an error executing the step
org.springframework.retry.ExhaustedRetryException: Retry exhausted after
last attempt in recovery path, but exception is not skippable.; nested
exception is org.springframework.dao.DataIntegrityViolationException:
PreparedStatementCallback; SQL [select test_user_result_insert_func(?, ?,
?);]; Batch entry 0 select test_user_result_insert_func(3, '333333333', 0)
was aborted. Call getNextException to see the cause.; nested exception is
java.sql.BatchUpdateException: Batch entry 0 select
test_user_result_insert_func(3, '333333333', 0) was aborted. Call
getNextException to see the cause.
at
org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$5.recover(FaultTolerantChunkProcessor.java:406)
at
org.springframework.retry.support.RetryTemplate.handleRetryExhausted(RetryTemplate.java:435)
at
org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:304)
at
org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:188)
at
org.springframework.batch.core.step.item.BatchRetryTemplate.execute(BatchRetryTemplate.java:217)
at
org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.write(FaultTolerantChunkProcessor.java:423)
at
org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199)
at
org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)
at
org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395)
at
org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at
org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267)
at
org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
at
org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
at
org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
at
org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
at
org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253)
at
org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
at
org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:137)
at
org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
at
org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60)
at
org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:152)
at
org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:131)
at
org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
at
org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301)
at
org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:134)
at
org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48)
at
org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:127)
at
org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:351)
at
org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:577)
Caused by: org.springframework.dao.DataIntegrityViolationException:
PreparedStatementCallback; SQL [select test_user_result_insert_func(?, ?,
?);]; Batch entry 0 select test_user_result_insert_func(3, '333333333', 0)
was aborted. Call getNextException to see the cause.; nested exception is
java.sql.BatchUpdateException: Batch entry 0 select
test_user_result_insert_func(3, '333333333', 0) was aborted. Call
getNextException to see the cause.
at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
at
org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:883)
at dao.writer.TestUserDbItemWriter.write(TestUserDbItemWriter.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at
org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
at
org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy1.write(Unknown Source)
at
org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175)
at
org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151)
at
org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$3.doWithRetry(FaultTolerantChunkProcessor.java:330)
at
org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:255)
... 26 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 select
test_user_result_insert_func(3, '333333333', 0) was aborted. Call
getNextException to see the cause.
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2743)
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResultRows(AbstractJdbc2Statement.java:2692)
at
org.postgresql.core.v3.QueryExecutorImpl$1.handleResultRows(QueryExecutorImpl.java:439)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1876)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
at
org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at
org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:898)
at
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
... 45 more

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-05-04 00:29:03 Re: Errors using JDBC batchUpdate with plpgsql function
Previous Message Anton 2015-05-03 21:15:54 optimization join on random value