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