Re: Hibernate, JBoss, PostgreSQL, timestamp read doesn't match during update

You should not use a trigger to generate the version info for hibernate. You
should use the application's time.

Dave

On Fri, Mar 20, 2009 at 12:36 PM, Roland Roberts <roland(at)astrofoto(dot)org>wrote:

> 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(at)rlenter(dot)com 6818 Madeline Court
> roland(at)astrofoto(dot)org Brooklyn, NY 11220
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>