Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Samuel Tardieu <sam@rfc1149.net>, pgsql-sql@postgresql.org
Subject: Re: Maintaining a counter up-to-date
Date: Mon, 2 Jun 2003 19:27:58 +0100
User-Agent: KMail/1.4.3
References: <871xyc5vmw.fsf@inf.enst.fr>
In-Reply-To: <871xyc5vmw.fsf@inf.enst.fr>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200306021927.58636.dev@archonet.com>

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=
=20
"procedural language" manual sections). A trigger will be fired for each ro=
w=20
inserted/deleted/updated. Of course this means it will be fired 5000 times=
=20
for 5000 updated rows.

--=20
  Richard Huxton