Re: Postgres JDBC-hibernate Problem - Mailing list pgsql-bugs

Here is a more detailed write-up of our current problem.

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.PostgresP
artitionBatcherFactory
...
...
...

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(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.core.service.ingest.DefaultEmplIngestManager.ingest(DefaultEmplInges
tManager.java:383)
        at
im.empl.core.service.ingest.AutoEmplIngestMonitor.ingest(AutoEmplIngestMonit
or.java:142)
        at
im.empl.core.service.ingest.AutoEmplIngestMonitor$1.run(AutoEmplIngestMonito
r.java:104)
Caused by: org.hibernate.TransactionException: nested transactions not
supported
        at
org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractT
ransactionImpl.java:152)
        at
org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1396)
        at
im.empl.core.service.ingest.EmployeeWriter.handleEmployeeSegment(EmployeeWri
ter.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


-----Original Message-----
From: Craig Ringer [mailto:ringerc@ringerc.id.au]
Sent: Tuesday, September 18, 2012 12:29 AM
To: Freddie Burgess
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Postgres JDBC-hibernate Problem

On 09/18/2012 01:23 AM, Freddie Burgess wrote:
> Thanks Craig,
>
> We were able to make the necessary adjustments to the way Hibernate
> manages the data types differently in version 4.1.6, so we got pass
> this error. Now we have to tackle the a problem with the hibernate
> 4.1.6 batcher process no longer allowing us to ingest data into the
> database. We are getting the "nested transactions not supported
> error", even though the java developers are telling me that they have
> only a single commit at the end of  their logical transaction.

Look at the PostgreSQL logs and see. It's easier to trace if you set
log_line_prefix to something like:

log_line_prefix = '<a=%a,u=%u,db=%d,pid=%p,vtx=%v,tx=%x,cmd=%i>'

which produces significantly more verbose logs but makes it easier to, using
the pid and txix/vtxid info, work out how different logged statements fit
into transactions.

--
Craig Ringer