Re: jsonb, collection & postgres_fdw

On 14.08.2020 09:40, Bharath Rupireddy wrote:
> On Thu, Aug 13, 2020 at 8:54 PM Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> Right now jsonb functions are treated as non-shippable by postgres_fdw
>> and so predicates with them are not pushed down to foreign server:
>>
>> I wonder if there is some way of making postgres_fdw to push this this
>> function to foreign server?
>> May be this check should be changed to:
>>
>> if (fe->inputcollid == InvalidOid || inner_cxt.state ==
>> FDW_COLLATE_NONE)
>> /* OK, inputs are all noncollatable */ ;
>>
> I think, in general, we may want to push the some of the local
> functions that may filter out tuples/rows to remote backend to reduce
> the data transfer(assuming collation and other settings are similar to
> that of the local backend), but definitely, not this way. One possible
> issue could be that, what if these functions are supported/installed
> on the local server, but not on the remote? May be because the remote
> postgres server version is different than that of the local? Is there
> a version check between local and remote servers in postgres_fdw?
>
> With Regards,
> Bharath Rupireddy.
> EnterpriseDB: http://www.enterprisedb.com

Right now postgres_fdw treat as shippable only builtin functions or
functions from extensions explicitly specified as shippable extensions
in parameters of this FDW server. So I do no see a problem here. Yes,
foreign server may have different version of Postgres which doesn't have
this built-in function or its  profile is different. It can happen if
postgres_fdw is used to connect two different servers which are
maintained independently. But in most cases I think, postgres_fdw is
used to organize some kind of cluster. In this case all nodes are
identical (hardware, OS, postgres version) and performance is very
critical (because scalability - of one of the goal of replacing single
node with cluster).
This is why push down of predicates is very critical in this case.

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.
Also when initialize postgres database, you can specify default collation.
I have not found any place in postgres_fdw which tries to check if
default collation of remote and local servers are the same
or specify collation explicitly when them are different.

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).
It will simplify using postres_fdw in clusters and makes it more efficient.