On Fri, 3 Mar 2006, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > There's a bit of difference between not encouraging it and disallowing it
> > entirely, but I'm willing to buy that argument. I do think that numeric
> > reference int needs to be allowed though, and I thought that's also
> > currently not there (although int reference numeric should work I think).
>
> Um, not sure which way you mean there. The case that would work in the
> proposal as I gave it is where the referencing column's type is
> implicitly promotable to the referenced column's type. So for example
> an FK int column could reference a PK numeric column but (without more
> btre support) not an FK numeric column referencing a PK int. Is that
> what you meant?
Yes.
> If you try numeric-references-int today, you get the "costly sequential
> scan" warning, because the selected "=" operator is numeric_eq and
> that's not compatible with the PK's index. Basically, if any implicit
> promotion happens on the PK side, you're going to get the warning
> because you're no longer using an "=" operator that matches the index.
> We have not seen many complaints about getting that warning since 8.0,
> so I think that in practice people aren't using these cases and it'd be
> OK to make them a hard error instead. I would also argue that if
> implicit promotion does happen on the PK side, it's very questionable
> what semantics the FK constraint has anyway --- you can no longer be
> sure that the operator you are using has a notion of equality that's
> compatible with the PK index's notion.
I'd argue that this case makes sense in a purely theoretical sense,
numeric(8,0) references int is a sensible operation (when you don't take
into account specific PostgreSQL limitations) and it's required by spec,
but I agree that promotion on the PK side is problematic because of the
issues about equality so we may not be able to do better. I'm just worried
that we're moving further from compliance with the spec.