[SQL] Maintaining a counter up-to-date

[Samuel Tardieu]

I have an "ips" table with 10+ 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 -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Maintaining a counter up-to-date

[Samuel Tardieu]

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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Ambiguous error message

[Samuel Tardieu]

In PostgreSQL 7.4, the following select:

select texten, total
from (select protocolid, count(*) as total) from ips where catid=1
  group by protocolid order by protocolid) as c
  inner join protocols using (protocolid);

gives the error message:

ERROR:  subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo.

Why isn't the "as c" considered as an alias?

  Sam
-- 
Samuel Tardieu -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Ambiguous error message

[Samuel Tardieu]

On  2/01, Michael Glaesemann wrote:

| I'm not quite sure what you want the query to return, but you've got a 
| problem with your parentheses.

I can't believe this :) I got hit by the "inner query needs to be aliased"
message and added it to the wrong place and them munged the query a lot.

The correct one was of course:
# select texten, total from (select protocolid, count(*) as total from ips
  where catid=1 group by protocolid order by protocolid) as c
  inner join protocols using (protocolid);

(PostgreSQL is used for the will-spam-for-food black-list, see
 http://www.rfc1149.net/wsff)

   Sam


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Ambiguous error message

[Samuel Tardieu]

On  3/01, Samuel Tardieu wrote:

| On  2/01, Michael Glaesemann wrote:
| 
| | I'm not quite sure what you want the query to return, but you've got a 
| | problem with your parentheses.
| 
| I can't believe this :) I got hit by the "inner query needs to be aliased"
| message and added it to the wrong place and them munged the query a lot.
| 
| The correct one was of course:
| # select texten, total from (select protocolid, count(*) as total from ips
|   where catid=1 group by protocolid order by protocolid) as c
|   inner join protocols using (protocolid);

(with the "order by" part moved at the end of the query to avoid losing the
order during the inner join)

  Sam


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Question about isolation

[Samuel Tardieu]

If in a transaction I call an embedded function in Pl/PgSQL, in which
I have:

  delete from t where condition;
  for e in select distinct on (f) * from t where ... loop
 ...
  end loop;

Do I have the guarantee that, in any event, rows deleted from table t
by the delete won't reappear in the select result?

  Sam
-- 
Samuel Tardieu -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Question about isolation

[Samuel Tardieu]

>>>>> "Chester" == Chester Kustarz <[EMAIL PROTECTED]> writes:

> On Wed, 28 Jan 2004, Chester Kustarz wrote:
>> On Wed, 28 Jan 2004, Samuel Tardieu wrote: > If in a transaction I
>> call an embedded function in Pl/PgSQL, in which > I have:
>> >
>> > delete from t where condition; > for e in select distinct on (f)
>> * from t where ... loop > ...  > end loop;
>> >
>> > Do I have the guarantee that, in any event, rows deleted from
>> table t > by the delete won't reappear in the select result?
>> 
>> i do not think you have that guarantee in READ COMMITTED mode
>> because there is a slight possibility another backend sneaked a
>> committed insert in between the delete and select
>> statement. perhaps you want to change to SERIALIZABLE transaction
>> isolation. or perhaps you would like to repeat the WHERE condition
>> from the DELETE in the following SELECT so as to not gather any of
>> the offending rows.
>> 
>> http://www.postgresql.org/docs/7.4/static/sql-set-transaction.html

> perhaps the isolation level applies to the statement that called the
> function, in which case you would be ok. that would make more sense,
> no?

Yes. But the possible effect your describe (insertion of new rows
after the DELETE statement and before the SELECT) matches accurately
the symptoms we are observing. However, as we do have a lot of
transactions, this is not easy to reproduce.

  Sam
-- 
Samuel Tardieu -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org