Обсуждение: User-defined Operator Pushdown and Collations
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:
Thanks!
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:
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
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, hFilter: ((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:
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
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
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
P
So without this rule the
expression would be pushed down and could then give different results.
regards, tom lane
Paul Ramsey <pramsey@cleverelephant.ca> writes:
> On Sun, Nov 27, 2016 at 9:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Why doesn't hs_fdw.h have a collation?
> I think I'm missing something, I cannot find a file like that anywhere.
I was referring to the variable shown in your EXPLAIN.
> 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?
Yeah, we don't know whether the operator actually cares about its input
collation. It'd be possible to be much more liberal if we knew it did
not, but such labeling was not included in the design for the collation
facility. That might've been a mistake ...
regards, tom lane
On Sun, Nov 27, 2016 at 11:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Paul Ramsey <pramsey@cleverelephant.ca> writes:
> On Sun, Nov 27, 2016 at 9:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Why doesn't hs_fdw.h have a collation?
> I think I'm missing something, I cannot find a file like that anywhere.
I was referring to the variable shown in your EXPLAIN.
Ah right. Why would hs_fdw.h have a collation, it's an hstore. It's not declared with a collation (the CREATE TYPE call doesn't set the COLLATEABLE attribue to true). Again my ignorance is running ahead of me: does every object in the database necessarily have a collation?
CREATE FOREIGN TABLE hs_fdw ( id integer, h hstore collate "en_CA.UTF-8") server foreign_server OPTIONS (table_name 'hs');
ERROR: collations are not supported by type hstore
> 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?
Yeah, we don't know whether the operator actually cares about its input
collation. It'd be possible to be much more liberal if we knew it did
not, but such labeling was not included in the design for the collation
facility. That might've been a mistake ...
In this case the hammer seems very large, since only one side of the '<-' operator is even collatable. Mind you, if it *did* work it would still bubble up to a case of 'text = text' at the top node, so the problem would still remain. Although it seems unfair: I can definite declare a table with a text column and run a query with Const = Var and it'll ship that OpExpr over, which seems no more fishy than what I'm asking the hstore to do.
hstore=# explain (verbose) select * from txt_fdw where txt = 'this';
QUERY PLAN
---------------------------------------------------------------------------
Foreign Scan on public.txt_fdw (cost=100.00..127.20 rows=7 width=36)
Output: id, txt
Remote SQL: SELECT id, txt FROM public.txt WHERE ((txt = 'this'::text))
(3 rows)
The fdw table can't know much about what the remote collation is, it only knows what I've told it locally which is that it's the default, so everything matches up. Sounds like the problem is hstore lacking a collation? Or, that lacking a collation is not considered equivalent to the default in the testing code.
P
P
regards, tom lane