Re: Triggered Data Change check

On Sun, 11 Nov 2001, Tom Lane wrote:

> Hiroshi Inoue <Inoue@tpf.co.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; raisenotice ''%'', NEW.a; return NEW; end;'
language'plpgsql';
 
create trigger tr after update on foo1 for each row executeprocedure 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-statementto update some           data item to a value that is distinct from the value to which           that data
itemwas 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.