Re: BUG #14560: FK not valid are ignored during transactions

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> Tom Lane wrote:
>> I think that the fact that you don't see an error when you
>> do only one row update per transaction is because we optimize away the
>> FK check entirely in that case (if the FK columns aren't changed,
>> obviously). I don't recall exactly why that optimization can't be used
>> for repeat updates in a single transaction.

> I would expect a failure if the value changes from a matching value to a
> mismatching value; or from a mismatching value to another mismatching
> value. But if the value does not change, why are we checking it at all?

Well, if you're going to make me go look it up ...

RI_FKey_fk_upd_check_required explains:

/*
* If the original row was inserted by our own transaction, we
* must fire the trigger whether or not the keys are equal. This
* is because our UPDATE will invalidate the INSERT so that the
* INSERT RI trigger will not do anything; so we had better do the
* UPDATE check. (We could skip this if we knew the INSERT
* trigger already fired, but there is no easy way to know that.)
*/

So the point is basically that we don't know if the previous row version
was a fresh INSERT or an UPDATE, and in the former case there might not
have been any previous check that the existing FK values satisfy the
constraint.

I don't think we can weaken this logic for a NOT VALID constraint, because
the contract is still that transactions aren't allowed to create any new
violations of the constraint.

regards, tom lane