Re: Best practice question

On Mon, Apr 21, 2014 at 6:19 PM, Tory M Blue <tmblue(at)gmail(dot)com> 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?

In addition to the David's answer I would like to add the below.

AFAIK Slony does not make any difference here. No, trigger doesn't
mean 2 updates. It supplies its function with a NEW row variable where
you can change necessary columns and return the modified one as a
resulting one. Another risk is the case when you need to update 2
tables on different servers and have their modified_timestamp fields
in sync. Here you need to determine the new value of the column in the
application.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com