Re: "Triggered data change violation", once again

On Wed, 24 Oct 2001, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> >> I think all we need to do to implement things correctly is to consider a
> >> previous event only if both xmin and cmin of the old tuple match the
> >> current xact & command IDs, rather than considering it on the basis of
> >> xmin alone.
>
> > Are there any things that might update the command ID during the execution
> > of the statement from inside functions that are being run?
>
> Functions can run new commands that get new command ID numbers within
> the current transaction --- but on return from the function, the current
> command number is restored. I believe rows inserted by such a function
> would look "in the future" to us at the outer command, and would be
> ignored.
>
> Actually, now that I think about it, the MVCC rules are that tuples with
> xmin = currentxact are not visible unless they have cmin < currentcmd.
> Not equal to. This seems to render the entire "triggered data change"
> test moot --- I rather suspect that we cannot have such a condition
> as old tuple cmin = currentcmd at all, and so we could just yank all
> that code entirely.

I'm not sure if this sequence would be an example of something that
would be disallowed, but if I do something like:

Make a plpgsql function that does update table1 set key=1 where key=2;
Make that an after update trigger on table1
Put a key=1 row into table1
Update table1 to set key to 2

I end up with a 1 in the table. I'm not sure, but I think that such
a case would be possible through the fk stuff with triggers that modify
the primary key table (right now it might "work" due to the problems
of checking intermediate states). Wouldn't this be the kind of thing
the "triggered data change" is supposed to prevent? I may be just
misunderstanding the intent of the spec.