Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> 1. either the new value of "test_days.dnia" as already present in the
> NEW row, is not visible to "UPDATE test_utarg" sub-statement of the same
> transaction. But earlier versions of Postgres did allow for that
> visibility.
> 2. or the constrainets in earlier postgres were checked on trigger
> transaction COMMIT, not along the way; so the constraint violation
> didn't occure then.
Current versions of PG check foreign keys at the end of each
insert/update/delete statement, so your before-insert trigger is in fact
erroneous: the referenced key does not yet exist in the target table.
I think 7.2 did constraint checking only when the entire interactive
command finished, but there were enough cases where that was wrong
that we changed it.
Consider declaring the foreign-key constraint as DEFERRED.
regards, tom lane