"Harald Fuchs" <hari.fuchs@xxxxxxxxxxxxxx> writes: > (Note the different column types.) > > This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the > following (somewhat misleading) error message: > > ERROR: insert or update on table "t2" violates foreign key constraint "t2_t1id_fk" > DETAIL: Key (t1id)=(t1id1) is not present in table "t1". > > Should this be documented explicitly? > Should the error message look different? Hm. I think this is just a bug. This shouldn't be an error at all. The query r_triggers.c is executing is (after simplifying a bit): SELECT * FROM t2 fk LEFT OUTER JOIN t1 pk ON ( pk.id = fk.t1id::character) WHERE pk.id IS NULL AND fk.t1id IS NOT NULL Note the cast. Really that cast should be char(5). It may be that we have to carry the typmod here. But really I don't understand why we put casts here at all. The whole point of using the opfamily to find the operator in advance was so that we could be sure to find the "right" operator. That means we should be able to put the operator there without casts and be confident that it'll find the right operator. The only case where the types might not match would be for operators which are used for multiple types -- such as binary compatible types like char(n)/varchar/text. So I think the fix is just to remove the four lines responsible for putting the casts there at all. --- ri_triggers.c 30 Jan 2008 14:27:38 +0000 1.102 +++ ri_triggers.c 07 Feb 2008 17:51:28 +0000 @@ -2920,13 +2920,9 @@ nspname = get_namespace_name(operform->oprnamespace); appendStringInfo(buf, " %s %s", sep, leftop); - if (leftoptype != operform->oprleft) - appendStringInfo(buf, "::%s", format_type_be(operform->oprleft)); appendStringInfo(buf, " OPERATOR(%s.", quote_identifier(nspname)); appendStringInfoString(buf, oprname); appendStringInfo(buf, ") %s", rightop); - if (rightoptype != operform->oprright) - appendStringInfo(buf, "::%s", format_type_be(operform->oprright)); ReleaseSysCache(opertup); } -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
