Re: Deferred constraint trigger semantics - Mailing list pgsql-general

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