Postgres Pro
Downloads
Home   >   mailing lists

Errors using JDBC batchUpdate with plpgsql function - Mailing list pgsql-general

From Nanker Phelge
Subject Errors using JDBC batchUpdate with plpgsql function
Date
Msg-id CAF20xuFkjQAY=u4r_Z9XR2HgPsnxfM3hH-BtMpkEn-mtug0ZZw@mail.gmail.com
Whole thread Raw
Responses Re: Errors using JDBC batchUpdate with plpgsql function  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Errors using JDBC batchUpdate with plpgsql function  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Errors using JDBC batchUpdate with plpgsql function  (Hannes Erven <hannes@erven.at>)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: plpgsql functions organisation
Next
From: Adrian Klaver
Date:
Subject: Re: Errors using JDBC batchUpdate with plpgsql function