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?
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.
regards, tom lane