Re: User-defined Operator Pushdown and Collations

On Sun, Nov 27, 2016 at 9:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Paul Ramsey <pramsey(at)cleverelephant(dot)ca> writes:
> > 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.
>
> 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
>