Defining a foreign key with a duplicate column is broken

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Defining a foreign key with a duplicate column is broken
Date: 2014-08-08 15:43:00
Message-ID: CAApHDvq6uDZAgsd+MwuYTTz6muWootLmE8OHEUDb8OhdfVXe7w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In master I've been testing some new code that I'm working on around
foreign keys.
I wasn't quite sure if it was possible to include the same column twice in
a foreign key, so I tested....

create table r1 (a int);
create table r2 (b int);
create unique index on r2(b,b);
alter table r1 add constraint r2_b_fkey foreign key (a,a) references r2
(b,b);
ERROR: cache lookup failed for opclass 0

Which is not quite any of the messages that I would have expected

I've tracked this down to a small bug in transformFkeyCheckAttrs()
where opclasses[1] won't get set because if (attnums[0] ==
indexStruct->indkey.values[1]) will match then break out of the inner loop.
Instead opclasses[0] gets set twice.

The attached seems to fix the problem, but the whole thing makes me wonder
if this is even meant to be allowed? I was thinking that this might be a
good time to disallow this altogether, since it's already broken and looks
like it has been for about 11 years

Disallowing it would simplify some code in my semi/anti join removal patch
that I posted here
http://www.postgresql.org/message-id/CAApHDvpCBEfuc5tD=vniepAv0pU5m=q=fOQZcOdMHeei7OQPgQ@mail.gmail.com

Any thoughts?

Regards

David Rowley

Attachment Content-Type Size
transformFkeyCheckAttrs_fix.patch application/octet-stream 632 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2014-08-08 15:54:24 Re: jsonb format is pessimal for toast compression
Previous Message Andrew Dunstan 2014-08-08 15:35:59 Re: jsonb format is pessimal for toast compression