From: hubert depesz lubaczewski <depesz@depesz.com>
To: Verghese, Riya <rverghese@cobalt.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Best practices for update timestamp with/without triggers
Date: Wed, 23 Apr 2014 12:28:59 +0200
Message-ID: <20140423102859.GE4362@depesz.com> (raw)
In-Reply-To: <CF7B10ED.4EEE2%rverghese@cobalt.com>
References: <CF7B10ED.4EEE2%rverghese@cobalt.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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/Attachments:
signature.asc (application/pgp-signature, 800B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: depesz@depesz.com, rverghese@cobalt.com
Subject: Re: Best practices for update timestamp with/without triggers
In-Reply-To: <20140423102859.GE4362@depesz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox