Triggers vs. JPA @PrePersist for creation and update timestamps pros and cons

I am building a new web app and I am using Spring, JPA/Hibernate, and Postgres. Some of my tables have creation_ts and lastupdate_ts columns which are timestamp columns that track when an insert occurred and when the last update occurred on a row.

I am also using a naming convention for columns in my tables so as a matter of design policy every table is guaranteed to have two columns pkey which is an integer surrogate key, and version for optimistic locking.

I have two ways to keep these fields up to date.

Option A: use triggers

This is the solution I have in place right now, I have two Postgres trigger that fire on insert and update and will keep these fields up to date. and I have two classes.

@MappedSuperclass
public abstract class PersistableObject
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="pkey")
    private Integer pkey;

    @Version
    @Column(name="version")
    private Integer version;

    public Integer getPkey()
    {
        return this.pkey;
    }

    public Integer getVersion()
    {
        return this.version;
    }
}

and I have

@MappedSuperclass
public class TimeStampedPersistableObject extends PersistableObject {

    @Column(name = "creation_ts")
    @Temporal(TemporalType.DATE)
    @org.hibernate.annotations.Generated(value = GenerationTime.INSERT)
    private Date    creationTimestamp;

    @Column(name = "update_ts")
    @Temporal(TemporalType.DATE)
    @org.hibernate.annotations.Generated(value = GenerationTime.ALWAYS)
    private Date    updateTimestamp;

    public Date getCreationTimestamp()
    {
        return this.creationTimestamp;
    }

    public Date getUpdateTimestamp()
    {
        return this.updateTimestamp;
    }
}

Option B: Use JPA listeners

In this option I would use JPA listeners to keep to timestamp columns up-to-date.

My Question:

Which of those two approaches is better? As I see things here is my personal list of pros and cons of each option and I very interested from hearing the experience of others with these two choices.

Option A pros:

  1. Database is doing the updates with the triggers so there is no danger of having clock skew in the cluster running the web app.
  2. If a non-JPA application accesses the database the requirement to keep those two columns is enforced.

Option A cons:

  1. Have to do a select after the insert and update to read the values that the triggers put into place.
  2. I am using hibernate annotations to read back the values

Option B pros:

  1. Less typing when creating the DDL
  2. No need to read back values from the database after insert and update
  3. Pure JPA annotations no hibernate specific annotations

Option B cons:

  1. Danger of clock skew in the cluster
  2. Fields set whenever the JPA provider decides to call the callback methods not predictable

How you would solve this problem for a new app where you have total control over the database and the java code.

9
задан MarnixKlooster ReinstateMonica 6 November 2012 в 08:14
поделиться