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

Alvaro Herrera <alvherre@2ndquadrant.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs