On Sun, Nov 27, 2016 at 9:31 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Paul Ramsey <pram...@cleverelephant.ca> writes: > > On Fri, Nov 25, 2016 at 11:30 AM, Paul Ramsey <pram...@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? > I think I'm missing something, I cannot find a file like that anywhere. > 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. OK, so there's a potential workaround w/ explicitly declared collations, I am hearing? With respect to this particular example, is this a case of a very large collation hammer getting in the way? Both '->' and '=' are operators that would be unaffected by collation, right? They are both just equality-based tests. But all operators are getting tested for coherent collation behaviour, so they get caught up in the net? Thanks! P > So without this rule the > expression would be pushed down and could then give different results. > > regards, tom lane >