Re: User-defined Operator Pushdown and Collations

On Fri, Nov 25, 2016 at 11:30 AM, Paul Ramsey <pramsey(at)cleverelephant(dot)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. In other parts of
the code, the default collation and collection 0 are treated as both
leading to a state of FDW_COLLATE_NONE. But the OpExpr instead of bubbling
FDW_COLLATE_NONE up the chain, returns false and ends the shippability of
the Node.

What are the issues around shipping nodes of different collations to the
remote? All the nodes in my example are foreign Var, or local Const, and
all either are collation 0 or DEFAULT_COLLATION_ID. Surely it should be
shippable?

P

> I'm still working at wrapping my head around why this is good or not, but
> if there's an obvious explanation and/or workaround, I'd love to know.
>
> Thanks!
>
> P
>
>