Re: User-defined Operator Pushdown and Collations

Paul Ramsey <pramsey@cleverelephant.ca> writes:
> On Fri, Nov 25, 2016 at 11:30 AM, Paul Ramsey <pramsey@cleverelephant.ca>
> wrote:
>> I've been trying to figure out an issue with operators not being pushed
>> down for user defined types, in this case "hstore". TL;DR:
>>
>> hstore=# explain (verbose) select * from hs_fdw where h -> 'a' = '1';
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> Foreign Scan on public.hs_fdw  (cost=100.00..157.78 rows=7 width=36)
>> Output: id, h
>> Filter: ((hs_fdw.h -> 'a'::text) = '1'::text)
>> Remote SQL: SELECT id, h FROM public.hs
>> (4 rows)
>>
>> In terms of "shippability" the "->" operator passes fine. It ends up not
>> being shipped because its collation bubbles up as FDW_COLLATE_NONE, and
>> gets kicked back as not deparseable around here:
>>
>> https://github.com/postgres/postgres/blob/4e026b32d4024b03856b4981b26c74
>> 7b7fef7afb/contrib/postgres_fdw/deparse.c#L499

> I'm finding this piece of code a little suspect, but that may just be my
> not fully understanding why/what determines when a collation is shippable.

> In the case of my example above, the OpExpr '->' has an input collation of
> 100 (DEFAULT_COLLATION_ID). The Var below has a collation of 0 (InvalidOid)
> and state of FDW_COLLATE_NONE, and the Const has collation of 100
> (DEFAULT_COLLATION_ID ) and state of FDW_COLLATE_NONE.

Why doesn't hs_fdw.h have a collation?

The intuition behind the rules in this area is that we'll only push down
expressions whose collation is traceable to a foreign Var.  Assuming that
you've correctly declared your foreign table with column collations that
match the column collations of the real table on the remote server, this
should ensure that you get the same collation-dependent behavior as you
would have gotten locally.  In this example, the expression's collation
behavior would be per DEFAULT_COLLATION_ID on both servers ... but they
might have different default collations. 

So without this rule the
expression would be pushed down and could then give different results.

                        regards, tom lane