Thread: MyBatis Batch Update Leads to PSQLException "Too many updateresults were returned"

I have a fairly simple implementation of a batch update via the MyBatis
functionality.

Postgres version (via RDS on Amazon Web Services): 9.6.6
JDBC driver version: 9.4

The error manifests as follows:

DataIntegrityViolationException
Root cause: BatchUpdateException
getNextException: PSQLException - Too many update results were returned.

Has anyone else run into this? I have searched far and wide for an
explanation (including here) but have not found an answer yet.

I assume this error is coming up from the JDBC driver?

The curious thing is that the update seems to work (as in database
side-effects) but it still throws an exception afterward.

Here is a fragment of the MyBatis mapper XML file for the update. The
failure can happen for as few as two or three updates, and it can succeed
with hundreds.

    <update id="update" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            UPDATE public.vid_event_timing SET mark_delete = 1 WHERE
            visitor_id=#{item.vid} AND timed_node_id=#{item.timedNodeId} AND
conditional_node_id= #{item.conditionalNodeId}
        </foreach>
    </update>

The MyBatis/Spring configuration includes this session factory bean, set to
ExecutorType.BATCH by default.

    @Bean
    public SqlSessionTemplate getSqlSessionTemplate()
    {
        SqlSessionFactory sessionFactory = null;
        try 
        {
            sessionFactory =
getMktPersistDesignSqlSessionFactoryBean().getObject();
           
sessionFactory.getConfiguration().setDefaultExecutorType(ExecutorType.BATCH);
        } 
        catch (Exception e) 
        {
            String message = PersistLogUtil.error(logger, "Failed to create
SqlSessionTemplate");
            throw new RuntimeException(message, e);
        }
        SqlSessionTemplate template = new
SqlSessionTemplate(sessionFactory);
        return template;
    }

Thanks for any help.




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-jdbc-f2168246.html

Thanks Dave. I actually did just that, using:

https://jdbc.postgresql.org/download/postgresql-42.2.1.jre7.jar

The result was basically the same, just a different nesting of the
exceptions:

DataIntegrityViolationException
root cause: PSQLException: ... too many updates ...

I was hoping an updated driver would help, but it appears to have the same
issue.

Regards,
Chris




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-jdbc-f2168246.html

Hi Dave,

I am certainly willing to follow up on that, and will do so. I can turn on
debugging with MyBatis and perhaps capture the query pattern that is being
generated.

However, it would help to get some background on the PSQLException that is
being thrown. 

* What is the nature of the error? 

* What does Postgres or its JDBC driver imply about the query for the
message of "... too many updates ..."

* Can you give me an indication of what logic is being tripped over that
leads to this error condition?

Thanks,
Chris




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-jdbc-f2168246.html