Re: 8.2/8.3 incompatibility

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> 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.

Bingo --- what's happening is that that's a cast to character(1):

regression=# select 'abcde'::varchar(5)::character;
bpchar
--------
a
(1 row)

> 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.

Yeah, we've found the "right" operator, but now we have to make sure
that the parser finds the same one. Casting ensures that there is a
unique exact match and we aren't risking an unexpected result from the
ambiguous-operator resolution heuristics.

If we weren't generating a textual representation of the query, but
building a post-parse-analysis form directly, we wouldn't need these
pushups because we could just store the desired operator's OID into the
data structure. But from a readability and maintainability standpoint,
the text format is probably safest. Anyway I certainly don't care to
risk back-patching such a large change as that would be.

> So I think the fix is just to remove the four lines responsible for putting
> the casts there at all.

I'm too lazy to generate a counterexample right now, but this *will*
break things. We need to fix the typmod issue instead.

After a few moment's thought, I'm tempted by the idea of not using
format_type_be here, but always emitting the type name in the format
"schema"."typname" --- this will guarantee that the parser doesn't
insert any default typmod associated with the weird SQL-spec rules
for certain special type names. That would fix things for character,
and also bit which has the same kind of issue, but can anyone think of
a case it would make worse?

regards, tom lane