Message-ID: <43E74594.5040209@openwired.net>
Date: Mon, 06 Feb 2006 13:48:20 +0100
From: =?ISO-8859-1?Q?Josep_Sanmart=ED?= <josep.sanmarti@openwired.net>
User-Agent: Mozilla Thunderbird 1.0.5 (X11/20050711)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Trigger efficiency
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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!

-- 
Josep Sanmarti
Analista de Projectes

OpenWired
Caballero 87 - Bajos
08029 - Barcelona
Tel. 93 495 0990
Fax. 93 419 4591

Openwired
Alejandro Villegas,29
28043 - MADRID - ESPAÑA
Teléfono: 91 300 51 09
Fax:  91 300 28 13
http://www.openwired.com