We have a process that feeds three Postgresql database tables, two of which are partitioned tables. There are no foreign key constraint dependencies between any of these tables.

Data get parsed and subsequently ingested into these tables in no particular order, but the process fails to load a single row!

Background

Previously, when we had hibernate 3.22 GA running. it was necessary for us to add the following hibernate configuration parameter to address inserts into the two partitioned tables on 3.22 GA:

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

Problem

This setting is no longer configurable in Hibernate 4.1.2

Table(s) included in the logical transaction Summary

Table name Structure
---------- ---------------
Employee non-partitioned
stockoptions partitioned
trading partitioned

While attempting to upgrade Hibernate to release 4.1.2, we started to receive the "nested transactions not supported" exception error

11:21:10,897 [Thread-7250] INFO im.empl.core.service.ingest.OptionsWriter:171 - Received stockoptions segment before any Employee segment.
11:21:10,897 [Thread-7250] INFO im.empl.core.service.ingest.TradingWriter:297 - Received trading segment before any Employee segment.
11:21:10,897 [Thread-7250] INFO im.empl.core.service.ingest.TradingWriter:297 - Received trading segment before any Employee segment.
11:21:10,897 [Thread-7250] INFO im.empl.core.service.ingest.TradingWriter:297 - Received trading segment before any Employee segment.
11:21:10,897 [Thread-53] WARN org.bushe.swing.event.EventService:? - Exception thrown by;EventService subscriber:im.empl.core.service.ingest.EmployeeWriter$3@4dd25f53. Subscriber class:class im.empl.core.service.ingest.EmployeeWriter$3
org.bushe.swing.exception.SwingException: Exception handling event topic event class=im.empl.core.io.parse.impl.Axisgraph78StreamReaderImpl$3, event=im.empl.core.io.parse.impl.Axisgraph78StreamReaderImpl$3@65770081, topic=null, eventObj=null
org.bushe.swing.exception.SwingException: Exception handling event topic event class=im.empl.core.io.parse.impl.Axisgraph78StreamReaderImpl$3, event=im.empl.core.io.parse.impl.Axisgraph78StreamReaderImpl$3@65770081, topic=null, eventObj=null
at org.bushe.swing.event.ThreadSafeEventService.handleException(ThreadSafeEventService.java:2021)
at org.bushe.swing.event.ThreadSafeEventService.handleException(ThreadSafeEventService.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(BusheApplicationEventService.java:28)
at im.empl.core.service.ingest.DefaultEmplIngestManager.ingest(DefaultEmplIngestManager.java:383)
at im.empl.core.service.ingest.AutoEmplIngestMonitor.ingest(AutoEmplIngestMonitor.java:142)
at im.empl.core.service.ingest.AutoEmplIngestMonitor$1.run(AutoEmplIngestMonitor.java:104)
Caused by: org.hibernate.TransactionException: nested transactions not supported
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:152)
at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1396)
at im.empl.core.service.ingest.EmployeeWriter.handleEmployeeSegment(EmployeeWriter.java:309)
at im.empl.core.service.ingest.EmployeeWriter$3.onEvent(EmployeeWriter.java:116)
at im.empl.core.service.ingest.EmployeeWriter$3.onEvent(EmployeeWriter.java:113)
at org.bushe.swing.event.ThreadSafeEventService.publish(ThreadSafeEventService.java:971)
... 5 more

* Application tier configuration

From core-db.xml:
<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="controlSessionFactory"/>
</bean>

<!-- enable the configuration of transactional behavior based on annotations -->
<tx:annotation-driven transaction-manager="transactionManager" />

In most cases we are using Spring’s HibernateTransactionManager, and annotations on methods that are transactional. If there is some type of exception in the method,
the transaction is rolled back automatically.

The Employee ingest behaves a bit differently – it handles transactions programmatically, for example:

Transaction tx = session.beginTransaction();
session.save(Employee);
tx.commit();

Could it be possible that if an exception is thrown by the session.save() method that the transaction might not be rolled back or closed, although no errors pertaining
to this scenario were recorded in the PostgreSQL log.

The TradingWriter makes use of the BatchWriter class, which also handles transactions programmatically.

Any workarounds to resolve this issue would be greatly appreciated.

thanks

I suggest you to try latest release Hibernate 4.1.7 since a lot of bugs have been fixed since version 4.1.2.

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.PostgresPartitionBatcherFactory

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(ThreadSafeEventService.java:2021)
at org.bushe.swing.event.ThreadSafeEventService.handleException(ThreadSafeEventService.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(BusheApplicationEventService.java:28)
at im.empl.cores.service.ingest.DefaultcoresIngestManager.ingest(DefaultcoresIngestManager.java:383)
at im.empl.cores.service.ingest.AutocoresIngestMonitor.ingest(AutocoresIngestMonitor.java:142)
at im.empl.cores.service.ingest.AutocoresIngestMonitor$1.run(AutocoresIngestMonitor.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.java:81)
at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:73)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:57)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2962)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.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(DefaultFlushEventListener.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:339)
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:

Do you know why the previous jdbc.batcher logic managed the 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 problem?

thanks