On Tue, 18 Aug 2020 at 17:36, Ashutosh Bapat <ashutosh.bapat....@gmail.com> wrote:
> On Mon, Aug 17, 2020 at 7:32 PM Bharath Rupireddy > <bharath.rupireddyforpostg...@gmail.com> wrote: > > > > On Fri, Aug 14, 2020 at 12:46 PM Konstantin Knizhnik < > k.knizh...@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 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
