Re: CREATE ROUTINE MAPPING
От | Hannu Krosing |
---|---|
Тема | Re: CREATE ROUTINE MAPPING |
Дата | |
Msg-id | CAHDDfCkKfPuRfCK7OYTLNGN=uqLxxPQeFCM15LTsv3-Hq68J8g@mail.gmail.com обсуждение исходный текст |
Ответ на | CREATE ROUTINE MAPPING (Corey Huinker <corey.huinker@gmail.com>) |
Ответы |
Re: CREATE ROUTINE MAPPING
(Corey Huinker <corey.huinker@gmail.com>)
Re: CREATE ROUTINE MAPPING (David Fetter <david@fetter.org>) |
Список | pgsql-hackers |
Hi Corey
Have you looked at pl/proxy ?
It does this and then some (sharding)
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.huinker@gmail.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_specSERVER 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 tWHERE t.active = true;that would become this query on the remote side:SELECT t.x, local_func1(), local_func2(t.y)FROM local_table tWHERE 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');andSELECT * 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.
В списке pgsql-hackers по дате отправления: