Re: Deferred constraint trigger semantics

> postgres(dot)rocks(at)gmail(dot)com wrote:
>
> It adds the "initially deferred" decoration to the "create constraint trigger" statement. This is (still) the result:
>
> INFO: trg fired. new.v = 10, n = 5
> INFO: trg fired. new.v = 20, n = 5
> INFO: trg fired. new.v = 30, n = 5
> INFO: trg fired. new.v = 40, n = 5
> INFO: trg fired. new.v = 50, n = 5
> INFO: trg fired. new.v = 60, n = 8
> INFO: trg fired. new.v = 70, n = 8
> INFO: trg fired. new.v = 80, n = 8
> Because You can do
> create constraint trigger trg
> after insert on t2
> deferrable initially deferred
> for each row
> execute function trg_fn();
>
> You didn't explicitly defer the trigger trg on t1!. That means after you insert on t1 then the trigger trg on t1 invoked rather than on commit time.
> If you
> create constraint trigger trg
> after insert on t1
> deferrable initially deferred
> for each row
> execute function trg_fn();
>
> create constraint trigger trg
> after insert on t2
> deferrable initially deferred
> for each row
> execute function trg_fn();
> then you will get
> INFO: 00000: trg fired. new.v = 10, n = 8
> INFO: 00000: trg fired. new.v = 20, n = 8
> INFO: 00000: trg fired. new.v = 30, n = 8
> INFO: 00000: trg fired. new.v = 40, n = 8
> INFO: 00000: trg fired. new.v = 50, n = 8
> INFO: 00000: trg fired. new.v = 60, n = 8
> INFO: 00000: trg fired. new.v = 70, n = 8
> INFO: 00000: trg fired. new.v = 80, n = 8

Er… yes. David Johnston pointed that out too. I'm embarrassed beyond belief. Sorry to have wasted folks' time because of my mistake.