Re: 8.2/8.3 incompatibility

"Harald Fuchs" <hari(dot)fuchs(at)googlemail(dot)com> 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!