Re: Triggered Data Change check

On Sun, 11 Nov 2001, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> >     I wanted to know if there was a decision
> > to remove the triggered data change violation checks
> > from trigger.c or to change them to a per statement
> > check?
>
> AFAIK no one is happy with the state of that code, but I'm not sure
> if we've agreed on exactly how to change it.  Do you have a proposal?

Well, I wonder if the check is so weak as to be fairly useless in the
first place really, even if applied to the statement as opposed to the
transaction.  It prevents cases like (not tested, but gives the idea):

create table foo1( a int unique);
create table foo2( a int unique default 2 references foo1(a)initially deferred on update set default);
alter table foo1 add foreign key(a) references foo2(a)initially deferred on update cascade);
begin;
insert into foo1 values (1);
insert into foo2 values (1);
end;
update foo1 set a=3;
-- I think it would have the following effect:
-- foo1 has "a" set to 3 which sets foo2's "a" to 2 which sets
-- foo1's "a" to 2 as well. And so the row in foo1 is changed
-- twice.

But, since you could do alot of this same work in your own triggers,
and doing so doesn't seem to trip the triggered data change check
(for example an after trigger on a non-fk table that updates the
same table), I wonder if we should either defend against neither
case or both.

As such, I'd say we should at least comment out the check and
error since it would fix alot of cases that people using the
system more normally run into at the expense of a more edge
condition.

One problem is that it opens up the foreign key stuff to a
bunch of cases that haven't been tested before and it may be
a little bit late for opening up that can of worms. I'm confident
that I've fixed related badness in the no action case and on
the base check(since my home copy had the check commented out and the
tests I ran worked in that case), but I haven't touched the referential
actions because they're a little more complicated.