hibernate insert batch with partitioned postgresql

Question

is there a solution for batch insert via hibernate in partitioned postgresql table? currently i'm getting an error like this...

ERROR org.hibernate.jdbc.AbstractBatcher - Exception executing batch:
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
   at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:61)
   at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:46)
   at org.hibernate.jdbc.BatchingBatcher.checkRowCounts(BatchingBatcher.java:68)....

i have found this link http://lists.jboss.org/pipermail/hibernate-dev/2007-October/002771.html but i can't find anywhere on the web is this problem solved or how it can be get around

Answer 1

You might want to try using a custom Batcher by setting the hibernate.jdbc.factory_class property. Making sure hibernate won't check the update count of batch operations might fix your problem, you can achieve that by making your custom Batcher extend the class BatchingBatcher, and then overriding the method doExecuteBatch(...) to look like:

    @Override
    protected void doExecuteBatch(PreparedStatement ps) throws SQLException, HibernateException {
        if ( batchSize == 0 ) {
            log.debug( "no batched statements to execute" );
        }
        else {
            if ( log.isDebugEnabled() ) {
                log.debug( "Executing batch size: " + batchSize );
            }

            try {
//              checkRowCounts( ps.executeBatch(), ps );
                ps.executeBatch();
            }
            catch (RuntimeException re) {
                log.error( "Exception executing batch: ", re );
                throw re;
            }
            finally {
                batchSize = 0;
            }

        }

    }

Note that the new method doesn't check the results of executing the prepared statements. Keep in mind that making this change might affect hibernate in some unexpected way (or maybe not).

Answer 2

They say to use two triggers in a partitioned table or the @SQLInsert annotation here: http://www.redhat.com/f/pdf/jbw/jmlodgenski_940_scaling_hibernate.pdf pages 21-26 (it also mentions an @SQLInsert specifying a String method).

Here is an example with an after trigger to delete the extra row in the master: https://gist.github.com/copiousfreetime/59067