Date: Wed, 23 Apr 2014 12:28:59 +0200
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
Message-ID: <20140423102859.GE4362@depesz.com>
Reply-To: depesz@depesz.com
References: <CF7B10ED.4EEE2%rverghese@cobalt.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature"; boundary="wTWi5aaYRw9ix9vO"
Content-Disposition: inline
In-Reply-To: <CF7B10ED.4EEE2%rverghese@cobalt.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--wTWi5aaYRw9ix9vO
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 :=3D clock_timestamp();
    RETURN NEW;
END;
$$ language plpgsql;

and that's all.

Best regards,

depesz

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

--wTWi5aaYRw9ix9vO
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----

iQIcBAEBCgAGBQJTV5XjAAoJEH8YKMd1okYDxXYQAMY+AxWO8c1Q9eF9y3n97BAL
XSBqxnqM5rdfniMz4U/Y95tPJlSUYeNWLxbASrWVICHOPx51vIlJbmAwIuS0xR7F
quBRaVLT3cjSfKhV4iJZvCpvo8MfOa2iQd/U9JDkhPKB9mKEjfjR3Np9KduyNpdD
1thH1vUSBtaBCW9tfJcIorr9ZygaRpCEtiOTnNxkCjXnwY8qa7r3rNb2l47HigUW
87N8322aJ4JHTlsJTQQ13M0FfEw1t7LJ6mMmYKCWKVY06rt9fn9asmirpeIfE7sU
hjwpwvg6flKE97AmqhttdK7A6sEvOKHwvrT/ELiqxA8/4OAjE7ZdB5Ibpcl1PZu6
LFf5XzUpuHM1c4vOWKgSi0mr6TilpHG7RMKJMBpAReTZGf4mV/2F04P0uS7kiVAA
zv7gEBopwCJdr6RC0NIy8Jbhyn1jKbtHAIAVyMpCJvjJSgdqIbe+fUdbJXNDOwrO
MFQ9zkMgBEUarowexc+M2GOEPde3pD3Awwwdm82eNv+gT7ke45yNYdFQvn2EDFhS
XVVjRW60T2KsmCyxx0hSTumaHrWO4xu5tZQ2NCssbzKuW5noqfJhaJYEq2qrC1vW
0tR6tkK9L6o470jFCLAUTjShizIAwemagPnDXLSuKINAPozj5Bp38kKt01bbyplw
SkSLTRsMqa66Yv3QJfoe
=rXhS
-----END PGP SIGNATURE-----

--wTWi5aaYRw9ix9vO--