Re: trigger fired on changes in specific column - Mailing list pgsql-general

From Richard Huxton
Subject Re: trigger fired on changes in specific column
Date
Msg-id 200208231442.43728.dev@archonet.com
Whole thread Raw
In response to Re: trigger fired on changes in specific column  (Dennis Bjorklund <db@zigo.dhs.org>)
List pgsql-general
On Friday 23 Aug 2002 12:03 pm, Dennis Bjorklund wrote:
> On Fri, 23 Aug 2002, Richard Huxton wrote:
> > However, I think you need to look at your data definitions - this sound
> > horribly unnormalized. I'm not a normalisation fascist, but this just
> > feels *very* wrong.
>
> Well, the values are redundant and calculated from other values in the
> table. It used to be in a view calculated from the rest. But the
> calculation takes a lot of time so I have to store it somewhere.
>
> The problem is that the calculation formula gives that when I change one
> value in a group all the calculated values for the group have to be
> recalculated.

If the whole group shares the same result value, having separate lookup tables
is probably a win.

> I think your recomendation is both valid and good. If I could find a
> better way to handle this I would. Maybe something like a view that caches
> values or something, but I have not found that in postgresql.

You could make a temporary table (yuck) but you might want to look at the
is_cacheable flag you can add to function definitions. That tells the planner
that for any given input value(s) the output is fixed. Primarily useful on
the WHERE side of things, but might be worth looking at.

- Richard Huxton

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Two servers on one machine
Next
From: Marcus Claesson
Date:
Subject: How to retrieve a comment/description from a table