On Mon, Nov 1, 2021 at 9:57 PM David G. Johnston <
david.g.johnston@gmail.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.
> 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
> t
he 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.
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.