tsearch2 avoiding firing of triggers.....

On Thu, Dec 16, 2004 at 02:21:22PM -0800, Net Virtual Mailing Lists wrote:

I have various triggers and rules in my database, mostly for keeping
tsearch2 updated and (now) materialized views. I'd say probably 90% of
the updates to my database do not require these triggers/rules to fire
off, for example the materialized view trigger only needs to execute when
the category a record is in changes. The tsearch2 stuff, only when the
relevant data fields have changed.

Is there some way to either explicitly prevent the trigger/rules from
firing, or modify them in some way so that they only update when necessary?

A trigger can return NULL to abandon the entire operation, which
would also prevent subsequent triggers from firing, but that's
probably not what you want since it would prevent the UPDATE from
happening at all. Maybe you could modify your trigger functions
to do something like this:

IF NEW.fieldname IS DISTINCT FROM OLD.fieldname THEN
-- do trigger stuff
END IF;

However, I'm not sure how you'd use that with tsearch2 because your
trigger probably looks like this:

CREATE TRIGGER fooupdate BEFORE UPDATE OR INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxfield, fieldname);

You might have to write your own trigger function that mimics
tsearch2(), but only if the field value changes.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/