From: "Verghese, Riya" <rverghese@cobalt.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Best practices for update timestamp with/without triggers
Thread-Topic: Best practices for update timestamp with/without triggers
Thread-Index: AQHPXch0QVHSRIw+DUKgAmsJa8P+IQ==
Date: Tue, 22 Apr 2014 01:16:15 +0000
Message-ID: <CF7B10ED.4EEE2%rverghese@cobalt.com>
Accept-Language: en-US
Content-Language: en-US
user-agent: Microsoft-MacOutlook/14.3.9.131030
Content-Type: multipart/alternative;
 boundary="_000_CF7B10ED4EEE2rverghesecobaltcom_"
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--_000_CF7B10ED4EEE2rverghesecobaltcom_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
X-NAIMIME-Disclaimer: 1
X-NAIMIME-Modified: 1

Hi
I am going to add a new column to a table for modify_date that needs to b=
e updated every time the table is updated. Is it better to just update ap=
plication 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 c=
urrent_timestamp when the table is updated? I also have slony in place, s=
o the trigger will need to be on master and slave. Slony will take care o=
f 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 t=
o update the modify_date or use a trigger?Is a trigger essentially 2 upda=
tes to the table? Are there any other risks in using the trigger?

Thanks
Riya Verghese


This message and any attachments are intended only for the use of the add=
ressee and may contain information that is privileged and confidential. I=
f the reader of the message is not the intended recipient or an authorize=
d representative of the intended recipient, you are hereby notified that =
any dissemination of this communication is strictly prohibited. If you ha=
ve received this communication in error, please notify us immediately by =
e-mail and delete the message and any attachments from your system.

--_000_CF7B10ED4EEE2rverghesecobaltcom_
Content-Type: text/HTML;
  charset="utf-8"
Content-ID: <B990FE3CA1296544B58DA295C4ACF81D@ds.local>
Content-Transfer-Encoding: base64
X-NAIMIME-Disclaimer: 1
X-NAIMIME-Modified: 1

