Mixed surrogate composite key insert in JPA 2.0, PostgreSQL and Hibernate 3.5

Question

First off, we are using JPA 2.0 and Hibernate 3.5 as persistence provider on a PostgreSQL database.

We successfully use the sequence of the database via the JPA 2.0 annotations as an auto-generated value for single-field-surrogate-keys and all works fine.

Now we are implementing a bi-temporal database-scheme that requires a mixed key in the following manner:

Table 1:
id             (pk, integer, auto-generated-sequence)
validTimeBegin (pk, dateTime)
validTimeEnd   (dateTime)
firstName      (varChar)

Now we have a problem. You see, if we INSERT a new element, the field id is auto-generated and that's fine. Only, if we want to UPDATE the field within this scheme, then we have to change the validTimeBegin column WITHOUT changing the id-field and insert it as a new row like so:

BEFORE THE UPDATE OF THE ROW:

|---|-------------------------|-------------------------|-------------------|
| id|      validTimeBegin     |       validTimeEnd      |     firstName     |
|---|-------------------------|-------------------------|-------------------|
|  1| 2010-05-01-10:00:00.000 |                    NULL |            Gerald |
|---|-------------------------|-------------------------|-------------------|

AFTER THE UPDATE OF THE ROW happening at exactly 2010-05-01-10:35:01.788 server-time:

(we update the person with the id:1 to reflect his new first name...)
|---|-------------------------|-------------------------|-------------------|
| id|      validTimeBegin     |       validTimeEnd      |     firstName     |
|---|-------------------------|-------------------------|-------------------|
|  1| 2010-05-01-10:00:00.000 | 2010-05-01-10:35:01.788 |            Gerald |
|---|-------------------------|-------------------------|-------------------|
|  1| 2010-05-01-10:35:01.788 |                    NULL |             Jerry |
|---|-------------------------|-------------------------|-------------------|

So our problem is, that this doesn't work at all using an auto-generated-sequence for the field id because when inserting a new row then the id ALWAYS is auto-generated although it really is part of a composite key which should sometimes behave differently.

So my question is: Is there a way to tell hibernate via JPA to stop auto-generating the id-field in the case I want to generate a new variety of the same person and go on as usual in every other case or do I have to take over the whole id-generation with custom code?

Thanks in advance, Gerald

Answer 1

AFAIK your ID field is not an ID in the sense as Hibernate understands it. If I might propose another solution, given the fact that essentially you just want to realize database modification logging:

• Step 0: Only use ID as a primary key in Hibernate. Use a compound primary key in Postgres.
• Step 1: Add filters to hibernate that automatically filter all rows having validEndTime set.
• Step 2: Add custom update statement to hibernate, that does an insert instead.
• Step 3: Add trigger in PostgreSQL that sets the end time automatically.
• Step 4: Create a constrained index on postgres for faster lookups by hibernate.