Re: jsonb, collection & postgres_fdw

On Fri, Aug 14, 2020 at 12:46 PM Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>
> 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.
>

Agree, push down of predicates(with functions) to the remote backend helps
a lot. But, is it safe to push all the functions? For instance, functions
that deal with time/time zones, volatile functions etc. I'm not exactly
sure whether we will have some issues here. Since postgres_fdw can also be
used for independently maintained postgres servers(may be with different
versions), we must have a mechanism to know the compatibility.

>
> 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.
>

I think it's better not to have a flag for this. As we have to deal with
the compatibility not only at the server version level, but also at each
function level. We could have something like a configuration file which
allows the user to specify the list of functions that are safely pushable
to remote in his/her own postgres_fdw setup, and let the postgres_fdw refer
this configuration file, while checking the pushability of the functions to
remote. This way, the user has some control over what's pushed and what's
not. Of course, this pushability check can only happen after the mandatory
checks happening currently such as remote backend configuration settings
such as collations etc.

Feel free to correct me.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com