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

Commitb88fa3b

Browse files
committed
Generate a WARNING when the column types in a foreign key constraint are
incompatible enough to prevent indexscanning the referenced table. Also,improve the error message that pops out when we can't implement the FK atall for lack of a usable equality operator. Fabien Coelho, with some reviewby Tom Lane.
1 parent642cd0a commitb88fa3b

File tree

4 files changed

+271
-76
lines changed

4 files changed

+271
-76
lines changed

‎src/backend/commands/tablecmds.c‎

Lines changed: 72 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.99 2004/02/15 21:01:39 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.100 2004/03/13 22:09:13 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -89,10 +89,12 @@ static void AlterTableAddForeignKeyConstraint(Relation rel,
8989
staticinttransformColumnNameList(OidrelId,List*colList,
9090
int16*attnums,Oid*atttypids);
9191
staticinttransformFkeyGetPrimaryKey(Relationpkrel,Oid*indexOid,
92-
List**attnamelist,
93-
int16*attnums,Oid*atttypids);
92+
List**attnamelist,
93+
int16*attnums,Oid*atttypids,
94+
Oid*opclasses);
9495
staticOidtransformFkeyCheckAttrs(Relationpkrel,
95-
intnumattrs,int16*attnums);
96+
intnumattrs,int16*attnums,
97+
Oid*opclasses);
9698
staticvoidvalidateForeignKeyConstraint(FkConstraint*fkconstraint,
9799
Relationrel,Relationpkrel);
98100
staticvoidcreateForeignKeyTriggers(Relationrel,FkConstraint*fkconstraint,
@@ -3016,6 +3018,7 @@ AlterTableAddForeignKeyConstraint(Relation rel, FkConstraint *fkconstraint)
30163018
int16fkattnum[INDEX_MAX_KEYS];
30173019
Oidpktypoid[INDEX_MAX_KEYS];
30183020
Oidfktypoid[INDEX_MAX_KEYS];
3021+
Oidopclasses[INDEX_MAX_KEYS];
30193022
inti;
30203023
intnumfks,
30213024
numpks;
@@ -3088,11 +3091,11 @@ AlterTableAddForeignKeyConstraint(Relation rel, FkConstraint *fkconstraint)
30883091
* Look up the referencing attributes to make sure they exist, and
30893092
* record their attnums and type OIDs.
30903093
*/
3091-
for (i=0;i<INDEX_MAX_KEYS;i++)
3092-
{
3093-
pkattnum[i]=fkattnum[i]=0;
3094-
pktypoid[i]=fktypoid[i]=InvalidOid;
3095-
}
3094+
MemSet(pkattnum,0,sizeof(pkattnum));
3095+
MemSet(fkattnum,0,sizeof(fkattnum));
3096+
MemSet(pktypoid,0,sizeof(pktypoid));
3097+
MemSet(fktypoid,0,sizeof(fktypoid));
3098+
MemSet(opclasses,0,sizeof(opclasses));
30963099

30973100
numfks=transformColumnNameList(RelationGetRelid(rel),
30983101
fkconstraint->fk_attrs,
@@ -3102,21 +3105,24 @@ AlterTableAddForeignKeyConstraint(Relation rel, FkConstraint *fkconstraint)
31023105
* If the attribute list for the referenced table was omitted, lookup
31033106
* the definition of the primary key and use it. Otherwise, validate
31043107
* the supplied attribute list. In either case, discover the index
3105-
* OID and the attnums and type OIDs of the attributes.
3108+
* OID and index opclasses, and the attnums and type OIDs of the
3109+
* attributes.
31063110
*/
31073111
if (fkconstraint->pk_attrs==NIL)
31083112
{
31093113
numpks=transformFkeyGetPrimaryKey(pkrel,&indexOid,
31103114
&fkconstraint->pk_attrs,
3111-
pkattnum,pktypoid);
3115+
pkattnum,pktypoid,
3116+
opclasses);
31123117
}
31133118
else
31143119
{
31153120
numpks=transformColumnNameList(RelationGetRelid(pkrel),
31163121
fkconstraint->pk_attrs,
31173122
pkattnum,pktypoid);
31183123
/* Look for an index matching the column list */
3119-
indexOid=transformFkeyCheckAttrs(pkrel,numpks,pkattnum);
3124+
indexOid=transformFkeyCheckAttrs(pkrel,numpks,pkattnum,
3125+
opclasses);
31203126
}
31213127

31223128
/* Be sure referencing and referenced column types are comparable */
@@ -3128,14 +3134,48 @@ AlterTableAddForeignKeyConstraint(Relation rel, FkConstraint *fkconstraint)
31283134
for (i=0;i<numpks;i++)
31293135
{
31303136
/*
3131-
*fktypoid[i] is theforeign key table's i'thelement's type
3132-
*pktypoid[i] is theprimary key table's i'thelement's type
3137+
*pktypoid[i] is theprimary key table's i'thkey's type
3138+
*fktypoid[i] is theforeign key table's i'thkey's type
31333139
*
3134-
* We let oper() do our work for us, including ereport(ERROR) if the
3135-
* types don't compare with =
3140+
* Note that we look for an operator with the PK type on the left;
3141+
* when the types are different this is critical because the PK index
3142+
* will need operators with the indexkey on the left. (Ordinarily
3143+
* both commutator operators will exist if either does, but we won't
3144+
* get the right answer from the test below on opclass membership
3145+
* unless we select the proper operator.)
31363146
*/
31373147
Operatoro=oper(makeList1(makeString("=")),
3138-
fktypoid[i],pktypoid[i], false);
3148+
pktypoid[i],fktypoid[i], true);
3149+
3150+
if (o==NULL)
3151+
ereport(ERROR,
3152+
(errcode(ERRCODE_UNDEFINED_FUNCTION),
3153+
errmsg("foreign key constraint \"%s\" "
3154+
"cannot be implemented",
3155+
fkconstraint->constr_name),
3156+
errdetail("Key columns \"%s\" and \"%s\" "
3157+
"are of incompatible types: %s and %s.",
3158+
strVal(nth(i,fkconstraint->fk_attrs)),
3159+
strVal(nth(i,fkconstraint->pk_attrs)),
3160+
format_type_be(fktypoid[i]),
3161+
format_type_be(pktypoid[i]))));
3162+
3163+
/*
3164+
* Check that the found operator is compatible with the PK index,
3165+
* and generate a warning if not, since otherwise costly seqscans
3166+
* will be incurred to check FK validity.
3167+
*/
3168+
if (!op_in_opclass(oprid(o),opclasses[i]))
3169+
ereport(WARNING,
3170+
(errmsg("foreign key constraint \"%s\" "
3171+
"will require costly sequential scans",
3172+
fkconstraint->constr_name),
3173+
errdetail("Key columns \"%s\" and \"%s\" "
3174+
"are of different types: %s and %s.",
3175+
strVal(nth(i,fkconstraint->fk_attrs)),
3176+
strVal(nth(i,fkconstraint->pk_attrs)),
3177+
format_type_be(fktypoid[i]),
3178+
format_type_be(pktypoid[i]))));
31393179

31403180
ReleaseSysCache(o);
31413181
}
@@ -3225,13 +3265,19 @@ transformColumnNameList(Oid relId, List *colList,
32253265
* transformFkeyGetPrimaryKey -
32263266
*
32273267
*Look up the names, attnums, and types of the primary key attributes
3228-
*for the pkrel.Used when the column list in the REFERENCES specification
3229-
*is omitted.
3268+
*for the pkrel. Also return the index OID and index opclasses of the
3269+
*index supporting the primary key.
3270+
*
3271+
*All parameters except pkrel are output parameters. Also, the function
3272+
*return value is the number of attributes in the primary key.
3273+
*
3274+
*Used when the column list in the REFERENCES specification is omitted.
32303275
*/
32313276
staticint
32323277
transformFkeyGetPrimaryKey(Relationpkrel,Oid*indexOid,
32333278
List**attnamelist,
3234-
int16*attnums,Oid*atttypids)
3279+
int16*attnums,Oid*atttypids,
3280+
Oid*opclasses)
32353281
{
32363282
List*indexoidlist,
32373283
*indexoidscan;
@@ -3287,6 +3333,7 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
32873333

32883334
attnums[i]=pkattno;
32893335
atttypids[i]=attnumTypeId(pkrel,pkattno);
3336+
opclasses[i]=indexStruct->indclass[i];
32903337
*attnamelist=lappend(*attnamelist,
32913338
makeString(pstrdup(NameStr(*attnumAttName(pkrel,pkattno)))));
32923339
}
@@ -3301,11 +3348,13 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
33013348
*
33023349
*Make sure that the attributes of a referenced table belong to a unique
33033350
*(or primary key) constraint. Return the OID of the index supporting
3304-
*the constraint.
3351+
*the constraint, as well as the opclasses associated with the index
3352+
*columns.
33053353
*/
33063354
staticOid
33073355
transformFkeyCheckAttrs(Relationpkrel,
3308-
intnumattrs,int16*attnums)
3356+
intnumattrs,int16*attnums,
3357+
Oid*opclasses)/* output parameter */
33093358
{
33103359
Oidindexoid=InvalidOid;
33113360
boolfound= false;
@@ -3370,6 +3419,7 @@ transformFkeyCheckAttrs(Relation pkrel,
33703419
{
33713420
if (attnums[j]==indexStruct->indkey[i])
33723421
{
3422+
opclasses[j]=indexStruct->indclass[i];
33733423
found= true;
33743424
break;
33753425
}

‎src/test/regress/expected/alter_table.out‎

Lines changed: 16 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -194,20 +194,24 @@ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" fo
194194
CREATE TEMP TABLE FKTABLE (ftest1 inet);
195195
-- This next should fail, because inet=int does not exist
196196
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
197-
ERROR:operator does not exist: inet = integer
198-
HINT:No operator matches the given nameandargument type(s). You may need to add explicit type casts.
197+
ERROR:foreign key constraint "$1" cannot be implemented
198+
DETAIL:Key columns "ftest1"and"ptest1" are of incompatible types: inet and integer.
199199
-- This should also fail for the same reason, but here we
200200
-- give the column name
201201
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
202-
ERROR:operator does not exist: inet = integer
203-
HINT:No operator matches the given nameandargument type(s). You may need to add explicit type casts.
202+
ERROR:foreign key constraint "$1" cannot be implemented
203+
DETAIL:Key columns "ftest1"and"ptest1" are of incompatible types: inet and integer.
204204
-- This should succeed, even though they are different types
205205
-- because varchar=int does exist
206206
DROP TABLE FKTABLE;
207207
CREATE TEMP TABLE FKTABLE (ftest1 varchar);
208208
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
209+
WARNING: foreign key constraint "$1" will require costly sequential scans
210+
DETAIL: Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
209211
-- As should this
210212
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
213+
WARNING: foreign key constraint "$2" will require costly sequential scans
214+
DETAIL: Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
211215
DROP TABLE pktable cascade;
212216
NOTICE: drop cascades to constraint $2 on table fktable
213217
NOTICE: drop cascades to constraint $1 on table fktable
@@ -218,27 +222,27 @@ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" fo
218222
-- This should fail, because we just chose really odd types
219223
CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
220224
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
221-
ERROR:operator does not exist: cidr = integer
222-
HINT:No operator matches the given nameandargument type(s). You may need to add explicit type casts.
225+
ERROR:foreign key constraint "$1" cannot be implemented
226+
DETAIL:Key columns "ftest1"and"ptest1" are of incompatible types: cidr and integer.
223227
DROP TABLE FKTABLE;
224228
-- Again, so should this...
225229
CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
226230
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
227231
references pktable(ptest1, ptest2);
228-
ERROR:operator does not exist: cidr = integer
229-
HINT:No operator matches the given nameandargument type(s). You may need to add explicit type casts.
232+
ERROR:foreign key constraint "$1" cannot be implemented
233+
DETAIL:Key columns "ftest1"and"ptest1" are of incompatible types: cidr and integer.
230234
DROP TABLE FKTABLE;
231235
-- This fails because we mixed up the column ordering
232236
CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
233237
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
234238
references pktable(ptest2, ptest1);
235-
ERROR:operator does not exist: integer = inet
236-
HINT:No operator matches the given nameandargument type(s). You may need to add explicit type casts.
239+
ERROR:foreign key constraint "$1" cannot be implemented
240+
DETAIL:Key columns "ftest1"and"ptest2" are of incompatible types: integer and inet.
237241
-- As does this...
238242
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
239243
references pktable(ptest1, ptest2);
240-
ERROR:operator does not exist: inet = integer
241-
HINT:No operator matches the given nameandargument type(s). You may need to add explicit type casts.
244+
ERROR:foreign key constraint "$1" cannot be implemented
245+
DETAIL:Key columns "ftest2"and"ptest1" are of incompatible types: inet and integer.
242246
-- temp tables should go away by themselves, need not drop them.
243247
-- test check constraint adding
244248
create table atacc1 ( test int );

0 commit comments

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