Re: CREATE ROUTINE MAPPING

On Wed, Jan 17, 2018 at 11:09:19AM -0500, Corey Huinker wrote:
> > > CREATE ROUTINE MAPPING local_routine_name
> > > > FOR (FUNCTION | PROCEDURE) remote_routine_name ( [ [ argmode ] [
> > argname ]
> > > > argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
> > > > [ RETURNS rettype
> > > > | RETURNS TABLE ( column_name column_type [, ...] ) ]
> > > > SERVER foreign_server_name
> > > > [ (option [, ...]) ]
> > > >
> > > > Does that seem like too broad an interpretation?
> > > >
> > >
> > > I had entertained having a pg_routine_mappings table like
> > > pg_user_mappings, and we still could, if the proc's language of
> > > 'external' clued the planner to look for the mapping. I can see
> > > arguments for either approach.
> >
> > It would be good to have them in the catalog somehow if we make CREATE
> > ROUTINE MAPPING a DDL. If I've read the standard correctly, there are
> > parts of information_schema which come into play for those routine
> > mappings.
> >
> > > Before anyone asks, I looked for, and did not find, any suggestion of
> > > IMPORT FOREIGN ROUTINE a la IMPORT FOREIGN SCHEMA, so as of yet there
> > > wouldn't be any way to grab all the functions that .a foreign server is
> > > offering up.
> >
> > How about making an option to IMPORT FOREIGN SCHEMA do it?
> >
> >
>
> Ok, so the steps seem to be:
> 1. settle on syntax.
> 2. determine data dictionary structures
> 3. parse and create those structures
> 4. "handle" external functions locally
> 5. provide structures passed to FDW handlers so that they can handle
> external functions
> 6. implement those handlers in postgres_fdw
>
> #1 is largely prescribed for us, though I'm curious as to how the CRM
> statements I've made up in examples above would look like as CREATE
> FUNCTION ... SERVER ...
>
> #2 deserves a lot of debate, but probably mostly hinges on the new
> "language" and how to associate a pg_proc entry with a pg_foreign_server
>
> #3 i'm guessing this is a lot of borrowing code from CREATE ROUTINE MAPPING
> but is otherwise pretty straightforward.
>
> #4 an external function obviously cannot be executed locally, doing so
> means that the planner failed to push it down, so this is probably
> stub-error functions
>
> #5 These functions would essentially be passed in the same as foreign
> columns with the "name" as "f(a,b,4)", and the burden of forming the remote
> query is on the FDW
>
> Which gets tricky. What should happen in simple situations is obvious:
>
> SELECT t.x, remote_func1(), remote_func2(t.y)
>
> FROM remote_table t
>
> WHERE t.active = true;
>
>
> that would become this query on the remote side:
>
> SELECT t.x, local_func1(), local_func2(t.y)
>
> FROM local_table t
>
> WHERE t.active = true;
>
> And it's still simple when local functions consume remote input
>
>
> SELECT local_func1(remote_func1(r.x)) FROM remote_table r WHERE r.active =
> true;
>
>
> 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.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate