Re: Defining a foreign key with a duplicate column is broken

On Sat, Aug 9, 2014 at 3:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > On Sat, Aug 9, 2014 at 12:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> So I'm thinking you're right: we should rewrite this code so that only
> >> indexes having all columns distinct can match, thereby ensuring that
> there
> >> is only one possible interpretation of the equality semantics for the
> >> foreign key.
>
> > I've attached a patch which disallows these, though I was not quite sure
> > about the new error message since likely this is something that should be
> > backpacked? I wasn't sure on the policy for new translations in a minor
> > release.
>
> There's no need for a new error message I think, because we should just
> ignore such indexes. After all, there might be a valid matching index
> later on.
>
>
hmm, but if the user attempts to define the foreign key that contains a
duplicate column in the REFERENCES part, then we'll never "find" any
indexes, so there's no point in looking at all. That's why I did the
duplicate checks as a pre-check before the loop over the indexes. Though
that's not to say that we couldn't throw the "there is no unique constraint
matching given keys for referenced table ..." error there.

I've attached a version of the patch that's a little smarter when it comes
to doing the duplicate checks in the attnums array... For what it's worth.

Regards

David Rowley