Re: Deferred constraint trigger semantics

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

postgres.rocks@xxxxxxxxx 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.