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

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:

Even though both inserts have completed by commit time, only the trigger firing caused by the second statement sees the final state that obtains the moment before commit. The first statement sees only the state after it finishes and before the second statement executes. You said « I suspect [that both statements will see the final state] if you actually perform deferral ». My test shows that this is not the case.

Did I misunderstand you? Or does this result surprise you? If it does, do you think that this is a bug?

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