From: Bill MacArthur <webmaster@dhs-club.com>
To: pgsql-admin@postgresql.org
Subject: Is there any value in using trigger conditions rather than function conditions
Date: Mon, 22 Apr 2013 12:04:23 -0400
Message-ID: <51755F87.3060808@dhs-club.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hello,
We have elements in our setup that have been around for quite awhile. We are currently running 9.2 and I was wondering if there would be any significant performance increase by moving the conditions out of the function into the trigger... like would avoiding the function load save anything.... or would the function be loaded regardless? The function is used by many tables to avoid having to explicitly define the timestamp in every query. We have a similar trig/func setup for recording the DB operator at the moment.
An example trigger:
CREATE TRIGGER update_members_stamp
BEFORE UPDATE
ON members
FOR EACH ROW
EXECUTE PROCEDURE update_stamp();
The shared function:
CREATE OR REPLACE FUNCTION update_stamp()
RETURNS trigger AS
$BODY$
BEGIN
-- if the stamp is being manually manipulated, then we will leave it alone
IF NEW.stamp ISNULL
OR NEW.stamp = OLD.stamp
THEN NEW.stamp := NOW();
END IF;
RETURN NEW;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 1;
If we did something like this would there be any real benefit on a moderately busy table? (ideally I would like to embed the action in the trigger but that does not seem possible)
CREATE TRIGGER update_members_stamp
BEFORE UPDATE
ON members
FOR EACH ROW
WHEN NEW.stamp ISNULL OR NEW.stamp = OLD.stamp
EXECUTE PROCEDURE update_stamp_x();
CREATE OR REPLACE FUNCTION update_stamp_x()
RETURNS trigger AS
$BODY$
BEGIN
NEW.stamp := NOW();
END IF;
RETURN NEW;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 1;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: webmaster@dhs-club.com
Subject: Re: Is there any value in using trigger conditions rather than function conditions
In-Reply-To: <51755F87.3060808@dhs-club.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