> david.g.johns...@gmail.com wrote: > >> 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).
Damn. I'm horrified that, despite my best intentions, I still managed to do a typo. How embarrassing… With the correction in place, I now get this output: INFO: trg fired. new.v = 10, n = 8 INFO: trg fired. new.v = 20, n = 8 INFO: trg fired. new.v = 30, n = 8 INFO: trg fired. new.v = 40, n = 8 INFO: trg fired. new.v = 50, n = 8 INFO: trg fired. new.v = 60, n = 8 INFO: trg fired. new.v = 70, n = 8 INFO: trg fired. new.v = 80, n = 8 This is exactly what you predicted. I'm delighted (finally) to see this outcome. >> [What I wrote here was rubbish, given that my test code was not what I >> claimed it was.] > > [David's response here is now moot.] > >> 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. Ah… I hadn't considered transition tables. However, they don't seem to be valuable for a constraint trigger. So your concern could be removed at a stroke by adding a semantic rule to the account of the CREATE TRIGGER syntax specification thus: « The SQL that creates a statement-level AFTER constraint trigger may not specify using transition tables. » Might this device give me hope? Finally, it seems that a not deferrable constraint trigger has no value—except in that using the keyword CONSTRAINT is a nice bit of self-documentation. Did I miss something? Is this trigger flavor valuable for a use-case that I haven't spotted?
