Re: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types

On Mon, Nov 1, 2021 at 9:57 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
> The fundamental issue here seems to be that normal FK usage results in
> referencing-to-referenced lookups which are one-to-one. But the on
update usage
> results in a referenced-to-referencing lookup which ends up being
many-to-one
> (values, not rows). If that lookup ends up being one-to-one, even if the
data
> types are different, then that difference in data types won't matter. In
this
> example it is many-to-one.

I believe the problem can present whenever the assignment cast from the
referenced type
to the referencing type is lossy. As examples, NUMERIC(10,2) to INT and the
more
esoteric TEXT to "char" both reproduce the issue.

On Mon, Nov 1, 2021 at 9:57 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Mon, Nov 1, 2021 at 5:34 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > The problem with this numeric case is that
> > we round off the value while storing it into the referencing column
> > --- but, for the specific values given, that results in no change in
> > the referencing value so ri_KeysEqual() decides that there's no need
> > to re-check the constraint. That's not an optimization I care to
> > give up.
>
> AFAICT, the only place where our documentation touches on this is
>> 5.4.5. Foreign Keys, which breezily says "Of course, the number and
>> type of the constrained columns need to match the number and type of
>> the referenced columns." So maybe we need to improve that, but I'm
>> not sure what to say instead. In view of these considerations,
>> we surely shouldn't encourage using different types.
>>
>>
> The following comes mostly from reading this thread. This is mostly just
> a conversation starter and me trying to make sure I understand the problem
> accurately, regardless of it's acceptability as documentation.
>
> """
> Warning, be careful, or simply avoid, using on update cascade when the
> data types involved in the foreign key are not identical.
>
> Why?
>
> When using update cascade there is an optimization in place that compares
> the old and new values for equality, after casting them to the referencing
> table's data type, and skips performing the update if the value did not
> change. This is problematic when multiple values in the referenced table's
> data type map to a single value in the referencing table's data type.
> e.g., both 1.00 and 1.45 numeric(10,2) are equal to 1 numeric(10,0). Thus
> the optimization check will conclude that changing the referenced value
> from 1.00 to 1.45 is a no-op with respect to the referencing row's value of
> 1, leaving the system in an inconsistent state.
> """
>
> The fundamental issue here seems to be that normal FK usage results in
> referencing-to-referenced lookups which are one-to-one. But the on update
> usage results in a referenced-to-referencing lookup which ends up being
> many-to-one (values, not rows). If that lookup ends up being one-to-one,
> even if the data types are different, then that difference in data types
> won't matter. In this example it is many-to-one.
>
> I feel like the optimization being done, and the comparison between old
> and new being performed, is amenable to change to detect this case and fail
> the update command. Though I need to get more into the weeds to actually
> defend that feeling and even offer an approach. But hopefully this helps
> in the meantime.
>
> Note: I haven't tried to reason out if on delete cascade has an issue here.
>
> David J.
>
>