From: Craig Servin <cservin@cromagnon.com>
Organization: Cro-Magnon
To: pgsql-sql@postgresql.org
Subject: Re: Trigger efficiency
Date: Mon, 6 Feb 2006 09:14:41 -0600
User-Agent: KMail/1.9
Cc: Josep =?iso-8859-1?q?Sanmart=ED?= <josep.sanmarti@openwired.net>
References: <43E74594.5040209@openwired.net>
In-Reply-To: <43E74594.5040209@openwired.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200602060914.41173.cservin@cromagnon.com>

I do not know of a way to make your trigger run less often, but you could o=
nly=20
have it do the insert if something changes.  This is what we do:


CREATE or replace FUNCTION UPDATE_SERVER_HST() RETURNS TRIGGER AS '
begin
	if (OLD.ADD_DATE is distinct from NEW.ADD_DATE
	or OLD.HOSTNAME is distinct from NEW.HOSTNAME
	) then

		insert into SERVER_HST( OPERATION, HST_USR_ID, HST_ADD_DATE, SERVER_ID,=09
UPDATE_COUNT, ADD_DATE, HOSTNAME) values( substr( TG_OP, 1, 1 ),=20
GET_CONNECTION_USR(), now()
, OLD.SERVER_ID, OLD.UPDATE_COUNT, OLD.ADD_DATE, OLD.HOSTNAME);
	end if;

RETURN NULL; -- result is ignored since this is an AFTER trigger

end;
'
language plpgsql
security definer;

That way the insert only happens if there is a change.  Since this is a pai=
n=20
we have a program that writes our history and update_count triggers for us=
=20
based on what we run through our modeling tool.

Craig


On Monday 06 February 2006 06:48, Josep Sanmart=ED wrote:
> Hi,
>
> I've the following problem and I don't know how to solve it: There is a
> table with about 10-12 fields, a couple of those fields are updated very
> often (about 30 times / minute or even more). This is my table (more or
> less):
>
> crete table monitor(
>     time Timestamp,
>     time2 timestamp,
>     ...
>     status int,
> );
>
> I need to know how many rows are in the table and keep that number to
> another table every time that there is an INSERT or DELETE on that
> table, so I made a trigger that fires on insert and delete events and
> calls a function that makes de update on the new table, that works well.
> My problem starts when I have to count the rows depending on the status
> field: I added a UPDATE on my trigger and it works! :)...... my trigger:
>
> CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE
>     ON monitor EXECUTE PROCEDURE f_barra_aps();
>
> But now, this trigger fires too often and there is an extra load on the
> system that makes everything run slower. I posted before a question
> asking about firing triggers on specific columns...thats not possible. I
> don't know how to solve it, perhaps I should do it without
> triggers.....any help will be appreciated
>
> thanks!