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.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
