Postgres Partitions not working with hibernate 4.1.6.Final

From: "Freddie Burgess" <fburgess(at)radiantblue(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Postgres Partitions not working with hibernate 4.1.6.Final
Date: 2012-09-25 20:12:21
Message-ID: 000801cd9b5a$12717600$37546200$@radiantblue.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)empl(dot)cores(dot)service(dot)ingest(dot)EmplWriter$2(at)791a9134(dot) 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(dot)empl(dot)cores(dot)io(dot)parse(dot)impl(dot)Corespec1467StreamReaderImpl$5(at)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(dot)empl(dot)cores(dot)io(dot)parse(dot)impl(dot)Corespec1467StreamReaderImpl$5(at)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jaime Casanova 2012-09-25 20:50:01 Re: Postgres Partitions not working with hibernate 4.1.6.Final
Previous Message Dimitri Fontaine 2012-09-25 16:29:17 Re: BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations