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

Commit7a9c8ce

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

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
@@ -6496,6 +6496,26 @@ transformFkeyCheckAttrs(Relation pkrel,
64966496
boolfound_deferrable= false;
64976497
List*indexoidlist;
64986498
ListCell*indexoidscan;
6499+
inti,
6500+
j;
6501+
6502+
/*
6503+
* Reject duplicate appearances of columns in the referenced-columns list.
6504+
* Such a case is forbidden by the SQL standard, and even if we thought it
6505+
* useful to allow it, there would be ambiguity about how to match the
6506+
* list to unique indexes (in particular, it'd be unclear which index
6507+
* opclass goes with which FK column).
6508+
*/
6509+
for (i=0;i<numattrs;i++)
6510+
{
6511+
for (j=i+1;j<numattrs;j++)
6512+
{
6513+
if (attnums[i]==attnums[j])
6514+
ereport(ERROR,
6515+
(errcode(ERRCODE_INVALID_FOREIGN_KEY),
6516+
errmsg("foreign key referenced-columns list must not contain duplicates")));
6517+
}
6518+
}
64996519

65006520
/*
65016521
* Get the list of index OIDs for the table from the relcache, and look up
@@ -6508,8 +6528,6 @@ transformFkeyCheckAttrs(Relation pkrel,
65086528
{
65096529
HeapTupleindexTuple;
65106530
Form_pg_indexindexStruct;
6511-
inti,
6512-
j;
65136531

65146532
indexoid=lfirst_oid(indexoidscan);
65156533
indexTuple=SearchSysCache1(INDEXRELID,ObjectIdGetDatum(indexoid));
@@ -6528,19 +6546,25 @@ transformFkeyCheckAttrs(Relation pkrel,
65286546
heap_attisnull(indexTuple,Anum_pg_index_indpred)&&
65296547
heap_attisnull(indexTuple,Anum_pg_index_indexprs))
65306548
{
6531-
/* Must get indclass the hard way */
65326549
DatumindclassDatum;
65336550
boolisnull;
65346551
oidvector*indclass;
65356552

6553+
/* Must get indclass the hard way */
65366554
indclassDatum=SysCacheGetAttr(INDEXRELID,indexTuple,
65376555
Anum_pg_index_indclass,&isnull);
65386556
Assert(!isnull);
65396557
indclass= (oidvector*)DatumGetPointer(indclassDatum);
65406558

65416559
/*
65426560
* The given attnum list may match the index columns in any order.
6543-
* Check that each list is a subset of the other.
6561+
* Check for a match, and extract the appropriate opclasses while
6562+
* we're at it.
6563+
*
6564+
* We know that attnums[] is duplicate-free per the test at the
6565+
* start of this function, and we checked above that the number of
6566+
* index columns agrees, so if we find a match for each attnums[]
6567+
* entry then we must have a one-to-one match in some order.
65446568
*/
65456569
for (i=0;i<numattrs;i++)
65466570
{
@@ -6549,31 +6573,14 @@ transformFkeyCheckAttrs(Relation pkrel,
65496573
{
65506574
if (attnums[i]==indexStruct->indkey.values[j])
65516575
{
6576+
opclasses[i]=indclass->values[j];
65526577
found= true;
65536578
break;
65546579
}
65556580
}
65566581
if (!found)
65576582
break;
65586583
}
6559-
if (found)
6560-
{
6561-
for (i=0;i<numattrs;i++)
6562-
{
6563-
found= false;
6564-
for (j=0;j<numattrs;j++)
6565-
{
6566-
if (attnums[j]==indexStruct->indkey.values[i])
6567-
{
6568-
opclasses[j]=indclass->values[i];
6569-
found= true;
6570-
break;
6571-
}
6572-
}
6573-
if (!found)
6574-
break;
6575-
}
6576-
}
65776584

65786585
/*
65796586
* Refuse to use a deferrable unique/primary key. This is per SQL

0 commit comments

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