Postgres Partitions not working with hibernate 4.1.6.Final - Mailing list pgsql-bugs

From Freddie Burgess
Subject Postgres Partitions not working with hibernate 4.1.6.Final
Date
Msg-id 000801cd9b5a$12717600$37546200$@radiantblue.com
Whole thread Raw
Responses Re: Postgres Partitions not working with hibernate 4.1.6.Final  (Jaime Casanova <jaime@2ndquadrant.com>)
List pgsql-bugs
In hibernate 3.3.2.GA, We previously utilized this java program to address
ingesting data into our PostgreSQL partitioned tables.

package im.empl.core.service.ingest.postgres;

import org.hibernate.Interceptor;
import org.hibernate.jdbc.Batcher;
import org.hibernate.jdbc.BatchingBatcherFactory;
import org.hibernate.jdbc.ConnectionManager;

public class PostgresPartitionBatcherFactory extends
BatchingBatcherFactory
{
public PostgresPartitionBatcherFactory() {}

public Batcher createBatcher(
ConnectionManager connectionManager,
Interceptor interceptor ) {
return new PostgresPartitionBatcher(
connectionManager,
interceptor);
}
}

then in our hibernate.properties file, we had set this parameter at
initialization:

# Enable when using Postgres with partitioned tables
hibernate.jdbc.factory_class=im.empl.core.service.ingest.postgres.PostgresPa
rtitionBatcherFactory

With this configuration we were able to insert data into our Postgres
Partitioned tables with no problem, now since we upgraded to Hibernate
4.1.6.Final we are getting errors
like this, since this property is no longer supported.

16:18:01,593 [Thread-23] DEBUG org.hibernate.SQL:104 - insert into EMPLOYEES
(ID, DEPT_NUMBER, ADDRESS, GEO_LOCATION, TYPE, CURRENT_DATE)
values (?, ?, ?, ?, 'CONSULTANT', ?)
16:18:01,754 [Thread-23] WARN org.bushe.swing.event.EventService:? -
Exception thrown by;EventService
subscriber:im.empl.cores.service.ingest.EmplWriter$2@791a9134. Subscriber
class:class im.empl.cores.service.ingest.EmplWriter$2
org.bushe.swing.exception.SwingException: Exception handling event topic
event class=im.empl.cores.io.parse.impl.Corespec1467StreamReaderImpl$5,
event=im.empl.cores.io.parse.impl.Corespec1467StreamReaderImpl$5@3f332b09,
topic=null, eventObj=null
org.bushe.swing.exception.SwingException: Exception handling event topic
event class=im.empl.cores.io.parse.impl.Corespec1467StreamReaderImpl$5,
event=im.empl.cores.io.parse.impl.Corespec1467StreamReaderImpl$5@3f332b09,
topic=null, eventObj=null
at
org.bushe.swing.event.ThreadSafeEventService.handleException(ThreadSafeEvent
Service.java:2021)
at
org.bushe.swing.event.ThreadSafeEventService.handleException(ThreadSafeEvent
Service.java:2009)
at
org.bushe.swing.event.ThreadSafeEventService.publish(ThreadSafeEventService.
java:975)
at
org.bushe.swing.event.ThreadSafeEventService.publish(ThreadSafeEventService.
java:904)
at
blue.core.event.bushe.BusheApplicationEventService.publish(BusheApplicationE
ventService.java:28)
at
im.empl.cores.service.ingest.DefaultcoresIngestManager.ingest(DefaultcoresIn
gestManager.java:383)
at
im.empl.cores.service.ingest.AutocoresIngestMonitor.ingest(AutocoresIngestMo
nitor.java:142)
at
im.empl.cores.service.ingest.AutocoresIngestMonitor$1.run(AutocoresIngestMon
itor.java:104)
Caused by: 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.j
ava:81)
at
org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.
java:73)
at
org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatc
hingBatch.java:57)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntity
Persister.java:2962)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntity
Persister.java:3403)
at
org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.
java:88)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:354)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:275)
at
org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions
(AbstractFlushingEventListener.java:326)
at
org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushE
ventListener.java:52)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1214)
at im.empl.cores.service.ingest.BatchWriter.flush(BatchWriter.java:104)
at im.empl.cores.service.ingest.EmplWriter$1.create(EmplWriter.java:94)
at im.empl.cores.service.ingest.EmplWriter$1.create(EmplWriter.java:1)
at
im.empl.cores.service.ingest.EmplWriter.handleEmplSegment(EmplWriter.java:33
9)
at im.empl.cores.service.ingest.EmplWriter$2.onEvent(EmplWriter.java:179)
at im.empl.cores.service.ingest.EmplWriter$2.onEvent(EmplWriter.java:1)
at
org.bushe.swing.event.ThreadSafeEventService.publish(ThreadSafeEventService.
java:971)

Based to some research, I found the best way to resolve this error
condition, was to modify the code to use the SQLInsert annotation to
suppress the row count check when
inserting the row into a partitioned table.

Questions:

Does anyone know why the previous .jdbc.batcher logic managed the postgres
partitioned inserts without any issues?
Are there any other alternative that will allow us to insert into a Postgres
partition table without making massive code changes?
Would upgrading from 4.1.6.Final to Hibernate 4.1.7 in Linux, fix this
partitioning ingest problem?

thanks

pgsql-bugs by date:

Previous
From: Dimitri Fontaine
Date:
Subject: Re: BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Next
From: Jaime Casanova
Date:
Subject: Re: Postgres Partitions not working with hibernate 4.1.6.Final