Re: User-defined Operator Pushdown and Collations

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

> Paul Ramsey <pramsey(at)cleverelephant(dot)ca> writes:
> > On Sun, Nov 27, 2016 at 9:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
>