Re: Calculated fileds in pg

On Mon, 25 Nov 2002, Jean-Christian Imbeault wrote:

> Stephan Szabo wrote:
> >
> >>
> >>Table A:
> >>
> >>id   integer primary key,
> >>B_id integer references B(id),
> >>cost integer default 0
> >>
> >>Table B:
> >>
> >>id    integer primary key,
> >>total integer -- select sum(cost) from A where B_id=id;
> >
> > I'd probably do B as a view if it was this simple, because writing the
> > triggers for A sound to be problematic from a getting the concurrency
> > right without potential deadlocks standpoint.
>
> The tables are not *that* simple. Both have about 15 columns, *but*
> there is just the one calculated column *and* it is as in the example,
> just the sum of matching rows in the other table.
>
> Here are the actual table schemas, would you still recommend a view? (I

One question is how often do you actually look for INVOICES.total_price in
comparison to modifications to INVOICES.id and INVOICE_LI?  If you tend
not to do lots of modifications but do alot of selects, then you probably
would want to consider a trigger solution.  Otherwise, a base table and an
INVOICES view that adds the field seems reasonable.  If you do lots of
selects on INVOICES but often don't need total_price then you can choose
whether to use the view or the base table depending on whether you care
about total_price or not.

> know nothing about views so this would be my first attempt at them). And
> you are right about the triggers being a problem for concurrency. I have
> implemented the above as a trigger on every insert/update to Table B and
> it is slow *and* I want to add other triggers but the concurrency aspect
>   is a little problematic.

It's fundamentally the same problem as foreign keys (except slightly
worse) in that updates to the cost of an line item row, an update of
which invoice it's associated with, an update of the id of an invoice, the
insertion or deletion of a line item all potentially make updates to an
invoice row and grab locks that might cause deadlock (although the
likelihood of a deadlocking pattern may be low) or at the very least you
might end up with serialization of concurrent transactions.