To: pgsql-sql@postgresql.org
From: Samuel Tardieu <sam@rfc1149.net>
Subject: Maintaining a counter up-to-date
Date: Mon, 02 Jun 2003 17:47:35 +0200
Organization: Avian Carrier & Friends
Lines: 80
Message-ID: <871xyc5vmw.fsf@inf.enst.fr>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: sam@rfc1149.net
User-Agent: Gnus/5.090007 (Oort Gnus v0.07) XEmacs/21.4 (Military
	Intelligence, i386--freebsd)
Cancel-Lock: sha1:4z/BlyC6pB378+4Wp29qRBZPQPc=

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
    ON INSERT TO ips
    DO
      UPDATE categories
        SET nentries = (categories.nentries + 1)
        WHERE (categories.catid = new.catid);

  CREATE RULE cat_del AS
    ON DELETE TO ips
    DO
      UPDATE categories
        SET nentries = (categories.nentries - 1)
        WHERE (categories.catid = old.catid);

  CREATE RULE cat_upd AS
    ON UPDATE TO ips
    WHERE old.catid <> new.catid
    DO
     (UPDATE categories
        SET nentries = (categories.nentries - 1)
        WHERE (categories.catid = old.catid);
      UPDATE categories
        SET nentries = (categories.nentries + 1)
        WHERE (categories.catid = new.catid); );

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.

I have not found an easy way to maintain the counter up-to-date.
I have found a complex solution: I created a "counter" table with two
fields, "catid" and "value". The idea is to put 1 in "value" for every
insertion or new value for update, or -1 for every deletion or old
value for update.

  CREATE RULE counter_ins AS
    ON INSERT TO ips
    DO
     (INSERT INTO counter (catid, value) VALUES (new.catid, 1);
      UPDATE categories
        SET nentries = nentries +
                (SELECT sum(*) FROM counter
                 WHERE counter.catid = categories.catid)
        WHERE (categories.catid = counter.catid);
      DELETE FROM counter; );

(I do not show the equivalent "ON DELETE" and "ON UPDATE" rules)

I have two questions:

  1) Is this way of doing things correct? Do I have the guarantee that
     all the commands in the "DO" part will be executed in a
     transaction even if the initial insertion into "ips" isn't?

  2) What is the simplest way of doing this? I guess doing stats in a
     database is quite a pretty usual operation.

Thanks in advance.

  Sam

PS/ the real problem is more complex, as we need to do those
    statistics on several fields, not only "catid"
-- 
Samuel Tardieu -- sam@rfc1149.net -- http://www.rfc1149.net/sam