PGh0bWw+CjxoZWFkPgo8bWV0YSBodHRwLWVxdWl2PSJDb250ZW50LVR5cGUiIGNvbnRlbnQ9InRl
eHQvaHRtbDsgY2hhcnNldD11cy1hc2NpaSI+CjwvaGVhZD4KPGJvZHkgc3R5bGU9IndvcmQtd3Jh
cDogYnJlYWstd29yZDsgLXdlYmtpdC1uYnNwLW1vZGU6IHNwYWNlOyAtd2Via2l0LWxpbmUtYnJl
YWs6IGFmdGVyLXdoaXRlLXNwYWNlOyBjb2xvcjogcmdiKDAsIDAsIDApOyBmb250LXNpemU6IDE0
cHg7IGZvbnQtZmFtaWx5OiBDYWxpYnJpLCBzYW5zLXNlcmlmOyI+CjxkaXY+SGk8L2Rpdj4KPGRp
dj5JIGFtIGdvaW5nIHRvIGFkZCBhIG5ldyBjb2x1bW4gdG8gYSB0YWJsZSBmb3IgbW9kaWZ5X2Rh
dGUgdGhhdCBuZWVkcyB0byBiZSB1cGRhdGVkIGV2ZXJ5IHRpbWUgdGhlIHRhYmxlIGlzIHVwZGF0
ZWQuIElzIGl0IGJldHRlciB0byBqdXN0IHVwZGF0ZSBhcHBsaWNhdGlvbiBjb2RlIHRvIHNldCB0
aGUgbW9kaWZ5X2RhdGUgdG8gY3VycmVudF90aW1lLCBvciBjcmVhdGUgYSBCZWZvcmUtVXBkYXRl
IHRyaWdnZXIgb24gdGhlIHRhYmxlIHRoYXQKIHdpbGwgdXBkYXRlIHRoZSBtb2RpZnlfZGF0ZSBj
b2x1bW4gdG8gY3VycmVudF90aW1lc3RhbXAgd2hlbiB0aGUgdGFibGUgaXMgdXBkYXRlZD8gSSBh
bHNvIGhhdmUgc2xvbnkgaW4gcGxhY2UsIHNvIHRoZSB0cmlnZ2VyIHdpbGwgbmVlZCB0byBiZSBv
biBtYXN0ZXIgYW5kIHNsYXZlLiBTbG9ueSB3aWxsIHRha2UgY2FyZSBvZiBzdXBwcmVzc2luZyBp
dCBvbiB0aGUgc2xhdmUgYW5kIGVuYWJsaW5nIGluIHRoZSBldmVudCBvZiBhIHN3aXRjaG92ZXIs
CiBidXQgaXQgaXMgYWRkaXRpb25hbCBvdmVyaGVhZCBhbmQgdmFsaWRhdGlvbiB0byBtYWtlIHN1
cmUgbm90aGluZyBmYWlsZWQgb24gc3dpdGNob3Zlci4mbmJzcDs8L2Rpdj4KPGRpdj5TbyBjb25z
aWRlcmluZyB0aGF0IHdlIGhhdmUgc2xvbnksIGlzIGl0IGJldHRlciB0byB1c2UgYXBwbGljYXRp
b24gY29kZSB0byB1cGRhdGUgdGhlIG1vZGlmeV9kYXRlIG9yIHVzZSBhIHRyaWdnZXI/SXMgYSB0
cmlnZ2VyIGVzc2VudGlhbGx5IDIgdXBkYXRlcyB0byB0aGUgdGFibGU/IEFyZSB0aGVyZSBhbnkg
b3RoZXIgcmlza3MgaW4gdXNpbmcgdGhlIHRyaWdnZXI/PC9kaXY+CjxkaXY+PGJyPgo8L2Rpdj4K
PGRpdj5UaGFua3M8L2Rpdj4KPGRpdj5SaXlhIFZlcmdoZXNlPC9kaXY+Cgo8RElWPjxQPjxIUj4K
PEg1PjxGT05UIGNvbG9yPWdyZXk+VGhpcyBtZXNzYWdlIGFuZCBhbnkgYXR0YWNobWVudHMgYXJl
IGludGVuZGVkIG9ubHkgZm9yIHRoZSB1c2Ugb2YgdGhlIGFkZHJlc3NlZSBhbmQgbWF5IGNvbnRh
aW4gaW5mb3JtYXRpb24gdGhhdCBpcyBwcml2aWxlZ2VkIGFuZCBjb25maWRlbnRpYWwuIElmIHRo
ZSByZWFkZXIgb2YgdGhlIG1lc3NhZ2UgaXMgbm90IHRoZSBpbnRlbmRlZCByZWNpcGllbnQgb3Ig
YW4gYXV0aG9yaXplZCByZXByZXNlbnRhdGl2ZSBvZiB0aGUgaW50ZW5kZWQgcmVjaXBpZW50LCB5
b3UgYXJlIGhlcmVieSBub3RpZmllZCB0aGF0IGFueSBkaXNzZW1pbmF0aW9uIG9mIHRoaXMgY29t
bXVuaWNhdGlvbiBpcyBzdHJpY3RseSBwcm9oaWJpdGVkLiBJZiB5b3UgaGF2ZSByZWNlaXZlZCB0
aGlzIGNvbW11bmljYXRpb24gaW4gZXJyb3IsIHBsZWFzZSBub3RpZnkgdXMgaW1tZWRpYXRlbHkg
YnkgZS1tYWlsIGFuZCBkZWxldGUgdGhlIG1lc3NhZ2UgYW5kIGFueSBhdHRhY2htZW50cyBmcm9t
IHlvdXIgc3lzdGVtLiA8L0ZPTlQ+PC9INT4KPC9QPjwvRElWPgo8L2JvZHk+CjwvaHRtbD4K

--_000_CF7B10ED4EEE2rverghesecobaltcom_--