From: Richard Huxton <dev@archonet.com>
To: Samuel Tardieu <sam@rfc1149.net>
To: pgsql-sql@postgresql.org
Subject: Re: Maintaining a counter up-to-date
Date: Mon, 2 Jun 2003 19:27:58 +0100
Message-ID: <200306021927.58636.dev@archonet.com> (raw)
In-Reply-To: <871xyc5vmw.fsf@inf.enst.fr>
References: <871xyc5vmw.fsf@inf.enst.fr>
On Monday 02 Jun 2003 4:47 pm, Samuel Tardieu wrote:
> I have an "ips" table with 100000+ records, each record having a
> "catid" field representing its category. "catid" references a row in a
> table called "categories".
>
> For statistics purpose (generation of images with the evolution of the
> number of rows by category), I am trying to reduce the load on the
> database.
>
> The request I was doing at the beginning was:
>
> SELECT catid, COUNT(*) FROM ips GROUP BY catid;
>
> I then added a "nentries" field to the "categories" table with some
> rules to maintain the counters up-to-date:
>
> CREATE RULE cat_ins AS
[snip]
> This works fine when inserting, deleting or updating one row in the
> "ips" table. However, when i/d/u several rows at a time with the same
> "catid", I only got an increment or decrement by one of the counter.
You want to use triggers not rules here (see the "server programming" and
"procedural language" manual sections). A trigger will be fired for each row
inserted/deleted/updated. Of course this means it will be fired 5000 times
for 5000 updated rows.
--
Richard Huxton
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: dev@archonet.com, sam@rfc1149.net
Subject: Re: Maintaining a counter up-to-date
In-Reply-To: <200306021927.58636.dev@archonet.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