Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9da8675

Browse files
committed
Reject duplicate column names in foreign key referenced-columns lists.
Such cases are disallowed by the SQL spec, and even if we wanted to allowthem, the semantics seem ambiguous: how should the FK columns be matched upwith the columns of a unique index? (The matching could be significant inthe presence of opclasses with different notions of equality, so this issueisn't just academic.) However, our code did not previously reject suchcases, but instead would either fail to match to any unique index, orgenerate a bizarre opclass-lookup error because of sloppy thinking in theindex-matching code.David Rowley
1 parentf25e0bf commit9da8675

File tree

1 file changed

+29
-22
lines changed

1 file changed

+29
-22
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 29 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -6738,6 +6738,26 @@ transformFkeyCheckAttrs(Relation pkrel,
67386738
boolfound_deferrable= false;
67396739
List*indexoidlist;
67406740
ListCell*indexoidscan;
6741+
inti,
6742+
j;
6743+
6744+
/*
6745+
* Reject duplicate appearances of columns in the referenced-columns list.
6746+
* Such a case is forbidden by the SQL standard, and even if we thought it
6747+
* useful to allow it, there would be ambiguity about how to match the
6748+
* list to unique indexes (in particular, it'd be unclear which index
6749+
* opclass goes with which FK column).
6750+
*/
6751+
for (i=0;i<numattrs;i++)
6752+
{
6753+
for (j=i+1;j<numattrs;j++)
6754+
{
6755+
if (attnums[i]==attnums[j])
6756+
ereport(ERROR,
6757+
(errcode(ERRCODE_INVALID_FOREIGN_KEY),
6758+
errmsg("foreign key referenced-columns list must not contain duplicates")));
6759+
}
6760+
}
67416761

67426762
/*
67436763
* Get the list of index OIDs for the table from the relcache, and look up
@@ -6750,8 +6770,6 @@ transformFkeyCheckAttrs(Relation pkrel,
67506770
{
67516771
HeapTupleindexTuple;
67526772
Form_pg_indexindexStruct;
6753-
inti,
6754-
j;
67556773

67566774
indexoid=lfirst_oid(indexoidscan);
67576775
indexTuple=SearchSysCache1(INDEXRELID,ObjectIdGetDatum(indexoid));
@@ -6770,19 +6788,25 @@ transformFkeyCheckAttrs(Relation pkrel,
67706788
heap_attisnull(indexTuple,Anum_pg_index_indpred)&&
67716789
heap_attisnull(indexTuple,Anum_pg_index_indexprs))
67726790
{
6773-
/* Must get indclass the hard way */
67746791
DatumindclassDatum;
67756792
boolisnull;
67766793
oidvector*indclass;
67776794

6795+
/* Must get indclass the hard way */
67786796
indclassDatum=SysCacheGetAttr(INDEXRELID,indexTuple,
67796797
Anum_pg_index_indclass,&isnull);
67806798
Assert(!isnull);
67816799
indclass= (oidvector*)DatumGetPointer(indclassDatum);
67826800

67836801
/*
67846802
* The given attnum list may match the index columns in any order.
6785-
* Check that each list is a subset of the other.
6803+
* Check for a match, and extract the appropriate opclasses while
6804+
* we're at it.
6805+
*
6806+
* We know that attnums[] is duplicate-free per the test at the
6807+
* start of this function, and we checked above that the number of
6808+
* index columns agrees, so if we find a match for each attnums[]
6809+
* entry then we must have a one-to-one match in some order.
67866810
*/
67876811
for (i=0;i<numattrs;i++)
67886812
{
@@ -6791,31 +6815,14 @@ transformFkeyCheckAttrs(Relation pkrel,
67916815
{
67926816
if (attnums[i]==indexStruct->indkey.values[j])
67936817
{
6818+
opclasses[i]=indclass->values[j];
67946819
found= true;
67956820
break;
67966821
}
67976822
}
67986823
if (!found)
67996824
break;
68006825
}
6801-
if (found)
6802-
{
6803-
for (i=0;i<numattrs;i++)
6804-
{
6805-
found= false;
6806-
for (j=0;j<numattrs;j++)
6807-
{
6808-
if (attnums[j]==indexStruct->indkey.values[i])
6809-
{
6810-
opclasses[j]=indclass->values[i];
6811-
found= true;
6812-
break;
6813-
}
6814-
}
6815-
if (!found)
6816-
break;
6817-
}
6818-
}
68196826

68206827
/*
68216828
* Refuse to use a deferrable unique/primary key. This is per SQL

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp