Re: Maintaining a counter up-to-date - Mailing list pgsql-sql

From Richard Huxton
Subject Re: Maintaining a counter up-to-date
Date
Msg-id 200306021927.58636.dev@archonet.com
Whole thread Raw
In response to Maintaining a counter up-to-date  (Samuel Tardieu <sam@rfc1149.net>)
Responses Re: Maintaining a counter up-to-date
List pgsql-sql
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


pgsql-sql by date:

Previous
From: "Erik G. Burrows"
Date:
Subject: Re: SQL problem: bank account
Next
From: Samuel Tardieu
Date:
Subject: Re: Maintaining a counter up-to-date