Re: Best practices for update timestamp with/without triggers

On Tue, Apr 22, 2014 at 01:16:15AM +0000, Verghese, Riya wrote:
> I am going to add a new column to a table for modify_date that needs
> to be updated every time the table is updated. Is it better to just
> update application code to set the modify_date to current_time, or
> create a Before-Update trigger on the table that will update the
> modify_date column to current_timestamp when the table is updated?
> I also have slony in place, so the trigger will need to be on master
> and slave. Slony will take care of suppressing it on the slave and
> enabling in the event of a switchover, but it is additional overhead
> and validation to make sure nothing failed on switchover.
> So considering that we have slony, is it better to use application
> code to update the modify_date or use a trigger?Is a trigger
> essentially 2 updates to the table? Are there any other risks in using
> the trigger?

It's better (in my opinion) to use trigger. And it's not two updates.

Just make your trigger function like:

create function sample_trigger() returns trigger as $$
BEGIN
NEW.modify_date := clock_timestamp();
RETURN NEW;
END;
$$ language plpgsql;

and that's all.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/