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

Tom Lane wrote:

> ADD table_constraint [ NOT VALID ]
>
> This form adds a new constraint to a table using the same syntax as
> CREATE TABLE, plus the option NOT VALID, which is currently only
> allowed for foreign key and CHECK constraints. If the constraint is
> marked NOT VALID, the potentially-lengthy initial check to verify that
> all rows in the table satisfy the constraint is skipped. The
> constraint will still be enforced against subsequent inserts or
> updates (that is, they'll fail unless there is a matching row in the
> referenced table, in the case of foreign keys; and they'll fail unless
> the new row matches the specified check constraints). But the database
> will not assume that the constraint holds for all rows in the table,
> until it is validated by using the VALIDATE CONSTRAINT option.
>
> So the behavior in the multiple-inserts-in-one-transaction case is as
> documented. 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. In any case, I don't see a
> bug here; I think you are misunderstanding what NOT VALID is supposed
> to do.

I agree that this is working as intended and documented.

However, note that the value in the referencing column does not change.
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?

Now, this is outside the initial intent for which NOT VALID was created,
which is to separate the long verification table scan from the access
exclusive lock. But I think there is room to say that, as a new
feature, the proposed behavior ought to at least appear in the TODO
list, if someone wants to have a stab at implementing it (not me).

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services