Re: CREATE ROUTINE MAPPING

On Tue, Sep 4, 2018 at 5:48 AM, David Fetter <david@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 <david@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