hibernate unable to delete @Entity with null @Version

Question

i have a JPA @Entity with an @Id defined as

private UUID id;

@Id
@Column(name = "f_id")
@GeneratedValue(generator = "system-uuid")
@Type(type = "pg-uuid")
public UUID getId() {
    return id;
}

and a @Version field defined as

private Timestamp lastModified;

@Version
@Column(name = "f_lastmodified")
@Attribute(required = false)
public Timestamp getLastModified() {
    return lastModified;
}

the resulting column in postgres (generated by hibernate using create-drop) is "timestamp without time zone". this class is used in an application running over postgres. when deleting an entity, i use the following code:

public T delete(UUID id) {
    log.debug("deleting entity {}", id);
    EntityManager em = ... //get from somewhere
    em.flush(); //make sure we're in sync with DB
    T object = em.find(getClassType(), id); //get latest version
    if (object == null) {
        throw new IllegalStateException("could not find an entity with id "+id);
    }
    em.refresh(object);
    em.remove(object);
    em.flush();
    return object;
}

it used to be a simpler find(type,id)+remove, but i've added some extra fluff trying to get it to work. the problem is that the above code isnt working. the error displayed in the log is:

08:25:15,940 INFO  [STDOUT] Hibernate:
08:25:15,950 INFO  [STDOUT]     delete
08:25:15,950 INFO  [STDOUT]         from
08:25:15,950 INFO  [STDOUT]             dpa.filesystem_status
08:25:15,950 INFO  [STDOUT]         where
08:25:15,950 INFO  [STDOUT]             f_id=?
08:25:15,960 INFO  [STDOUT]             and f_lastmodified=?
08:25:45,123 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session: org.hibernate.StaleObjec
tStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [FilesystemStatus#78c839f0-0b12-4df6-b9ec-ac1e52e9b1f1]
        at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:1950) [:3.6.1.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2710) [:3.6.1.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2911) [:3.6.1.Final]

after some debugging i think i found the root cause of the issue: the entity i was trying to delete has NULL in the version field. hibernate builds a prepared statement for the delete (which is printed in the log snippet above), sets the id correctly and then sets NULL for the version field (also logically correct). the hibernate code that does this is in BasicBinder.java line 78 (hibernate core 3.6.1)

st.setNull( index, sqlDescriptor.getSqlType() );

the 2nd argument resolves to 93 at runtime. eventually, running the prepared statement results in 0 rows being deleted and hibernates assumes its because the "f_lastmodified=?" part didnt match and decides someone must have committed a different timestamp in between (==stale entity passed to delete). this is not true.

how do i continue from here?

more specifically:
1. how do i know if 93 is the correct sql type to pass to setNull() ? where can i find a table of "correct" sql types? is it DB-specific (meaning i should go look in postgres docs) or some JDBC standard ?
2. how do i resolve the issue? i cant guarentee a null-free @Version column, so i need this scenario to work
3. is this a known hibernate / postgresql-jdbc-driver issue i missed ?

im using jdk 1.6u24 x64 on win7 x64, jboss 6.1-snapshot, hibernate 3.6.1.final, postgres 9.0, postgres jdbc4 driver 9.0-801

Answer 1

You won't be able to use @Version because of the 2nd point, as the version columns must have a value.

2) how do i resolve the issue? I can't guarentee a null-free @Version column, so i need this scenario to work

To circumvent this, you'll need to either create your own solution for versioning or you'll need to assign a default value to that column. One question, I assume that the rows with null versions are not generated by your application, is this correct?