Re: caching subtotals: update vs sum -- aaugh!

On Fri, Jan 11, 2002 at 03:11:16PM -0600, i whined about:
> > the amounts are right for _acct.id = 2, but should be zero for
> > both of the others. arggh!

then Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> replied
> [ scratches head ] This may represent a bug. I'm too lazy today to
> whip up a test case based on your emails --- could I trouble you for
> a script that creates and loads the test tables?

here i had my hopes up. alas, it was only me and my bungles.

i think i found it-- this finally works (sure would like to have
the subquery use the WHERE, though...)

create rule acct_edit as
on update to acct
do instead (
update _acct set
code = NEW.code,
charge = p.charge,
cost = p.cost
from (
select
sum(charge) as charge,
sum(cost ) as cost,
acct_id
from
_prop
-- where
-- acct_id = OLD.id -- can't see *OLD* record here
group by
acct_id
) p
where
id = OLD.id
and
p.acct_id = OLD.id;
);

i also tried creating a temp table within the rule, but didn't
get very far. (and BOY is it crucial to have an index on
_prop(acct_id)!)

--

as for where the problem sat:

UPDATE _acct SET
cost = ppp.cost,
charge = ppp.charge
FROM
(SELECT
acct_id,
SUM(cost) AS cost,
SUM(charge) AS charge
FROM
_prop
GROUP BY
acct_id) ppp
WHERE
acct_id = ppp.acct_id;

the WHERE was wrong -- shoulda been

WHERE
id -- _acct.id, in top query
=
ppp.acct_id -- subquery from _prop.acct_id
;

--
DEBIAN NEWBIE TIP #60 from Vineet Kumar <debian-user(at)virtual(dot)doorstop(dot)net>
:
Been hoping to find A FEATURE-PACKED MUTT CONFIG FILE? Check
out the ones at Sven Guckes' site:
http://www.fefe.de/muttfaq/muttrc
There's also some great vimrc ideas there, too.

Also see http://newbieDoc.sourceForge.net/ ...