Re: CREATE ROUTINE MAPPING - Mailing list pgsql-hackers

On Thu, Jan 25, 2018 at 10:43 AM, 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.
>
> - In pg_proc, store foreign functions as having a new language,
>   sql_med, which doesn't actually exist.  This "language" would
>   function as a hint to the planner.
>
> - Add a new system catalog for foreign functions that references
>   pg_proc and pg_foreign_server. Writing to it would also do the usual
>   stuff with pg_depend.
>
> - During planning, at least to start, we'd ensure that foreign
>   functions can only take arguments on the same server.

May be I am going in details, not expected at this stage. Right now
FDWs have a notion of shippability - i.e. certain expressions can be
evaluated on the remote server. Shippable expressions are pushed down
to the foreign server, but that's optional. Unshippable expressions
however can not be pushed down to the foreign server. With this
change, we will have a new notion of shippability where a
function/expression must be shipped to the foreign server. As long as
these strict-shippable expressions are part of shippable expressions,
things work as they are today, but as an earlier mail by Corey shows,
if those are expressions are not part of shippable expressions, they
need to be evaluated on foreign server apart from the query that gets
pushed down. You seem to be suggesting that we do not implement it
right now, which is fine. But whatever design we chose should be
extensible to do that.

A possible way to implement this may be to implement sql-med language
handler which takes the responsibility to interact with FDW and
evaluate the function. That way we can use existing function
evaluation infrastructure.

>
> - Once it's established that the combinations could actually work,
>   execution gets pushed to the foreign server(s)
>

Overall this structure looks ok to me.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company