Composite id in hibernate+postgres breaks due to returned column order

Question

I have a parent object with composite-id (legacy db - can't modify this). I have a child object that is a bidirectional one-to-many (parent-to-children) relationship. The mapping is correct, as I can load an instance of either entity and correctly navigate across the relationship. My problem comes when I store the parent and it cascades to the child. The Postgres dialect is issuing a query of the form:

"insert into tablename (column1, column2, column3, column4) values (value1, value2, value3, value4) returning *"

Which is a nice postgres shortcut for returning all values of the row just inserted. However, columns come back in arbitrary order set by the db, though it is a standard resultset with all of the column metadata included. However, postgres appears to be assuming that the columns coming back are in some arbitrary order.

The table in question has a btime and mtime field which are updated via a trigger on insert. Both are timestamp columns. Those are the first two columns that come back. I spent a while attempting to debug hibernate, but it is a slow process. I believe that what is happening is that the first timestamp column is assumed to be the generated id column, and it is failing when it attempts to convert the timestamp string into a Long. In fact, the generated id shows up as the 4th column.

Caused by: org.postgresql.util.PSQLException: Bad value for type long : 2010-02-21 18:11:19.774362
 at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796)
 at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019)
 at org.hibernate.id.IdentifierGeneratorFactory.get(IdentifierGeneratorFactory.java:104)
 at org.hibernate.id.IdentifierGeneratorFactory.getGeneratedIdentity(IdentifierGeneratorFactory.java:92)
 at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:98)
 at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57)

I believe that this is somehow related to using a composite key as I've used a nearly identical setup for btime and mtime columns in another application, which has a hibernate-specific schema that uses generated long ids everywhere. Because btime and mtime come from a parent table that all other tables in the db inherit, there is no way to change the order of the columns.

The net result is that both the parent insert and the cascaded child insert do succeed, but then hibernate throws an exception after it fails to load the generated field(s) for the child entity. This feels very much like a bug in hibernate, and it is one which has stopped me cold. I'm hoping someone knows a workaround or a bug fix.

I'm using hibernate-3.3.1-GA as distributed by springsource in the package of dependencies with the latest spring 3.0.1 release

CREATE TABLE parent_stat (
      btime timestamp DEFAULT NOW() NOT NULL,  -- Birth Time or Creation Time
      mtime timestamp DEFAULT NOW() NOT NULL,   -- Modified Time
      enabled boolean DEFAULT true NOT NULL
);

CREATE TABLE portal.parent_persistent (
    version  int   DEFAULT 1     NOT NULL   -- Version Number
);


CREATE TABLE portal.customers
(
  customer_id int NOT NULL,
  zone_id int NOT NULL,
  <other properties go here>
  CONSTRAINT pk_customers PRIMARY KEY (zone_id, customer_id)
) INHERITS (portal.parent_stat, portal.parent_persistent);

CREATE TABLE portal.users
(
  user_id bigserial NOT NULL,
  customer_zone_id int NOT NULL,
  customer_id int NOT NULL,
  <more properties here>
  CONSTRAINT pk_users_user_id PRIMARY KEY (user_id),
  CONSTRAINT fk_users_customers FOREIGN KEY (customer_zone_id, customer_id) REFERENCES customers(zone_id, customer_id) ON UPDATE RESTRICT ON DELETE RESTRICT
) INHERITS (portal.parent_stat, portal.parent_persistent);

<hibernate-mapping>
    <class name="CustomerImpl" proxy="Customer" schema="portal" table="customers">
        <composite-id name="key" class="CustomerKeyImpl">
            <key-property name="zoneId" type="int" column="zone_id"/>
            <key-property name="customerId" type="int" column="customer_id"/>
        </composite-id>
        &version;
        &auditable;
        <set name="users" lazy="true" inverse="true" order-by="lower(email) asc" cascade="save-update,delete">
            <key>
                <column name="customer_zone_id"/>
                <column name="customer_id"/>
            </key>
            <one-to-many class="UserImpl"/>
        </set>
    </class>
</hibernate-mapping>

<hibernate-mapping default-lazy="true">
    <class name="UserImpl" proxy="User" schema="portal" table="users">
        <id name="id" type="java.lang.Long" column="user_id">
            <generator class="identity"/>
        </id>
        &version;
        &auditable;
        <many-to-one name="customer" class="CustomerImpl" not-null="true" cascade="save-update">
            <column name="customer_zone_id"/>
            <column name="customer_id"/>
        </many-to-one>
    </class>
</hibernate-mapping>

The version entity and the auditable entity are defined as follows:

<version name="version" column="version" unsaved-value="null" type="java.lang.Long"/>

and

<property name="created" type="java.util.Calendar" column="btime" generated="insert" insert="false" update="false"/>
<property name="modified" type="java.util.Calendar" column="mtime" generated="always" insert="false" update="false"/>

Finally, the following stored procedure is set up to execute before insert on both tables, which is how the mtime gets updated.

CREATE OR REPLACE FUNCTION touchrow() RETURNS TRIGGER AS $$
DECLARE
 mtime timestamp NOT NULL DEFAULT NOW();
BEGIN

NEW.mtime := mtime;

RAISE DEBUG 'mtime=%', NEW.mtime;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

All of the this functionality, including the parent tables and the stored proc for updating the mtime have been used in other apps. The only difference is the composite key of the parent object.

Note: I can store the parent object without a reference to the child without difficulty. If I look at my sql logs, I can see that hibernate issues a separate select after performing the insert in that case - I presume it is the difference between cascaded save and not, or else the different between composite primary key and composite foreign key. The dialect only issues the "insert...returning *" syntax on the child object, and it does it whether I save the parent first, then add the child before saving the child, or if I just let the parent cascade to the child (or vice versa).

Answer 1

I was never able to figure out a fix for this problem. The only reasonable workaround was to stop using identity key generation and go to a sequence id generator instead. That caused hibernate to not attempt to use the 'returning' clause of an insert/update statement.