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

will trillich <will@serensoft.com> writes:
> but i can't seem to get by cerebellum around how to flow all the
> subtotals upstream -- these don't work:

>     -- all totals wind up in ONE invoice record
>     update invoice set
>       bal = sum(line_item.bal),
>       cost = sum(line_item.cost),
>       charge = sum(line_item.charge)
>     where line_item.item_id = set.id;

UPDATEs containing top-level aggregate functions don't really work
correctly.  SQL92 forbids such things entirely, suggesting that they
think it's not well-defined.  Postgres doesn't currently reject the
query, but the behavior is rather broken IMHO.  See past discussions
in the archives about whether we should reject this, and what it should
mean if we don't.

As for your example with UPDATE ... GROUP BY, I don't believe that that
will get past the parser.

The temp table is probably the right way to go, ugly as it is.
The only standards-conformant way to do it in one query would be three
independent subselects:

update invoice set
  bal = (select sum(line_item.bal) from line_item where item_id = invoice.id),
  cost = (select sum(line_item.cost) from line_item where item_id = invoice.id),
  charge = (select sum(line_item.charge) from line_item where item_id = invoice.id);

and the amount of extra computation needed to do it that way is large.

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.

            regards, tom lane