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

Поиск
Список
Период
Сортировка
От David Rowley
Тема Re: Defining a foreign key with a duplicate column is broken
Дата
Msg-id CAApHDvp6mEeudBTCCqHxtcqLZ7M5FchUgqgvsU-qTXTPHWs_2g@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Defining a foreign key with a duplicate column is broken  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Defining a foreign key with a duplicate column is broken  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
On Sat, Aug 9, 2014 at 3:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, Aug 9, 2014 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.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


Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: "Ramirez, Danilo"
Дата:
Сообщение: Re: PostgreSQL vs oracle doing 1 million sqrts am I doing it wrong?
Следующее
От: David G Johnston
Дата:
Сообщение: Re: jsonb format is pessimal for toast compression