Re: Deferred constraint trigger semantics

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)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?