pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMaintaining a counter up-to-date
3+ messages / 2 participants
[nested] [flat]
* Maintaining a counter up-to-date
@ 2003-06-02 15:47 Samuel Tardieu <sam@rfc1149.net>
2003-06-02 18:27 ` Re: Maintaining a counter up-to-date Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Samuel Tardieu @ 2003-06-02 15:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Maintaining a counter up-to-date
2003-06-02 15:47 Maintaining a counter up-to-date Samuel Tardieu <sam@rfc1149.net>
@ 2003-06-02 18:27 ` Richard Huxton <dev@archonet.com>
2003-06-02 18:29 ` Re: Maintaining a counter up-to-date Samuel Tardieu <sam@rfc1149.net>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2003-06-02 18:27 UTC (permalink / raw)
To: Samuel Tardieu <sam@rfc1149.net>; 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Maintaining a counter up-to-date
2003-06-02 15:47 Maintaining a counter up-to-date Samuel Tardieu <sam@rfc1149.net>
2003-06-02 18:27 ` Re: Maintaining a counter up-to-date Richard Huxton <dev@archonet.com>
@ 2003-06-02 18:29 ` Samuel Tardieu <sam@rfc1149.net>
0 siblings, 0 replies; 3+ messages in thread
From: Samuel Tardieu @ 2003-06-02 18:29 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On 2/06, Richard Huxton wrote:
| 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.
Ouch. At the current time, I reverted to count(*) style which seems to be
performed in much less time in 7.3.2 than in 7.2.x.
I'll look at the triggers, thanks.
Sam
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-06-02 18:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-02 15:47 Maintaining a counter up-to-date Samuel Tardieu <sam@rfc1149.net>
2003-06-02 18:27 ` Richard Huxton <dev@archonet.com>
2003-06-02 18:29 ` Samuel Tardieu <sam@rfc1149.net>
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