Dear patchers,
Here is my second and last try of the day.
This patch adds a "notice" at constraint creation time if the referential
integrity check is to be "costly", that is it cannot use the index due to
some incompatibility.
The patch was generated with the "difforig" script against the current cvs
head.
I put much validation which looks fine to me, but it is only me.
I'm not that satisfied with the wording and the content of the error
message. Any better suggestion would be welcome.
Have a nice day,
--
Fabien Coelho - [EMAIL PROTECTED]
*** ./src/backend/commands/tablecmds.c.orig Wed Mar 3 09:18:19 2004
--- ./src/backend/commands/tablecmds.c Wed Mar 3 18:27:04 2004
***************
*** 90,98 ****
int16 *attnums, Oid *atttypids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
! int16 *attnums, Oid *atttypids);
static Oid transformFkeyCheckAttrs(Relation pkrel,
! int numattrs, int16 *attnums);
static void validateForeignKeyConstraint(FkConstraint *fkconstraint,
Relation rel, Relation pkrel);
static void createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
--- 90,98 ----
int16 *attnums, Oid *atttypids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
! int16 *attnums, Oid *atttypids, Oid
*idxopclass);
static Oid transformFkeyCheckAttrs(Relation pkrel,
! int numattrs, int16 *attnums, Oid*
idxopclass);
static void validateForeignKeyConstraint(FkConstraint *fkconstraint,
Relation rel, Relation pkrel);
static void createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
***************
*** 3016,3021 ****
--- 3016,3022 ----
int16 fkattnum[INDEX_MAX_KEYS];
Oid pktypoid[INDEX_MAX_KEYS];
Oid fktypoid[INDEX_MAX_KEYS];
+ Oid idxopclass[INDEX_MAX_KEYS];
int i;
int numfks,
numpks;
***************
*** 3092,3097 ****
--- 3093,3099 ----
{
pkattnum[i] = fkattnum[i] = 0;
pktypoid[i] = fktypoid[i] = InvalidOid;
+ idxopclass[i] = InvalidOid;
}
numfks = transformColumnNameList(RelationGetRelid(rel),
***************
*** 3108,3114 ****
{
numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid,
&fkconstraint->pk_attrs,
!
pkattnum, pktypoid);
}
else
{
--- 3110,3116 ----
{
numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid,
&fkconstraint->pk_attrs,
!
pkattnum, pktypoid, idxopclass);
}
else
{
***************
*** 3116,3122 ****
fkconstraint->pk_attrs,
pkattnum, pktypoid);
/* Look for an index matching the column list */
! indexOid = transformFkeyCheckAttrs(pkrel, numpks, pkattnum);
}
/* Be sure referencing and referenced column types are comparable */
--- 3118,3124 ----
fkconstraint->pk_attrs,
pkattnum, pktypoid);
/* Look for an index matching the column list */
! indexOid = transformFkeyCheckAttrs(pkrel, numpks, pkattnum,
idxopclass);
}
/* Be sure referencing and referenced column types are comparable */
***************
*** 3131,3141 ****
* fktypoid[i] is the foreign key table's i'th element's type
* pktypoid[i] is the primary key table's i'th element's type
*
! * We let oper() do our work for us, including ereport(ERROR) if the
! * types don't compare with =
*/
! Operator o = oper(makeList1(makeString("=")),
! fktypoid[i], pktypoid[i],
false);
ReleaseSysCache(o);
}
--- 3133,3153 ----
* fktypoid[i] is the foreign key table's i'th element's type
* pktypoid[i] is the primary key table's i'th element's type
*
! * we let oper() do our work for us, including ereport(ERROR)
! * if the types don't compare with =
*/
! Operator o = oper(makeList1(makeString("=")),
! fktypoid[i], pktypoid[i], false);
!
! /* is the operator found index-compatible?
! * we check the operator found wrt the expected opclass of the index.
! */
! Oid opno = oprid(o);
! if (!op_in_opclass(opno, idxopclass[i]))
! {
! ereport(NOTICE, (errmsg("costly cross-type foreign key "
! "because of
component %d", i+1)));
! }
ReleaseSysCache(o);
}
***************
*** 3231,3237 ****
static int
transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
! int16 *attnums, Oid *atttypids)
{
List *indexoidlist,
*indexoidscan;
--- 3243,3249 ----
static int
transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
! int16 *attnums, Oid *atttypids, Oid
*idxopclass)
{
List *indexoidlist,
*indexoidscan;
***************
*** 3287,3292 ****
--- 3299,3305 ----
attnums[i] = pkattno;
atttypids[i] = attnumTypeId(pkrel, pkattno);
+ idxopclass[i] = indexStruct->indclass[i];
*attnamelist = lappend(*attnamelist,
makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno)))));
}
***************
*** 3305,3311 ****
*/
static Oid
transformFkeyCheckAttrs(Relation pkrel,
! int numattrs, int16 *attnums)
{
Oid indexoid = InvalidOid;
bool found = false;
--- 3318,3324 ----
*/
static Oid
transformFkeyCheckAttrs(Relation pkrel,
! int numattrs, int16 *attnums, Oid
*idxopclass)
{
Oid indexoid = InvalidOid;
bool found = false;
***************
*** 3370,3375 ****
--- 3383,3389 ----
{
if (attnums[j] ==
indexStruct->indkey[i])
{
+ idxopclass[j] =
indexStruct->indclass[i];
found = true;
break;
}
***************
*** 3379,3384 ****
--- 3393,3399 ----
}
}
}
+
ReleaseSysCache(indexTuple);
if (found)
break;
*** ./src/test/regress/expected/alter_table.out.orig Wed Mar 3 10:51:40 2004
--- ./src/test/regress/expected/alter_table.out Wed Mar 3 18:20:20 2004
***************
*** 206,213 ****
--- 206,215 ----
DROP TABLE FKTABLE;
CREATE TEMP TABLE FKTABLE (ftest1 varchar);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
+ NOTICE: costly cross-type foreign key because of component 1
-- As should this
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
+ NOTICE: costly cross-type foreign key because of component 1
DROP TABLE pktable cascade;
NOTICE: drop cascades to constraint $2 on table fktable
NOTICE: drop cascades to constraint $1 on table fktable
*** ./src/test/regress/expected/foreign_key.out.orig Wed Mar 3 10:51:37 2004
--- ./src/test/regress/expected/foreign_key.out Wed Mar 3 18:20:13 2004
***************
*** 738,746 ****
--- 738,748 ----
-- This should succeed, even though they are different types
-- because varchar=int does exist
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable);
+ NOTICE: costly cross-type foreign key because of component 1
DROP TABLE FKTABLE;
-- As should this
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));
+ NOTICE: costly cross-type foreign key because of component 1
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- Two columns, two tables
***************
*** 1034,1036 ****
--- 1036,1143 ----
COMMIT;
ERROR: insert or update on table "fktable" violates foreign key constraint "$1"
DETAIL: Key (fk)=(200) is not present in table "pktable".
+ -- test notice about expensive referential integrity checks,
+ -- where the index cannot be used because of type incompatibilities.
+ DROP TABLE fktable CASCADE;
+ DROP TABLE pktable CASCADE;
+ CREATE TABLE pktable (
+ id1 INT4 PRIMARY KEY,
+ id2 VARCHAR(4) UNIQUE,
+ id3 REAL UNIQUE,
+ UNIQUE(id1, id2),
+ UNIQUE(id1, id3),
+ UNIQUE(id2, id3),
+ UNIQUE(id1, id2, id3)
+ );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for
table "pktable"
+ NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id2_key" for
table "pktable"
+ NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id3_key" for
table "pktable"
+ NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id1_key" for
table "pktable"
+ NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id1_key1" for
table "pktable"
+ NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id2_key1" for
table "pktable"
+ NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id1_key2" for
table "pktable"
+ CREATE TABLE fktable (
+ x1 INT4 REFERENCES pktable(id1),
+ x2 VARCHAR(4) REFERENCES pktable(id2),
+ x3 REAL REFERENCES pktable(id3),
+ x4 TEXT
+ );
+ -- check individual constraints with alter table.
+ -- otherwise one cannot know from the error message
+ -- which constraint triggered the message.
+ -- 1 of them
+ ALTER TABLE fktable ADD CONSTRAINT fk_1_1
+ FOREIGN KEY (x1) REFERENCES pktable(id1);
+ ALTER TABLE fktable ADD CONSTRAINT fk_1_2
+ FOREIGN KEY (x1) REFERENCES pktable(id2);
+ ALTER TABLE fktable ADD CONSTRAINT fk_1_3
+ FOREIGN KEY (x1) REFERENCES pktable(id3);
+ NOTICE: costly cross-type foreign key because of component 1
+ ALTER TABLE fktable ADD CONSTRAINT fk_2_1
+ FOREIGN KEY (x2) REFERENCES pktable(id1);
+ NOTICE: costly cross-type foreign key because of component 1
+ ALTER TABLE fktable ADD CONSTRAINT fk_2_2
+ FOREIGN KEY (x2) REFERENCES pktable(id2);
+ ALTER TABLE fktable ADD CONSTRAINT fk_2_3
+ FOREIGN KEY (x2) REFERENCES pktable(id3);
+ NOTICE: costly cross-type foreign key because of component 1
+ ALTER TABLE fktable ADD CONSTRAINT fk_3_1
+ FOREIGN KEY (x3) REFERENCES pktable(id1);
+ NOTICE: costly cross-type foreign key because of component 1
+ ALTER TABLE fktable ADD CONSTRAINT fk_3_2
+ FOREIGN KEY (x3) REFERENCES pktable(id2);
+ ALTER TABLE fktable ADD CONSTRAINT fk_3_3
+ FOREIGN KEY (x3) REFERENCES pktable(id3);
+ ALTER TABLE fktable ADD CONSTRAINT fk_4_1
+ FOREIGN KEY (x4) REFERENCES pktable(id1);
+ NOTICE: costly cross-type foreign key because of component 1
+ ALTER TABLE fktable ADD CONSTRAINT fk_4_2
+ FOREIGN KEY (x4) REFERENCES pktable(id2);
+ ALTER TABLE fktable ADD CONSTRAINT fk_4_3
+ FOREIGN KEY (x4) REFERENCES pktable(id3);
+ NOTICE: costly cross-type foreign key because of component 1
+ -- 3 of them
+ ALTER TABLE fktable ADD CONSTRAINT fk_123_123
+ FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id1,id2,id3);
+ ALTER TABLE fktable ADD CONSTRAINT fk_213_213
+ FOREIGN KEY (x2,x1,x3) REFERENCES pktable(id2,id1,id3);
+ ALTER TABLE fktable ADD CONSTRAINT fk_321_321
+ FOREIGN KEY (x3,x2,x1) REFERENCES pktable(id3,id2,id1);
+ ALTER TABLE fktable ADD CONSTRAINT fk_123_213
+ FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id1,id3);
+ NOTICE: costly cross-type foreign key because of component 2
+ ALTER TABLE fktable ADD CONSTRAINT fk_123_321
+ FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id3,id2,id1);
+ NOTICE: costly cross-type foreign key because of component 1
+ NOTICE: costly cross-type foreign key because of component 3
+ ALTER TABLE fktable ADD CONSTRAINT fk_123_312
+ FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id3,id1,id2);
+ NOTICE: costly cross-type foreign key because of component 1
+ NOTICE: costly cross-type foreign key because of component 2
+ -- 2 of them
+ ALTER TABLE fktable ADD CONSTRAINT fk_13_31
+ FOREIGN KEY (x1,x3) REFERENCES pktable(id3,id1);
+ NOTICE: costly cross-type foreign key because of component 1
+ NOTICE: costly cross-type foreign key because of component 2
+ ALTER TABLE fktable ADD CONSTRAINT fk_13_13
+ FOREIGN KEY (x1,x3) REFERENCES pktable(id1,id3);
+ ALTER TABLE fktable ADD CONSTRAINT fk_14_31
+ FOREIGN KEY (x1,x4) REFERENCES pktable(id3,id1);
+ NOTICE: costly cross-type foreign key because of component 1
+ NOTICE: costly cross-type foreign key because of component 2
+ ALTER TABLE fktable ADD CONSTRAINT fk_14_13
+ FOREIGN KEY (x1,x4) REFERENCES pktable(id1,id3);
+ NOTICE: costly cross-type foreign key because of component 2
+ ALTER TABLE fktable ADD CONSTRAINT fk_12_12
+ FOREIGN KEY (x1,x2) REFERENCES pktable(id1,id2);
+ ALTER TABLE fktable ADD CONSTRAINT fk_12_21
+ FOREIGN KEY (x1,x2) REFERENCES pktable(id2,id1);
+ NOTICE: costly cross-type foreign key because of component 2
+ ALTER TABLE fktable ADD CONSTRAINT fk_42_12
+ FOREIGN KEY (x4,x2) REFERENCES pktable(id1,id2);
+ NOTICE: costly cross-type foreign key because of component 1
+ ALTER TABLE fktable ADD CONSTRAINT fk_42_21
+ FOREIGN KEY (x4,x2) REFERENCES pktable(id2,id1);
+ NOTICE: costly cross-type foreign key because of component 2
+ DROP TABLE fktable CASCADE;
+ DROP TABLE pktable CASCADE;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
