Postgres Partitions not working with hibernate 4.1.6.Final

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

On Tue, Sep 25, 2012 at 3:12 PM, Freddie Burgess
<fburgess(at)radiantblue(dot)com> wrote:
>
> 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?

don't know about hibernate or that strange way to insert into partitions.
the postgresql's way is to create triggers on parent table to route
the inserts into the partitions as described here:
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

> Would upgrading from 4.1.6.Final to Hibernate 4.1.7 in Linux, fix this
> partitioning ingest problem?
>

don't know, but that certainly looks like a hibernate bug, not a postgresql one

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

Hi Jaime,

We have our partitioning setup exactly like the instructions in
ddl-partitioning.html and prior to the upgrade to Hibernate 4.1.6 ingest was
working flawlessly as long as we set the
hibernate.jdbc.factory_class=im.empl.core.service.ingest.postgres.PostgresPa
rtitionBatcherFactory in Hibernate. If we comment out this setting in our
old configuration using Hibernate 3.2.2.GA inserts into our partitioned
tables breaks as well.

I'm not sure how org.hibernate.jdbc.Batcher is affecting partition inserts.

I previously omitted the return call to the PostgresPartitionBatcher module
component to the batcher process.

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

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.engine.jdbc.batch.spi.Batch;
import org.hibernate.engine.jdbc.batch.spi.BatchKey;
import org.hibernate.engine.jdbc.batch.spi.BatchObserver;
import org.hibernate.engine.jdbc.spi.JdbcCoordinator;

public class PostgresPartitionBatcher implements Batch

{

private static Logger logger =
Logger.getLogger("PostgresPartitionBatcher");


protected void doExecuteBatch() {

try {
/*
* use reflection to access the private field of the
super class
* ugly but necessary
*/
Field field =
this.getClass().getSuperclass().getDeclaredField(
"batchSize");
field.setAccessible(true);

int batchSize = field.getInt(this);
if (batchSize != 0) {
try {
// ps.executeBatch();
}
catch (RuntimeException re) {
throw re;
}
finally {
batchSize = 0;
field.setInt(
this,
batchSize);
}
}
}
catch (Exception e) {
logger.error("Batch execution failed.", e);
throw new HibernateException(
"Unable to execute batch.");
}
}

@Override
public void addObserver(BatchObserver arg0) {
// TODO Auto-generated method stub

}

@Override
public void addToBatch() {
// TODO Auto-generated method stub

}

@Override
public void execute() {
// TODO Auto-generated method stub

}

@Override
public PreparedStatement getBatchStatement(String arg0, boolean
arg1) {
// TODO Auto-generated method stub
return null;
}

@Override
public BatchKey getKey() {
// TODO Auto-generated method stub
return null;
}

@Override
public void release() {
// TODO Auto-generated method stub

}
}

-----Original Message-----
From: jcasanov(at)systemguards(dot)com(dot)ec [mailto:jcasanov(at)systemguards(dot)com(dot)ec] On
Behalf Of Jaime Casanova
Sent: Tuesday, September 25, 2012 4:50 PM
To: Freddie Burgess
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] Postgres Partitions not working with hibernate
4.1.6.Final

On Tue, Sep 25, 2012 at 3:12 PM, Freddie Burgess <fburgess(at)radiantblue(dot)com>
wrote:
>
> 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?

don't know about hibernate or that strange way to insert into partitions.
the postgresql's way is to create triggers on parent table to route the
inserts into the partitions as described here:
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

> Would upgrading from 4.1.6.Final to Hibernate 4.1.7 in Linux, fix this
> partitioning ingest problem?
>

don't know, but that certainly looks like a hibernate bug, not a postgresql
one

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación