From: Josep Sanmartí <josep.sanmarti@openwired.net>
To: pgsql-sql@postgresql.org
Subject: Trigger efficiency
Date: Mon, 06 Feb 2006 13:48:20 +0100
Message-ID: <43E74594.5040209@openwired.net> (raw)
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
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: josep.sanmarti@openwired.net
Subject: Re: Trigger efficiency
In-Reply-To: <43E74594.5040209@openwired.net>
* 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