Re: Deferred constraint trigger semantics

david.g.johnston@gmail.com wrote:

bryn@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).

[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.