Re: Problems requiring a GROUP BY clause on update?

I am attempting to find out how many rows a query operation affected, then
update a separate row to keep count, because running a SELECT COUNT(id) FROM
lists is far too slow when there are millions of records and the number if
required for a user interface.

So far my only success has only come from making functions for each operation,
and using GET DIAGNOSTICS num_affected := ROW_COUNT;

I don't want triggers to update one at a time because I at times insert
millions of rows, and don't think that millions of update statements is too
healthy :).

Thanks, Josh.


On September 17, 2003 11:23 am, Tom Lane wrote:
> Joshua Moore-Oliva <josh@chatgris.com> writes:
> > Attribute lists.list_size_active must be GROUPed or used in an aggregate
> > function
> >
> > CREATE OR REPLACE RULE items_log_insert AS ON INSERT
> >   TO listdb.list_items
> >     WHERE NEW.active = 1 DO (
> >     UPDATE lists SET
> >         list_size_active = list_size_active + COUNT( NEW.active )
> >       WHERE list_id = NEW.list_id;
> > );
>
> The error message isn't really helpful, perhaps, but I think the system
> is quite right to squawk.  What do you expect that command to do?  The
> COUNT() is completely meaningless because there isn't anything for it
> to iterate over.  (The SQL spec forbids aggregate functions in UPDATE
> lists altogether, and I rather think they are right, though we've not
> yet got around to installing that specific error check.)
>
> Possibly what you want is some kind of sub-select:
>
> UPDATE lists SET
>     list_size_active = list_size_active +
>         (SELECT COUNT(*) FROM ... WHERE ...)
>     WHERE list_id = NEW.list_id;
>
> but I can't help you with what to put for "..." because you've not made
> it clear what you are trying to achieve.
>
> It's also entirely likely that you'd find an ON INSERT trigger to be
> easier to work with than a rule.  People frequently try to force rules
> to behave like per-tuple actions, but they almost always lose the
> battle.  A rule is a query-level transformation, and it requires a
> different mindset to use effectively.
>
>             regards, tom lane