On Tue, Sep 4, 2018 at 5:48 AM, David Fetter <da...@fetter.org> wrote: > On Fri, Aug 31, 2018 at 05:18:26PM +0900, Masahiko Sawada wrote: >> On Thu, Jan 25, 2018 at 2:13 PM, David Fetter <da...@fetter.org> wrote: >> > On Thu, Jan 18, 2018 at 04:09:13PM -0500, Corey Huinker wrote: >> >> > >> >> > >> >> > > >> >> > > But other situations seem un-handle-able to me: >> >> > > >> >> > > SELECT remote_func1(l.x) FROM local_table l WHERE l.active = true; >> >> > >> >> > Do we have any way, or any plan to make a way, to push the set (SELECT >> >> > x FROM local_table WHERE active = true) to the remote side for >> >> > execution there? Obviously, there are foreign DBs that couldn't >> >> > support this, but I'm guessing they wouldn't have much by way of UDFs >> >> > either. >> >> > >> >> >> >> No. The remote query has to be generated at planning time, so it can't >> >> make >> >> predicates out of anything that can't be resolved into constants by the >> >> planner itself. The complexities of doing so would be excessive, far >> >> better >> >> to let the application developer split the queries up because they know >> >> better which parts have to resolve first. >> > >> > So Corey and I, with lots of inputs from Andrew Gierth and Matheus >> > Oliveira, have come up with a sketch of how to do this, to wit: >> > >> > - Extend CREATE FUNCTION to take either FOREIGN and SERVER or AS and >> > LANGUAGE as parameters, but not both. This seems simpler, at least >> > in a proof of concept, than creating SQL standard compliant grammar >> > out of whole cloth. The SQL standard grammar could be layered in >> > later via the rewriter if this turns out to work. >> >> I'm also interested in this feature. While studying this feature, I >> understood that this feature just pair a local function with a remote >> function, not means that creates a kind of virtual function that can >> be invoked on only foreign servers. For example, if we execute the >> following SQL the local_func() is invoked in local because the col1 >> column of local_table is referenced by it. >> >> SELECT * FROM local_table l WHERE local_func(l.col1) = 1; >> >> On the other hand, suppose we have the following routine mapping, >> >> CREATE ROUTINE MAPPING rmap FOR local_func(integer) OPTIONS >> (remote_func_schema = 'myschema', remote_func_name = 'remote_func'); >> >> and execute the similar SQL for a foreign table. We will get the >> following remote SQL. >> >> - Local SQL >> SELECT * FROM foreign_table f WHERE local_func(f.col1) = 1; >> >> - Remote SQL >> SELECT * FROM foreign_table f WHERE my_schema.remote_func(f.col1) = 1; >> >> In this concept, the CREATE ROUTINE MAPPING doesn't need to specify >> the return type of function but must specify the existing function in >> the local PostgreSQL. The mapped remote function is expected to have >> the same properly(arguments, return type etc) as the local function. I >> might be missing something, please give me feedback. >> >> Please find a attached PoC patch of ROUTINE MAPPING feature. This >> patch is missing many things such as the doc and the shippability >> supports but this patch adds the new system catalog pg_routine_mapping >> with three attributes: name, procid, serverid and enables FDWs to >> refer this mapping and and to replace the function. > > Sawada-san, > > Thanks very much for sending this. I just tried to compile it, and > got: > > make > make -C ./src/backend generated-headers > make[1]: Entering directory '/home/shackle/pggit/postgresql/src/backend' > make -C catalog distprep generated-header-symlinks > make[2]: Entering directory > '/home/shackle/pggit/postgresql/src/backend/catalog' > make[2]: *** No rule to make target > '../../../src/include/catalog/pg_routine_mapping.h', needed by 'bki-stamp'. > Stop. > make[2]: Leaving directory > '/home/shackle/pggit/postgresql/src/backend/catalog' > make[1]: *** [Makefile:141: submake-catalog-headers] Error 2 > make[1]: Leaving directory '/home/shackle/pggit/postgresql/src/backend' > make: *** [src/Makefile.global:369: submake-generated-headers] Error 2 > > Was there a file missing from the patch? >
Oops, sorry I missed the header file. Attached the PoC patches again. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
0001-PoC-Support-ROUTINE-MAPPING.patch
Description: Binary data
0002-Poc-postgres_fdw-support-routine-mappings.patch
Description: Binary data
