Re: jsonb, collection & postgres_fdw

On Tue, 18 Aug 2020 at 17:36, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> On Mon, Aug 17, 2020 at 7:32 PM Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> >
> > 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.
>
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.
>

While looking at something else in postgres_fdw, I came across an old
feature which I had completely forgotten about. We allow extensions to be
added to server options. Any object belonging to these extensions,
including functions, can be shipped to the foreign server. See
postres_fdw/sql/postgres_fdw.sql for examples. This is an awkward way since
there is no way to control individual functions and a UDF has to be part of
an extension to be shippable. It doesn't provide flexibility to map a local
function to a remote one if their names differ. But we have something. May
be we could dig past conversations to understand why it was done this way.

--
Best Wishes,
Ashutosh