Re: jsonb, collection & postgres_fdw

On Mon, Aug 17, 2020 at 7:32 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Fri, Aug 14, 2020 at 12:46 PM Konstantin Knizhnik <k.knizhnik@postgrespro.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
wewill have some issues here. Since postgres_fdw can also be used for independently maintained postgres servers(may be
withdifferent 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
versionlevel, but also at each function level. We could have something like a configuration file which allows the user
tospecify the list of functions that are safely pushable to remote in his/her own postgres_fdw setup, and let the
postgres_fdwrefer this configuration file, while checking the pushability of the functions to remote. This way, the
userhas some control over what's pushed and what's not. Of course, this pushability check can only happen after the
mandatorychecks happening currently such as remote backend configuration settings such as collations etc. 

I agree with most of this. We need a way for a user to tell us which
function is safe to be executed on the foreign server (not just
postgres_fdw, but other kinds of FDWs as well). But maintaining that
as a configurable file and associating safety with an FDW isn't
sufficient. We should maintain that as a catalog. A function may be
safe to push down based on the FDW (a given function always behaves in
the same way on any of the servers of an FDW as its peer locally), or
may be associated with a server (a function is available and behaves
same as its local peer on certain server/s but not all). Going further
a local function may map to a function with a different name on the
remote server/fdw, so that same catalog may maintain the function
mapping. An FDW may decide to cache relevant information, update the
catalog using IMPORT FOREIGN SCHEMA(or ROUTINE), or add some defaults
when installing the extension.

More details are required to be worked out but here my initial thoughts on this.


--
Best Wishes,
Ashutosh Bapat