Re: Hibernate, JBoss, PostgreSQL, timestamp read doesn't match during update - Mailing list pgsql-jdbc

I have no idea where to point the blame on this one.  I have a workaround, but here's the scenario.

I have a timestamp column which I want to use in JBoss/Hibernate as a "version" column so that Hibernate can do opportunistic locking for updates.  The timestamp is actually generated by a trigger on the table.  Here's an edited down copy of the DDL for the table and trigger:

 CREATE TABLE security (
  id                        SERIAL,
  primary_identifier        VARCHAR(10) NOT NULL,
  ...
  active_on                 TIMESTAMP(3),
 );

 DROP FUNCTION security_biur_trg() CASCADE;

 CREATE FUNCTION security_biur_trg() RETURNS TRIGGER AS $$
    BEGIN
        NEW.active_on := CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
        RETURN NEW;
    END;
 $$ LANGUAGE plpgsql;

 CREATE TRIGGER security_biur BEFORE INSERT OR UPDATE
    ON security FOR EACH ROW
    EXECUTE PROCEDURE security_biur_trg();

I have a custom UserVersionType for Hibernate to allow it to treat the column as a version object.  You can see the full post of my code at http://www.hibernate.org/461.html.  The code does work with one caveat that I will come to shortly.

The Hibernate mapping file for this table is
 <?xml version="1.0"?>
 <!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

 <hibernate-mapping>
  <class name="Security">
    <id name="securityId" column="id" type="long" unsaved-value="null">
      <generator class="sequence">
        <param name="sequence">security_id_seq</param>
      </generator>
    </id>
    <natural-id>
      <property name="primaryIdentifier"
                column="primary_identifier"
                type="string"/>
    </natural-id>
    <version name="activeOn"
             column="active_on"
             type="HibernateUTC$TimestampType"
             generated="always"
             insert="false"
             unsaved-value="null"/>
    ...
  </class>
 </hibernate-mapping>

What happens when I actually try to update a row is described in more detail here, http://forum.hibernate.org/viewtopic.php?p=2409286#2409286, but the short answer is that Hibernate thinks the row has been updated by another transaction.  After trying all sorts of different mapping definitions for the active_on column (including letting Hibernate generate the values, which sort-of works), I finally hit upon a simple column definition change that works around the problem:

 active_on timestamp(3)

Yes, restrict the timestamp to millisecond precision.  Somewhere the sub-millisecond parts are getting lost.  I have no idea if it is in the JDBC layer or somewhere in Hibernate.  Any clues on figuring this out?

Oh, the SQL generated by Hibernate puts both the primary key and the active_on column in the "where" clause.  This is what it is supposed to do and how it detects a row as having been updated by another transaction.  In this case, the precision mismatch fools it.

roland

--
                      PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                            6818 Madeline Court
roland@astrofoto.org                           Brooklyn, NY 11220


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc