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.knizhnik@postgrespro.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.