Re: CREATE ROUTINE MAPPING

Hi Corey

Have you looked at pl/proxy ?

It does this and then some (sharding)

It actually started out as a set of pl/pythonu functions, but then got
formalized into a full extension language for defining remote (potentially
sharded) function calls

Best Regards
Hannu Krosng

On Fri, 12 Jan 2018 at 03:38, Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:

> A few months ago, I was researching ways for formalizing calling functions
> on one postgres instance from another. RPC, basically. In doing so, I
> stumbled across an obscure part of the the SQL Standard called ROUTINE
> MAPPING, which is exactly what I'm looking for.
>
> The syntax specified is, roughly:
>
> CREATE ROUTINE MAPPING local_routine_name FOR remote_routine_spec
> SERVER my_server [ OPTIONS( ... ) ]
>
>
> Which isn't too different from CREATE USER MAPPING.
>
> The idea here is that if I had a local query:
>
> 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;
>
>
>
> That was probably the main intention of this feature, but I see a
> different possibility there. Consider the cases:
>
> SELECT remote_func(1,'a');
>
>
> and
>
> SELECT * FROM remote_srf(10, true);
>
>
> Now we could have written remote_func() and remote_srf() in plpythonu, and
> it could access whatever remote data that we wanted to see, but that
> exposes our local server to the untrusted pl/python module as well as
> python process overhead.
>
> We could create a specialized foreign data wrapper that requires a WHERE
> clause to include all the require parameters as predicates, essentially
> making every function a table, but that's awkward and unclear to an end
> user.
>
> Having the ability to import functions from other servers allows us to
> write foreign servers that expose functions to the local database, and
> those foreign servers handle the bloat and risks associated with accessing
> that remote data.
>
> Moreover, it would allow hosted environments (AWS, etc) that restrict the
> extensions that can be added to the database to still connect to those
> foreign data sources.
>
> I'm hoping to submit a patch for this someday, but it touches on several
> areas of the codebase where I have no familiarity, so I've put forth to
> spark interest in the feature, to see if any similar work is underway, or
> if anyone can offer guidance.
>
> Thanks in advance.
>