On Sun, Nov 27, 2016 at 9:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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?
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.
