Re: jsonb, collection & postgres_fdw

Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> writes:
> I still do not completely understand current criteria of shippable
> functions.
> I understood Tom's explanation, but:

> postgres=# create table t1(t text collate "C");
> CREATE TABLE
> postgres=# create foreign table ft1(t text collate "ru_RU") server
> pg_fdw options (table_name 't1');
> CREATE FOREIGN TABLE
> postgres=# explain select * from ft1 where lower(t)='some';
>                          QUERY PLAN
> ------------------------------------------------------------
>  Foreign Scan on ft1  (cost=100.00..132.07 rows=7 width=32)
> (1 row)

> lower(t) is pushed to remote server despite to the fact that "t" has
> different collations at local and remote servers.

Well, that's the case because you lied while creating the foreign
table. We have no practical way to cross-check whether the foreign
table's declaration is an accurate representation of the remote table,
so we just take it on faith that it is.

The problem that the collation check is trying to solve is that we
can't safely push COLLATE clauses to the remote server, because it
may not have the same set of collation names as the local server.
So we can only push clauses whose collation is entirely derivable
from the table column(s) they use. And then, per the above, we rely on
the user to make sure that the local and remote columns have equivalent
collations. (Which conceivably would have different names.)

> From my point of view, it will be nice to have flag in postgres_fdw
> server indicating that foreign and remote servers are identical
> and treat all functions as shippable in this case (not only built-in
> ones are belonging to explicitly specified shippable extensions).

Perhaps, but not everyone has that use-case. I'd even argue that it's
a minority use-case.

regards, tom lane