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

Commitbbe826f

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

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
@@ -5986,6 +5986,26 @@ transformFkeyCheckAttrs(Relation pkrel,
59865986
boolfound_deferrable= false;
59875987
List*indexoidlist;
59885988
ListCell*indexoidscan;
5989+
inti,
5990+
j;
5991+
5992+
/*
5993+
* Reject duplicate appearances of columns in the referenced-columns list.
5994+
* Such a case is forbidden by the SQL standard, and even if we thought it
5995+
* useful to allow it, there would be ambiguity about how to match the
5996+
* list to unique indexes (in particular, it'd be unclear which index
5997+
* opclass goes with which FK column).
5998+
*/
5999+
for (i=0;i<numattrs;i++)
6000+
{
6001+
for (j=i+1;j<numattrs;j++)
6002+
{
6003+
if (attnums[i]==attnums[j])
6004+
ereport(ERROR,
6005+
(errcode(ERRCODE_INVALID_FOREIGN_KEY),
6006+
errmsg("foreign key referenced-columns list must not contain duplicates")));
6007+
}
6008+
}
59896009

59906010
/*
59916011
* Get the list of index OIDs for the table from the relcache, and look up
@@ -5998,8 +6018,6 @@ transformFkeyCheckAttrs(Relation pkrel,
59986018
{
59996019
HeapTupleindexTuple;
60006020
Form_pg_indexindexStruct;
6001-
inti,
6002-
j;
60036021

60046022
indexoid=lfirst_oid(indexoidscan);
60056023
indexTuple=SearchSysCache1(INDEXRELID,ObjectIdGetDatum(indexoid));
@@ -6018,19 +6036,25 @@ transformFkeyCheckAttrs(Relation pkrel,
60186036
heap_attisnull(indexTuple,Anum_pg_index_indpred)&&
60196037
heap_attisnull(indexTuple,Anum_pg_index_indexprs))
60206038
{
6021-
/* Must get indclass the hard way */
60226039
DatumindclassDatum;
60236040
boolisnull;
60246041
oidvector*indclass;
60256042

6043+
/* Must get indclass the hard way */
60266044
indclassDatum=SysCacheGetAttr(INDEXRELID,indexTuple,
60276045
Anum_pg_index_indclass,&isnull);
60286046
Assert(!isnull);
60296047
indclass= (oidvector*)DatumGetPointer(indclassDatum);
60306048

60316049
/*
60326050
* The given attnum list may match the index columns in any order.
6033-
* Check that each list is a subset of the other.
6051+
* Check for a match, and extract the appropriate opclasses while
6052+
* we're at it.
6053+
*
6054+
* We know that attnums[] is duplicate-free per the test at the
6055+
* start of this function, and we checked above that the number of
6056+
* index columns agrees, so if we find a match for each attnums[]
6057+
* entry then we must have a one-to-one match in some order.
60346058
*/
60356059
for (i=0;i<numattrs;i++)
60366060
{
@@ -6039,31 +6063,14 @@ transformFkeyCheckAttrs(Relation pkrel,
60396063
{
60406064
if (attnums[i]==indexStruct->indkey.values[j])
60416065
{
6066+
opclasses[i]=indclass->values[j];
60426067
found= true;
60436068
break;
60446069
}
60456070
}
60466071
if (!found)
60476072
break;
60486073
}
6049-
if (found)
6050-
{
6051-
for (i=0;i<numattrs;i++)
6052-
{
6053-
found= false;
6054-
for (j=0;j<numattrs;j++)
6055-
{
6056-
if (attnums[j]==indexStruct->indkey.values[i])
6057-
{
6058-
opclasses[j]=indclass->values[i];
6059-
found= true;
6060-
break;
6061-
}
6062-
}
6063-
if (!found)
6064-
break;
6065-
}
6066-
}
60676074

60686075
/*
60696076
* Refuse to use a deferrable unique/primary key. This is per SQL

0 commit comments

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