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 |
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 |