Thread: Problem in AlterTableAddConstraint?
Browsing through backend/commands/command.c I noticed the following code:
if (indexStruct->indisunique) { List *attrl;
/* go through the fkconstraint->pk_attrs list */ foreach(attrl, fkconstraint->pk_attrs) {
Ident*attr=lfirst(attrl); found = false; for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i]
!=0;
i++) { int pkattno = indexStruct->indkey[i]; if (pkattno>0) {
char *name = NameStr(rel_attrs[pkattno-1]->attname); if (strcmp(name, attr->name)==0)
{ found = true; break; } }
} if (!found) break; } }
which is (I think) supposed to be checking for a unique index on the FK
fields in the referenced table.
Unfortunately, my reading of this code suggests it is doing the following:
for each column in the FK
see if we can find the column in the index if not, then die
next FK column
The problem with this is that it needs to ensure a 1:1 match between
columns for the UNIQUE constraint requirement to be satisfied...I think.
To give an example,
create table c2(f1 integer, f2 integer, unique(f1,f2)); create table c1(f1 integer, f2 integer, foreign key(f1)
references
c2(f1));
is allowed with current sources.
I'd guess that adding code to ensure the column lists are the same size
would fix the problem.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Assuming the silence is agreement, does this look like the right solution
(I assume looping through the index is the only way to count the segments):
if (indexStruct->indisunique) { List *attrl;
/* go through the fkconstraint->pk_attrs list */ foreach(attrl, fkconstraint->pk_attrs) {
Ident*attr=lfirst(attrl); found = false; for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i]
!=0;
i++) { int pkattno = indexStruct->indkey[i]; if (pkattno>0) {
char *name = NameStr(rel_attrs[pkattno-1]->attname); if (strcmp(name, attr->name)==0)
{ found = true; break; } }
}
if (!found)
+ { break;
+ } else {
+
+ /* Require same number of segments */
+ if (i != length(fkconstraint->pk_attrs))
+ {
+ found = false;
+ break;
+ }
+ } } }
At 02:49 29/11/00 +1100, Philip Warner wrote:
>
> create table c2(f1 integer, f2 integer, unique(f1,f2));
> create table c1(f1 integer, f2 integer,
> foreign key(f1) references c2(f1));
>
> is allowed with current sources.
>
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Can anyone comment on this?
>
> Browsing through backend/commands/command.c I noticed the following code:
>
> if (indexStruct->indisunique)
> {
> List *attrl;
>
> /* go through the fkconstraint->pk_attrs list */
> foreach(attrl, fkconstraint->pk_attrs)
> {
> Ident *attr=lfirst(attrl);
> found = false;
> for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0;
> i++)
> {
> int pkattno = indexStruct->indkey[i];
> if (pkattno>0)
> {
> char *name = NameStr(rel_attrs[pkattno-1]->attname);
> if (strcmp(name, attr->name)==0)
> {
> found = true;
> break;
> }
> }
> }
> if (!found)
> break;
> }
> }
>
> which is (I think) supposed to be checking for a unique index on the FK
> fields in the referenced table.
>
> Unfortunately, my reading of this code suggests it is doing the following:
>
> for each column in the FK
>
> see if we can find the column in the index
> if not, then die
>
> next FK column
>
> The problem with this is that it needs to ensure a 1:1 match between
> columns for the UNIQUE constraint requirement to be satisfied...I think.
>
> To give an example,
>
> create table c2(f1 integer, f2 integer, unique(f1,f2));
> create table c1(f1 integer, f2 integer, foreign key(f1) references
> c2(f1));
>
> is allowed with current sources.
>
> I'd guess that adding code to ensure the column lists are the same size
> would fix the problem.
>
>
> ----------------------------------------------------------------
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.B.N. 75 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 0500 83 82 82 | ___________ |
> Http://www.rhyme.com.au | / \|
> | --________--
> PGP key available upon request, | /
> and from pgp5.ai.mit.edu:11371 |/
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can anyone comment on this?
The code seems to have been changed as per Philip's suggestion.
regards, tom lane
Should be fixed now. I had sent a patch in a while ago about it
and the code does seem to do an extra step now which gets the length
of the index and compares it to the length of the attribute list.
On Mon, 22 Jan 2001, Bruce Momjian wrote:
>
> Can anyone comment on this?
>
> >
> > Browsing through backend/commands/command.c I noticed the following code:
> >
> > if (indexStruct->indisunique)
> > {
> > List *attrl;
> >
> > /* go through the fkconstraint->pk_attrs list */
> > foreach(attrl, fkconstraint->pk_attrs)
> > {
> > Ident *attr=lfirst(attrl);
> > found = false;
> > for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0;
> > i++)
> > {
> > int pkattno = indexStruct->indkey[i];
> > if (pkattno>0)
> > {
> > char *name = NameStr(rel_attrs[pkattno-1]->attname);
> > if (strcmp(name, attr->name)==0)
> > {
> > found = true;
> > break;
> > }
> > }
> > }
> > if (!found)
> > break;
> > }
> > }
> >
> > which is (I think) supposed to be checking for a unique index on the FK
> > fields in the referenced table.
> >
> > Unfortunately, my reading of this code suggests it is doing the following:
> >
> > for each column in the FK
> >
> > see if we can find the column in the index
> > if not, then die
> >
> > next FK column
> >
> > The problem with this is that it needs to ensure a 1:1 match between
> > columns for the UNIQUE constraint requirement to be satisfied...I think.
> >
> > To give an example,
> >
> > create table c2(f1 integer, f2 integer, unique(f1,f2));
> > create table c1(f1 integer, f2 integer, foreign key(f1) references
> > c2(f1));
> >
> > is allowed with current sources.
> >
> > I'd guess that adding code to ensure the column lists are the same size
> > would fix the problem.
> >
> >
> > ----------------------------------------------------------------
> > Philip Warner | __---_____
> > Albatross Consulting Pty. Ltd. |----/ - \
> > (A.B.N. 75 008 659 498) | /(@) ______---_
> > Tel: (+61) 0500 83 82 81 | _________ \
> > Fax: (+61) 0500 83 82 82 | ___________ |
> > Http://www.rhyme.com.au | / \|
> > | --________--
> > PGP key available upon request, | /
> > and from pgp5.ai.mit.edu:11371 |/
> >
>
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>