Dear patchers,
This patch adds a "notice" at constraint creation time if the referential
integrity check is to be "costly", that is the comparison operator
involves some coercion. The patch also accepts the validation of the
regression tests with the added notice.
The patch was generated with the "difforig" script against the current cvs
head.
Thanks in advance for considering it, and for any comment or cheering,
--
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 09:30:57 2004
***************
*** 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);
}
--- 3131,3154 ----
* 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
*
! * First we try to find an inexpensive comparison =,
! * but there is no error if none is found.
*/
! Operator o = compatible_oper(makeList1(makeString("=")),
! fktypoid[i],
pktypoid[i], true);
!
! if (o == (Operator) NULL)
! {
! /* Now we let oper() do our work for us, including
ereport(ERROR)
! * if the types don't compare with =
! */
! o = oper(makeList1(makeString("=")),
! fktypoid[i], pktypoid[i], false);
!
! if (o != (Operator) NULL)
! ereport(NOTICE, (errmsg("costly cross-type foreign
key: "
!
"coercion needed")));
! }
ReleaseSysCache(o);
}
*** ./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 10:51:52 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: coercion needed
-- As should this
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
+ NOTICE: costly cross-type foreign key: coercion needed
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 10:51:49 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: coercion needed
DROP TABLE FKTABLE;
-- As should this
CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));
+ NOTICE: costly cross-type foreign key: coercion needed
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- Two columns, two tables
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
