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

On Thu, Jan 10, 2002 at 10:41:50AM -0500, Tom Lane wrote:
> Or ... wait a second. How about
>
> update invoice set
> bal = ss.bal,
> cost = ss.cost,
> charge = ss.charge
> from
> (select
> item_id,
> sum(bal) as bal,
> sum(cost) as cost,
> sum(charge) as charge
> from
> line_item
> group by
> item_id) ss
> where ss.item_id = invoice.id;
>
> I haven't tried this but it seems like it should work. Better check the
> quality of the generated plan though. The temp table might be faster.

this is why we like having you around, Tom. you're sneaky in all
the right places. :)

i knew there was a solution in there somewhere...

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;

this seems like it'd properly update all _acct records with
appropriate subtotals from child _prop records. BUT-- after
doing just that (above), given this data in then _prop table,

db=# select id,acct_id,charge,cost from _prop;
id | acct_id | charge | cost
----+---------+--------+-------
3 | 4 | 0.00 | 0.00
4 | 3 | 0.00 | 0.00
5 | 2 | 210.98 | 7.25
2 | 2 | 384.95 | 40.00
(4 rows)

(then i do the update, above) i wind up with the following in
the _acct table:

db=# select id,charge,cost from _acct;
id | charge | cost
----+--------+-------
3 | 595.93 | 47.25
4 | 595.93 | 47.25
2 | 595.93 | 47.25
(3 rows)

the amounts are right for _acct.id = 2, but should be zero for
both of the others. arggh!

for completeness, i tried aliasing the main table, to no avail:

UPDATE _acct a SET -- alias for subquery?
cost = ppp.cost,
charge = ppp.charge
FROM
(SELECT
acct_id,
SUM(cost) AS cost,
SUM(charge) AS charge
FROM
_prop
WHERE -- hmm?
acct_id = a.id
GROUP BY
acct_id) ppp
WHERE
acct_id = ppp.acct_id;

ERROR: parser: parse error at or near "a"

i'm hoping to add this to a rule:

ON UPDATE TO acct DO INSTEAD (
UPDATE _acct SET
f1 = NEW.f1,
f2 = NEW.f2,
-- yada yada
fN = NEW.fN
WHERE
id = OLD.id
;
UPDATE _acct SET
cost = ppp.cost,
charge = ppp.charge
FROM
(SELECT
acct_id,
SUM(cost) AS cost,
SUM(charge) AS charge
FROM
_prop
-- WHERE
-- acct_id = OLD.acct_id
GROUP BY
acct_id
) ppp
WHERE
id = OLD.acct_id;
);

i'm not having any luck with this, though. i guess it's okay to
do the CREATE TEMP TABLE... DROP TABLE... within a rule, right?
:(

--
DEBIAN NEWBIE TIP #36 from Sean Quinlan <smq(at)gmx(dot)co(dot)uk>
:
Looking to CHANGE THE DEFAULT LS COLORS? It's simple: first,
dircolors -p >~/.dircolors
and then edit the results to suit your tastes; finally, insert
eval `dircolors -b ~/.dircolors`
in your ~/.bashrc. Next time you log in (or source ~/.bashrc)
your new colors will take effect.

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