From: Craig Servin <cservin@cromagnon.com>
To: pgsql-sql@postgresql.org
Cc: Josep Sanmartí <josep.sanmarti@openwired.net>
Subject: Re: Trigger efficiency
Date: Mon, 6 Feb 2006 09:14:41 -0600
Message-ID: <200602060914.41173.cservin@cromagnon.com> (raw)
In-Reply-To: <43E74594.5040209@openwired.net>
References: <43E74594.5040209@openwired.net>
I do not know of a way to make your trigger run less often, but you could only
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,
UPDATE_COUNT, ADD_DATE, HOSTNAME) values( substr( TG_OP, 1, 1 ),
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 pain
we have a program that writes our history and update_count triggers for us
based on what we run through our modeling tool.
Craig
On Monday 06 February 2006 06:48, Josep Sanmartí 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!
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-sql@postgresql.org
Cc: cservin@cromagnon.com, josep.sanmarti@openwired.net
Subject: Re: Trigger efficiency
In-Reply-To: <200602060914.41173.cservin@cromagnon.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