Обсуждение: trigger fired on changes in specific column

I would like to fire a trigger when the value in a single column have
changed. I read somewhere that this is in the SQL-standard but not in
postgresql, I don't have the SQL-spec so I can't check, and I don't know
what version of the standard.

I can't find it in the todo list though, either it's missing and should be
added or it's not there by intention.

I have a table where the rows are partitioned into small groups.  When a
value in column A changes, then every value in the same group would have
their column B value updated. This would in turn trigger more things, If
the trigger is fired only when column A changes the problem is solved.

After this I need to do almost the same procedure where a value of column
B is changed to update the column C for all rules in the group. This
ordering of triggers would work automatically if they fire just when the
correct column are updated.

Well, I can work around these things. One way is to place B and C in their
own tables so updating these don't trigger anything in the original table.
I also think I can keep them in the relation by being carefull and not
update unless the value actually changed, this costs a litle extra CPU
time.

If i'm lucky it's even fixed in 7.3 and then I can live without it for a
while.

--
/Dennis

On Friday 23 Aug 2002 7:52 am, Dennis Bjorklund wrote:

> I have a table where the rows are partitioned into small groups.  When a
> value in column A changes, then every value in the same group would have
> their column B value updated. This would in turn trigger more things, If
> the trigger is fired only when column A changes the problem is solved.
>
> After this I need to do almost the same procedure where a value of column
> B is changed to update the column C for all rules in the group. This
> ordering of triggers would work automatically if they fire just when the
> correct column are updated.
>
> Well, I can work around these things. One way is to place B and C in their
> own tables so updating these don't trigger anything in the original table.
> I also think I can keep them in the relation by being carefull and not
> update unless the value actually changed, this costs a litle extra CPU
> time.

A single IF OLD.x=NEW.x for each column won't slow you down noticably. Indeed,
if changes cascade A=>B=>C then you might well be able to optimise for case
A.

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.

Is there a good reason why you don't decompose B,C etc to their own tables and
join where necessary. You can probably keep things looking the same (or at
least very similar) using an appropriate view.

Of course, you might have damn good reasons why none of the above helps in
this case.

HTH

- Richard Huxton

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.

Before I added these calculated values the database was in BCNF. Now it is
not, but it will not really change anything if I keep these values in
there own table or not.

> Is there a good reason why you don't decompose B,C etc to their own tables and
> join where necessary. You can probably keep things looking the same (or at
> least very similar) using an appropriate view.

I can do this (and probably will), and what it helps me is just so the
triggers works (esier). It does not really change the problem of storing
redundant data in the database. But the world is not perfect, I have to
precalculate and store these values or it is to slow.

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.

--
/Dennis

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

Dennis Bjorklund <db@zigo.dhs.org> writes:
> I would like to fire a trigger when the value in a single column
> have changed. I read somewhere that this is in the SQL-standard but
> not in postgresql, I don't have the SQL-spec so I can't check, and I
> don't know what version of the standard.

Yes, it's in the standard (SQL99).

> I can't find it in the todo list though, either it's missing and
> should be added or it's not there by intention.

It should probably be there -- I can't see a good reason not to
implement it, at any rate.

> If i'm lucky it's even fixed in 7.3 and then I can live without it
> for a while.

It's not in CVS HEAD. Given that no one is currently working on it
AFAIK, it almost certainly won't be in 7.3

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC