[hibernate-dev] 'INSERT' transactions are always rolled back on PostgreSQL when partitioning is used.

[Julius Stroffek]

Hi All,

I have created a simple java application running on glassfish and using 
a hibernate as a persistence provider on PostgreSQL. I just simply 
insert display and delete rows from a table.

Everything worked fine but when I use partitioning on tables as described at
http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html

The application started to report every insert to the table as 
"Transaction marked for rollback" and throwing an exception

javax.persistence.OptimisticLockException: 
org.hibernate.StaleStateException: Batch update returned unexpected row 
count from update [0]; actual row count: 0; expected: 1
        at 
org.hibernate.ejb.AbstractEntityManagerImpl.wrapStaleStateException(AbstractEntityManagerImpl.java:654)
        at 
org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:600)
        at 
org.hibernate.ejb.AbstractEntityManagerImpl$1.beforeCompletion(AbstractEntityManagerImpl.java:525)
        .... (plenty of more lines)
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:61)
        at 
org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:46)
        at 
org.hibernate.jdbc.BatchingBatcher.checkRowCounts(BatchingBatcher.java:68)
        at 
org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
        at 
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
        at 
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
        at 
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
        at 
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at 
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
        at 
org.hibernate.ejb.AbstractEntityManagerImpl$1.beforeCompletion(AbstractEntityManagerImpl.java:516)
        ... 50 more

This is due that Statement.executeBatch returns the incorrect number of 
rows affected by the transaction. This number returned by PostgreSQL is 
defined at
http://www.postgresql.org/docs/8.2/static/rules-status.html

Since the rule system in general is too complex in PostgreSQL it is not 
possible to define the command status string so it would contain the 
number of rows affected by the transaction. I think that JDBC driver 
should return Statement.SUCCESS_NO_INFO in this case, however, it is not 
doing so now and the JDBC spec does not allow to return 
Statement.SUCCESS_NO_INFO in Statement.executeUpdate.

I had a look at hibernate source where there are couple of 
"Expectations" implementations but I have not found how it is possible 
to choose different implementation in my application. A workaround for 
this could be that I can use Expectation.NONE as an "Expectations" 
instance. Is it possible to set this up in an application 
code/configuration? I have not found anything like this in documentation.

Thanks for your comments.

Cheers

Julo