Re: Foreign key type checking patch

On Mon, 1 Mar 2004, Fabien COELHO wrote:

> > > Here is a proposed patch against 7.4.1 to check exact match
> > > of foreign key types wrt the referenced keys, and to show
> > > a warning if this is not the case.
> >
> > I think that this concern may be obsolete in CVS tip,
>
> I just get the current CVS and had a quick look at it.
>
> > at least for the cases where we have indexable cross-type operators.
> > The correct way to do this would be to look at the operator found by
> > oper() and see whether it's indexable.
>
> I must admit that I do not understand your point.
>
> I wish I would have a WARNING if a foreign key is not declared exactly as
> the key it references. I think that it is a desirable feature for stupid
> users, including myself!
>
> I cannot see why whether the "=" comparison version which is chosen is
> indexable or not would lead to this information. It seems quite
> reasonnable to look directly at the attribute types and compare them for
> this purpose.
>
> I noticed the compatible_oper() function which would return a no-coersion
> binary operator between types. However that does not fit my purpose. For
> instance, it seems to me that the IsBinaryCoercible returns true for
> VARCHAR(12) and VARCHAR(16), as the type oid is the same, but I think a
> warning makes sense anyway. So it is not the same issue.
>
> So I can't see your point. Maybe some more lights would help?

Currently the cross-type indexing issues cause us problems in performance
and so warning was considered reasonable so that it would warn people
about that problem. Tom's suggestion has effectively the same effect,
warn when there might be an issue on the performance grounds.

I'm really not sure that it makes sense to warn for the fk cases where the
semantics should be correct (if they're not we need to fix it or make it
an error) but in which an error might have been made by the user because
the types are different given that it at least seems reasonable to me that
the fk type is allowable to be a subset of the referenced type. I don't
think simply different types is sufficient to be warning material.