Re: Triggered Data Change check

On Sun, 11 Nov 2001, Tom Lane wrote:

> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> > Strictly speaking MVCC is only for read-only queries.
> > Even under MVCC, update, delete and select .. for update have
> > to see the newest tuples.
>
> True. But my point is that we already have mechanisms to deal with
> that set of issues; the trigger code shouldn't concern itself with
> the problem.

This sequence on my system prints the numbers increasing by 1 which
I would assume means that the updates are going through:

create table foo1(a int);
create function f() returns opaque as 'begin update foo1 set a=a+1; raise
notice ''%'', NEW.a; return NEW; end;' language 'plpgsql';
create trigger tr after update on foo1 for each row execute
procedure f();
insert into foo1 values(1);
update foo1 set a=1;

I think that if this were an fk trigger, this would technically be illegal
behavior as soon as that row in foo1 was modified again during the
function execution from the "update foo1 set a=1" statement due to the
following (sql92, 11.8 General Rules -- I don't have the copy of sql99
on this machine to look at, but I'm guessing there's something similar)
7) If any attempt is made within an SQL-statement to update some
data item to a value that is distinct from the value to which
that data item was previously updated within the same SQL-
statement, then an exception condition is raised: triggered
data change violation.
Given this is under the referential constraint definition, I'm guessing
it's about ri constraints even though the wording seems to say any
attempt.

Because its easy to get around with general triggers, I'm not sure the
check is meaningful, and it's alot less likely to occur than the normal
update/delete or update/update cases that currently error out in the
system, so I'm also for ripping out the check, although I think we
probably want to think about this for later.