On Wed, May 11, 2022 at 3:43 PM Bryn Llewellyn <b...@yugabyte.com> wrote:
> > Oops. I did a copy-and-paste error on going from my test env. to email and > missed out the "deferral" that I'd intended. For completeness, here's the > test that I meant: > > create constraint trigger trg > after insert on t1 > for each row > execute function trg_fn(); > > create constraint trigger trg > after insert on t2 > initially deferred > for each row > execute function trg_fn(); > > It adds the "initially deferred" decoration to the "create constraint > trigger" statement. This is (still) the result: > You only added it to the uninteresting trigger on t2. It's the t1 trigger where I'd expect the behavior to change. I'm assuming your test does both (not in a position to test it myself at the moment). > Even though both inserts have completed by commit time, only the trigger > firing caused by the second statement sees the final state that obtains the > moment before commit. The first statement sees only the state after it > finishes and before the second statement executes. You said « I suspect > [that both statements will see the final state] if you actually perform > deferral ». My test shows that this is not the case. > > *Did I misunderstand you? Or does this result surprise you? If it does, do > you think that this is a bug*? > It both surprises me and fails to surprise me at the same time. I have no opinion on whether the observed behavior constitutes a bug or not. I'd lean toward not - just maybe a chance to improve the documentation. > > With respect to « having to keep around a working set of what are the > changed records » I think that the complexity that you envisaged is avoided > by the (emergent) rule that an AFTER EACH STATEMENT trigger cannot see > "old" and "new" values. In other words, all you can sensibly do in its > function is ordinary SQL that sees the current state at the moment it fires. > > To my surprise, it *is* legal to write code that accesses "old" and "new" > values. But, because many rows can be affected by a single statement, and > the trigger fires just once, the meanings of "old" and "new" are undefined. > I've seen that, in any test that I do, both are always set to NULL (which > seems reasonable). > I was thinking more about transition tables - though I admit it's not a complete thought given their opt-in nature. David J.
