Re: CREATE ROUTINE MAPPING - Mailing list pgsql-hackers
| From | Corey Huinker |
|---|---|
| Subject | Re: CREATE ROUTINE MAPPING |
| Date | |
| Msg-id | CADkLM=fSY6yzbtnwrKKHATeBPkV8d1G_fN0CxnOe1U7htQoSEw@mail.gmail.com Whole thread Raw |
| In response to | Re: CREATE ROUTINE MAPPING (David Fetter <david@fetter.org>) |
| Responses |
Re: CREATE ROUTINE MAPPING
|
| List | pgsql-hackers |
> > 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
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
#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
But other situations seem un-handle-able to me:SELECT local_func1(remote_func1(r.x)) FROM remote_table r WHERE r.active = true;
SELECT remote_func1(l.x) FROM local_table l WHERE l.active = true;In those cases, at least initially, I think the FDW handler is right to raise an error, because the function inputs are unknowable at query time, and the inputs cannot also be pushed down to the remote server. That might not be common, but I can see situations like this:
SELECT r.*
FROM remote_srf( ( SELECT remote_code_value FROM local_table_of_remote_codes WHERE local_code_value = 'xyz' ) ) r;
and we would want things like that to work. Currently is similar table-situations the FDW has no choice but to fetch the entire table and filter locally. That's good for tables, whose contents are knowable, but the set of possible function inputs is unreasonably large. The current workaround in table-land is to run the inner query locally, and present the result at a constant to a follow-up query, so maybe that's what we have to do here, at least initially.
I'm doing this thinking on the mailing list in the hopes that it evokes suggestions, warnings, suggested code samples, and of course, help.
#6 is where the FDW either does the translation or rejects the notion that functions can be pushed down, either outright or based on the usage of the function in the query.
I'm doing this thinking on the mailing list in the hopes that it evokes suggestions, warnings, suggested code samples, and of course, help.
pgsql-hackers by date: