Re: CREATE ROUTINE MAPPING - Mailing list pgsql-hackers

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?

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