CREATE ROUTINE MAPPING
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.
On Thu, Jan 11, 2018 at 09:37:43PM -0500, Corey Huinker 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( ... ) ]
[neat use cases elided]
For what it's worth, the now-defunct DBI-Link I wrote had
remote_execute(), which did many of the things you describe here, only
with no help from the rest of PostgreSQL, as it was implemented
strictly in userland.
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
On Fri, Jan 12, 2018 at 8:07 AM, 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_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;
I think this is a desired feature. Being able to call a function on
remote server through local server is often useful.
PostgreSQL allows function overloading, which means that there can be
multiple functions with same name differing in argument types. So, the
syntax has to include the input parameters or their types at least.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
2018-01-12 10:02 GMT+01:00 Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>:
On Fri, Jan 12, 2018 at 8:07 AM, 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_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;
I think this is a desired feature. Being able to call a function on
remote server through local server is often useful.PostgreSQL allows function overloading, which means that there can be
multiple functions with same name differing in argument types. So, the
syntax has to include the input parameters or their types at least.
+1
Pavel
Show quoted text
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
PostgreSQL allows function overloading, which means that there can be
multiple functions with same name differing in argument types. So, the
syntax has to include the input parameters or their types at least.
"local_routine_name" and "remote_routine_spec" were my own paraphrasings of
what the spec implies. I'm nearly certain that the local routine name,
which the spec says is just an identifier, cannot have a parameter spec on
it, which leaves only one other place to define it, remote_routine_spec,
which wasn't defined at all. I _suppose_ parameter definitions could be
pushed into options, but that'd be ugly.
On Fri, Jan 12, 2018 at 11:11:26AM -0500, Corey Huinker wrote:
PostgreSQL allows function overloading, which means that there can
be multiple functions with same name differing in argument types.
So, the syntax has to include the input parameters or their types
at least."local_routine_name" and "remote_routine_spec" were my own
paraphrasings of what the spec implies. I'm nearly certain that the
local routine name, which the spec says is just an identifier,
cannot have a parameter spec on it, which leaves only one other
place to define it, remote_routine_spec, which wasn't defined at
all. I _suppose_ parameter definitions could be pushed into options,
but that'd be ugly.
In my draft of SQL:2011, which I don't think has substantive changes
to what's either in the official SQL:2011 or SQL:2016, it says:
<routine mapping definition> ::=
CREATE ROUTINE MAPPING <routine mapping name> FOR <specific routine designator>
SERVER <foreign server name> [ <generic options> ]
Syntax Rules
1) Let FSN be the <foreign server name>. Let RMN be the <routine mapping name>.
2) The catalog identified by the explicit or implicit catalog name of FSN shall include a foreign server
descriptor whose foreign server name is equivalent to FSN.
3) The SQL-environment shall not include a routine mapping descriptor whose routine mapping name is
RMN.
4) Let R be the SQL-invoked routine identified by the <specific
routine designator>. R shall identify an SQL-invoked regular function.
It goes on from there, but I think there's a reasonable interpretation
of this which allows us to use the same syntax as CREATE
(FUNCTION|PROCEDURE), apart from the body, e.g.:
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?
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
It goes on from there, but I think there's a reasonable interpretation
of this which allows us to use the same syntax as CREATE
(FUNCTION|PROCEDURE), apart from the body, e.g.: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?
That's really interesting. I didn't think to look in the definition of
CREATE FUNCTION to see if a SERVER option popped in there, but seems like a
more accessible way to introduce the notion of remote functions, because I
talked to a few developers about this before posting to the list, and only
one had ever heard of ROUTINE MAPPING and had no clear recollection of it.
An option on CREATE FUNCTION is going to get noticed (and used!) a lot
sooner.
Having said that, I think syntactically we have to implement CREATE ROUTINE
MAPPING, even if it is just translated to a CREATE FUNCTION call.
In either case, I suspected that pg_proc would need a nullable srvid column
pointing to pg_foreign_server, and possibly a new row in pg_language for
'external'. 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.
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.
On Fri, Jan 12, 2018 at 02:29:53PM -0500, Corey Huinker wrote:
It goes on from there, but I think there's a reasonable interpretation
of this which allows us to use the same syntax as CREATE
(FUNCTION|PROCEDURE), apart from the body, e.g.: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?
That's really interesting. I didn't think to look in the definition of
CREATE FUNCTION to see if a SERVER option popped in there, but seems like a
more accessible way to introduce the notion of remote functions,
It does indeed. Adding the functionality to CREATE
(FUNCTION|PROCEDURE) seems like a *much* better idea than trying to
wedge it into the CREATE ROUTINE MAPPING syntax.
because I talked to a few developers about this before posting to
the list, and only one had ever heard of ROUTINE MAPPING and had no
clear recollection of it. An option on CREATE FUNCTION is going to
get noticed (and used!) a lot sooner.
+1
Having said that, I think syntactically we have to implement CREATE ROUTINE
MAPPING, even if it is just translated to a CREATE FUNCTION call.In either case, I suspected that pg_proc would need a nullable srvid column
pointing to pg_foreign_server, and possibly a new row in pg_language for
'external'.
Makes a lot of sense.
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?
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
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;
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.
#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.
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 FDWWhich 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
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.
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.
- In pg_proc, store foreign functions as having a new language,
sql_med, which doesn't actually exist. This "language" would
function as a hint to the planner.
- Add a new system catalog for foreign functions that references
pg_proc and pg_foreign_server. Writing to it would also do the usual
stuff with pg_depend.
- During planning, at least to start, we'd ensure that foreign
functions can only take arguments on the same server.
- Once it's established that the combinations could actually work,
execution gets pushed to the foreign server(s)
What say?
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
On Thu, Jan 25, 2018 at 10:43 AM, 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.- In pg_proc, store foreign functions as having a new language,
sql_med, which doesn't actually exist. This "language" would
function as a hint to the planner.- Add a new system catalog for foreign functions that references
pg_proc and pg_foreign_server. Writing to it would also do the usual
stuff with pg_depend.- During planning, at least to start, we'd ensure that foreign
functions can only take arguments on the same server.
May be I am going in details, not expected at this stage. Right now
FDWs have a notion of shippability - i.e. certain expressions can be
evaluated on the remote server. Shippable expressions are pushed down
to the foreign server, but that's optional. Unshippable expressions
however can not be pushed down to the foreign server. With this
change, we will have a new notion of shippability where a
function/expression must be shipped to the foreign server. As long as
these strict-shippable expressions are part of shippable expressions,
things work as they are today, but as an earlier mail by Corey shows,
if those are expressions are not part of shippable expressions, they
need to be evaluated on foreign server apart from the query that gets
pushed down. You seem to be suggesting that we do not implement it
right now, which is fine. But whatever design we chose should be
extensible to do that.
A possible way to implement this may be to implement sql-med language
handler which takes the responsibility to interact with FDW and
evaluate the function. That way we can use existing function
evaluation infrastructure.
- Once it's established that the combinations could actually work,
execution gets pushed to the foreign server(s)
Overall this structure looks ok to me.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
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.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Attachments:
0001-PoC-Support-ROUTINE-MAPPING.patchapplication/octet-stream; name=0001-PoC-Support-ROUTINE-MAPPING.patchDownload
From 246cc94030ac8d24d65154e88571a86005262fcb Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 31 Aug 2018 16:54:53 +0900
Subject: [PATCH 1/2] PoC: Support ROUTINE MAPPING.
---
src/backend/catalog/Makefile | 2 +-
src/backend/catalog/dependency.c | 9 ++
src/backend/catalog/objectaddress.c | 45 ++++++++
src/backend/commands/alter.c | 1 +
src/backend/commands/event_trigger.c | 2 +
src/backend/commands/foreigncmds.c | 216 +++++++++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 1 +
src/backend/foreign/foreign.c | 61 ++++++++++
src/backend/parser/gram.y | 115 ++++++++++++++++++-
src/backend/tcop/utility.c | 32 ++++++
src/backend/utils/cache/lsyscache.c | 3 +-
src/backend/utils/cache/syscache.c | 34 ++++++
src/include/catalog/dependency.h | 1 +
src/include/catalog/indexing.h | 7 ++
src/include/commands/defrem.h | 4 +
src/include/foreign/foreign.h | 10 ++
src/include/nodes/nodes.h | 3 +
src/include/nodes/parsenodes.h | 29 +++++
src/include/parser/kwlist.h | 2 +-
src/include/utils/syscache.h | 3 +
20 files changed, 571 insertions(+), 9 deletions(-)
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 0865240..0ba23e5 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -42,7 +42,7 @@ CATALOG_HEADERS := \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h pg_extension.h \
pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
- pg_foreign_table.h pg_policy.h pg_replication_origin.h \
+ pg_routine_mapping.h pg_foreign_table.h pg_policy.h pg_replication_origin.h \
pg_default_acl.h pg_init_privs.h pg_seclabel.h pg_shseclabel.h \
pg_collation.h pg_partitioned_table.h pg_range.h pg_transform.h \
pg_sequence.h pg_publication.h pg_publication_rel.h pg_subscription.h \
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 4f1d365..046c839 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -48,6 +48,7 @@
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
#include "catalog/pg_rewrite.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_subscription.h"
#include "catalog/pg_tablespace.h"
@@ -163,6 +164,7 @@ static const Oid object_classes[] = {
ForeignDataWrapperRelationId, /* OCLASS_FDW */
ForeignServerRelationId, /* OCLASS_FOREIGN_SERVER */
UserMappingRelationId, /* OCLASS_USER_MAPPING */
+ RoutineMappingRelationId, /* OCLASS_ROUTINE_MAPPING */
DefaultAclRelationId, /* OCLASS_DEFACL */
ExtensionRelationId, /* OCLASS_EXTENSION */
EventTriggerRelationId, /* OCLASS_EVENT_TRIGGER */
@@ -1252,6 +1254,10 @@ doDeletion(const ObjectAddress *object, int flags)
RemoveUserMappingById(object->objectId);
break;
+ case OCLASS_ROUTINE_MAPPING:
+ RemoveRoutineMappingById(object->objectId);
+ break;
+
case OCLASS_DEFACL:
RemoveDefaultACLById(object->objectId);
break;
@@ -2514,6 +2520,9 @@ getObjectClass(const ObjectAddress *object)
case UserMappingRelationId:
return OCLASS_USER_MAPPING;
+ case RoutineMappingRelationId:
+ return OCLASS_ROUTINE_MAPPING;
+
case DefaultAclRelationId:
return OCLASS_DEFACL;
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7db942d..2a40731 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -47,6 +47,7 @@
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
#include "catalog/pg_rewrite.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_subscription.h"
#include "catalog/pg_tablespace.h"
@@ -3339,6 +3340,25 @@ getObjectDescription(const ObjectAddress *object)
break;
}
+ case OCLASS_ROUTINE_MAPPING:
+ {
+ HeapTuple tup;
+ Oid funcid;
+ Form_pg_routine_mapping rmform;
+
+ tup = SearchSysCache1(ROUTINEMAPPINGOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for routine mapping %u",
+ object->objectId);
+ rmform = (Form_pg_routine_mapping) GETSTRUCT(tup);
+ funcid = rmform->rmproc;
+
+ ReleaseSysCache(tup);
+ appendStringInfoString(&buffer, NameStr(rmform->rmname));
+ break;
+ }
+
case OCLASS_DEFACL:
{
Relation defaclrel;
@@ -4038,6 +4058,10 @@ getObjectTypeDescription(const ObjectAddress *object)
appendStringInfoString(&buffer, "user mapping");
break;
+ case OCLASS_ROUTINE_MAPPING:
+ appendStringInfoString(&buffer, "routine mapping");
+ break;
+
case OCLASS_DEFACL:
appendStringInfoString(&buffer, "default acl");
break;
@@ -4897,6 +4921,27 @@ getObjectIdentityParts(const ObjectAddress *object,
break;
}
+ case OCLASS_ROUTINE_MAPPING:
+ {
+ HeapTuple tup;
+ Form_pg_routine_mapping rmform;
+
+ tup = SearchSysCache1(ROUTINEMAPPINGOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for routine mapping %u",
+ object->objectId);
+ rmform = (Form_pg_routine_mapping) GETSTRUCT(tup);
+
+ ReleaseSysCache(tup);
+
+ if (objname)
+ *objname = list_make1(pstrdup(NameStr(rmform->rmname)));
+
+ appendStringInfoString(&buffer, NameStr(rmform->rmname));
+ break;
+ }
+
case OCLASS_DEFACL:
{
Relation defaclrel;
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index eff325c..5352470 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -623,6 +623,7 @@ AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid,
case OCLASS_FDW:
case OCLASS_FOREIGN_SERVER:
case OCLASS_USER_MAPPING:
+ case OCLASS_ROUTINE_MAPPING:
case OCLASS_DEFACL:
case OCLASS_EXTENSION:
case OCLASS_EVENT_TRIGGER:
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index eecc85d..dd174cd 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -110,6 +110,7 @@ static event_trigger_support_data event_trigger_support[] = {
{"PUBLICATION", true},
{"ROLE", false},
{"ROUTINE", true},
+ {"ROUTINE MAPPING", true},
{"RULE", true},
{"SCHEMA", true},
{"SEQUENCE", true},
@@ -1202,6 +1203,7 @@ EventTriggerSupportsObjectClass(ObjectClass objclass)
case OCLASS_FDW:
case OCLASS_FOREIGN_SERVER:
case OCLASS_USER_MAPPING:
+ case OCLASS_ROUTINE_MAPPING:
case OCLASS_DEFACL:
case OCLASS_EXTENSION:
case OCLASS_POLICY:
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index e5dd995..e84f011 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -26,6 +26,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "catalog/pg_user_mapping.h"
+#include "catalog/pg_routine_mapping.h"
#include "commands/defrem.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
@@ -1662,3 +1663,218 @@ import_error_callback(void *arg)
errcontext("importing foreign table \"%s\"",
callback_arg->tablename);
}
+
+/*
+ * Create rouitne mapping
+ */
+ObjectAddress
+CreateRoutineMapping(CreateRoutineMappingStmt *stmt)
+{
+ Relation rel;
+ Datum options;
+ Datum values[Natts_pg_routine_mapping];
+ bool nulls[Natts_pg_routine_mapping];
+ Oid rmId;
+ Oid funcId;
+ NameData procname;
+ HeapTuple tuple;
+ ObjectAddress myself;
+ ObjectAddress referenced;
+ ForeignServer *srv;
+ ForeignDataWrapper *fdw;
+
+ rel = heap_open(RoutineMappingRelationId, RowExclusiveLock);
+
+ funcId = LookupFuncWithArgs(stmt->objtype, stmt->func, false);
+
+ /* @@@: acl check */
+
+ if (GetRoutineMappingByName(stmt->name, true) != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("routine mapping \"%s\" already exists",
+ stmt->name)));
+
+ srv = GetForeignServerByName(stmt->servername, false);
+ rmId = GetSysCacheOid2(ROUTINEMAPPINGPROCSERVER,
+ ObjectIdGetDatum(funcId),
+ ObjectIdGetDatum(srv->serverid));
+
+ if (OidIsValid(rmId))
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("rouitne mapping for \"%s\" already exists for server %s, skipping",
+ NameListToString(stmt->func->objname), stmt->servername)));
+ return InvalidObjectAddress;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("routine mapping for \"%s\" already exists for server %s",
+ NameListToString(stmt->func->objname), stmt->servername)));
+ }
+
+ fdw = GetForeignDataWrapper(srv->fdwid);
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(values));
+
+ namestrcpy(&procname, stmt->name);
+ values[Anum_pg_routine_mapping_rmname - 1] = NameGetDatum(&procname);
+ values[Anum_pg_routine_mapping_rmproc - 1] = ObjectIdGetDatum(funcId);
+ values[Anum_pg_routine_mapping_rmserver - 1] = ObjectIdGetDatum(srv->serverid);
+
+ options = transformGenericOptions(RoutineMappingRelationId,
+ PointerGetDatum(NULL),
+ stmt->options,
+ fdw->fdwvalidator);
+
+ if (PointerIsValid(DatumGetPointer(options)))
+ values[Anum_pg_routine_mapping_rmoptions - 1] = options;
+ else
+ nulls[Anum_pg_routine_mapping_rmoptions - 1] = true;
+
+ tuple = heap_form_tuple(rel->rd_att, values, nulls);
+
+ rmId = CatalogTupleInsert(rel, tuple);
+
+ /* Add dependency on the server and proc */
+ myself.classId = RoutineMappingRelationId;
+ myself.objectId = rmId;
+ myself.objectSubId = 0;
+
+ referenced.classId = ForeignServerRelationId;
+ referenced.objectId = srv->serverid;
+ referenced.objectSubId = 0;
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+ /* Post creation hook for new user mapping */
+ InvokeObjectPostCreateHook(RoutineMappingRelationId, rmId, 0);
+
+ heap_close(rel, RowExclusiveLock);
+
+ return myself;
+}
+
+Oid
+RemoveRoutineMapping(DropRoutineMappingStmt *stmt)
+{
+ RoutineMapping *rm;
+ ObjectAddress object;
+ Oid rmId;
+
+ rm = GetRoutineMappingByName(stmt->name, true);
+
+ if (!rm)
+ {
+ if (!stmt->missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("routine mapping \"%s\" does not exist",
+ stmt->name)));
+
+ ereport(NOTICE,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ (errmsg("routine mapping \"%s\" done not exist, skipping",
+ stmt->name))));
+ return InvalidOid;
+ }
+
+ rmId = rm->rmid;
+
+ object.classId = RoutineMappingRelationId;
+ object.objectId = rm->rmid;
+ object.objectSubId = 0;
+
+ performDeletion(&object, DROP_CASCADE, 0);
+
+ return rmId;
+}
+
+void
+RemoveRoutineMappingById(Oid rmId)
+{
+ HeapTuple tp;
+ Relation rel;
+
+ rel = heap_open(RoutineMappingRelationId, RowExclusiveLock);
+
+ tp = SearchSysCache1(ROUTINEMAPPINGOID, ObjectIdGetDatum(rmId));
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for routine mapping %u", rmId);
+
+ CatalogTupleDelete(rel, &tp->t_self);
+
+ ReleaseSysCache(tp);
+
+ heap_close(rel, RowExclusiveLock);
+}
+
+ObjectAddress
+AlterRoutineMapping(AlterRoutineMappingStmt *stmt)
+{
+ RoutineMapping *rm;
+ Relation rel;
+ Datum values[Natts_pg_routine_mapping];
+ bool nulls[Natts_pg_routine_mapping];
+ bool repl[Natts_pg_user_mapping];
+ HeapTuple tp;
+ ObjectAddress address;
+
+ rel = heap_open(RoutineMappingRelationId, RowExclusiveLock);
+
+ rm = GetRoutineMappingByName(stmt->name, false);
+
+ tp = SearchSysCacheCopy1(ROUTINEMAPPINGOID, ObjectIdGetDatum(rm->rmid));
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(repl, false, sizeof(repl));
+
+ if (stmt->options)
+ {
+ ForeignServer *server;
+ ForeignDataWrapper *fdw;
+ Datum datum;
+ bool isnull;
+
+ server = GetForeignServer(rm->serverid);
+ fdw = GetForeignDataWrapper(server->fdwid);
+
+ datum = SysCacheGetAttr(ROUTINEMAPPINGPROCSERVER,
+ tp,
+ Anum_pg_routine_mapping_rmoptions,
+ &isnull);
+
+ if (isnull)
+ datum = PointerGetDatum(NULL);
+
+ datum = transformGenericOptions(RoutineMappingRelationId,
+ datum,
+ stmt->options,
+ fdw->fdwvalidator);
+ if (PointerIsValid(DatumGetPointer(datum)))
+ values[Anum_pg_routine_mapping_rmoptions - 1] = datum;
+ else
+ nulls[Anum_pg_routine_mapping_rmoptions - 1] = true;
+
+ repl[Anum_pg_routine_mapping_rmoptions - 1] = true;
+ }
+
+ tp = heap_modify_tuple(tp, RelationGetDescr(rel),
+ values, nulls, repl);
+
+ CatalogTupleUpdate(rel, &tp->t_self, tp);
+
+ ObjectAddressSet(address, RoutineMappingRelationId, rm->rmid);
+
+ heap_freetuple(tp);
+
+ heap_close(rel, RowExclusiveLock);
+
+ return address;
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2a12d64..d6a76f6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9642,6 +9642,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
case OCLASS_FDW:
case OCLASS_FOREIGN_SERVER:
case OCLASS_USER_MAPPING:
+ case OCLASS_ROUTINE_MAPPING:
case OCLASS_DEFACL:
case OCLASS_EXTENSION:
case OCLASS_EVENT_TRIGGER:
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index eac78a5..0a435af 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -17,6 +17,7 @@
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_user_mapping.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
@@ -465,6 +466,66 @@ IsImportableForeignTable(const char *tablename,
return false; /* shouldn't get here */
}
+/*
+ * GetRoutineMapping - look up the routine mapping.
+ */
+RoutineMapping *
+GetRoutineMapping(Oid rmid)
+{
+ Form_pg_routine_mapping rmform;
+ RoutineMapping *rm;
+ HeapTuple tp;
+ Datum datum;
+ bool isnull;
+
+ tp = SearchSysCache1(ROUTINEMAPPINGOID, ObjectIdGetDatum(rmid));
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for routine mapping");
+
+ rmform = (Form_pg_routine_mapping) GETSTRUCT(tp);
+
+ rm = (RoutineMapping *) palloc(sizeof(RoutineMapping));
+ rm->rmid = rmid;
+ rm->rmname = pstrdup(NameStr(rmform->rmname));
+ rm->procid = rmform->rmproc;
+ rm->serverid = rmform->rmserver;
+
+ datum = SysCacheGetAttr(ROUTINEMAPPINGPROCSERVER,
+ tp,
+ Anum_pg_routine_mapping_rmoptions,
+ &isnull);
+
+ if (isnull)
+ rm->options = NIL;
+ else
+ rm->options = untransformRelOptions(datum);
+
+ ReleaseSysCache(tp);
+
+ return rm;
+}
+
+/*
+ * GetRoutineMappingByName - look up the routine map by name.
+ */
+RoutineMapping *
+GetRoutineMappingByName(const char *rmname, bool missing_ok)
+{
+ Oid rmid;
+
+ rmid = GetSysCacheOid1(ROUTINEMAPPINGNAME, CStringGetDatum(rmname));
+
+ if (!OidIsValid(rmid) && !missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("routine mapping \"%s\" does not exist", rmname)));
+
+ if (!OidIsValid(rmid))
+ return NULL;
+
+ return GetRoutineMapping(rmid);
+}
/*
* deflist_to_tuplestore - Helper function to convert DefElem list to
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4bd2223..0275a63 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -250,7 +250,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
AlterObjectDependsStmt AlterObjectSchemaStmt AlterOwnerStmt
AlterOperatorStmt AlterSeqStmt AlterSystemStmt AlterTableStmt
AlterTblSpcStmt AlterExtensionStmt AlterExtensionContentsStmt AlterForeignTableStmt
- AlterCompositeTypeStmt AlterUserMappingStmt
+ AlterCompositeTypeStmt AlterUserMappingStmt AlterRoutineMappingStmt
AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt
AlterDefaultPrivilegesStmt DefACLAction
AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt
@@ -260,10 +260,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt
CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
- CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
- CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
+ CreateUserStmt CreateUserMappingStmt CreateRoutineMappingStmt CreateRoleStmt
+ CreatePolicyStmt CreatedbStmt
+ DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt
- DropAssertStmt DropCastStmt DropRoleStmt
+ DropAssertStmt DropCastStmt DropRoleStmt DropRoutineMappingStmt
DropdbStmt DropTableSpaceStmt
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
@@ -846,6 +847,7 @@ stmt :
| AlterPublicationStmt
| AlterRoleSetStmt
| AlterRoleStmt
+ | AlterRoutineMappingStmt
| AlterSubscriptionStmt
| AlterTSConfigurationStmt
| AlterTSDictionaryStmt
@@ -886,6 +888,7 @@ stmt :
| CreateTransformStmt
| CreateTrigStmt
| CreateEventTrigStmt
+ | CreateRoutineMappingStmt
| CreateRoleStmt
| CreateUserStmt
| CreateUserMappingStmt
@@ -907,6 +910,7 @@ stmt :
| DropTableSpaceStmt
| DropTransformStmt
| DropRoleStmt
+ | DropRoutineMappingStmt
| DropUserMappingStmt
| DropdbStmt
| ExecuteStmt
@@ -5224,6 +5228,107 @@ AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name alter_generi
/*****************************************************************************
*
+ * QUERY:
+ * CREATE ROUTINE MAPPING [IF NOT EXISTS] name
+ * FOR [FUNCTION|PROCEDUER] <function_with_args>
+ * SERVER name [OPTIONS]
+ *
+ *****************************************************************************/
+
+CreateRoutineMappingStmt: CREATE ROUTINE MAPPING name
+ FOR FUNCTION function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $4;
+ n->objtype = OBJECT_FUNCTION;
+ n->func = $7;
+ n->servername = $9;
+ n->options = $10;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE ROUTINE MAPPING IF_P NOT EXISTS name
+ FOR FUNCTION function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $7;
+ n->objtype = OBJECT_FUNCTION;
+ n->func = $10;
+ n->servername = $12;
+ n->options = $13;
+ n->if_not_exists = true;
+ $$ = (Node *) n;
+ }
+ | CREATE ROUTINE MAPPING name
+ FOR PROCEDURE function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $4;
+ n->objtype = OBJECT_PROCEDURE;
+ n->func = $7;
+ n->servername = $9;
+ n->options = $10;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE ROUTINE MAPPING IF_P NOT EXISTS name
+ FOR PROCEDURE function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $7;
+ n->objtype = OBJECT_PROCEDURE;
+ n->func = $10;
+ n->servername = $12;
+ n->options = $13;
+ n->if_not_exists = true;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY:
+ * ALTER ROUTINE MAPPING name OPTION
+
+ *****************************************************************************/
+AlterRoutineMappingStmt: ALTER ROUTINE MAPPING name alter_generic_options
+ {
+ AlterRoutineMappingStmt *n = makeNode(AlterRoutineMappingStmt);
+ n->name = $4;
+ n->options = $5;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY:
+ * DROP ROUTINE MAPPING name
+
+ *****************************************************************************/
+
+DropRoutineMappingStmt: DROP ROUTINE MAPPING name
+ {
+ DropRoutineMappingStmt *n = makeNode(DropRoutineMappingStmt);
+ n->name = $4;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
+ | DROP ROUTINE MAPPING IF_P EXISTS name
+ {
+ DropRoutineMappingStmt *n = makeNode(DropRoutineMappingStmt);
+ n->name = $6;
+ n->missing_ok = true;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
* QUERIES:
* CREATE POLICY name ON table
* [AS { PERMISSIVE | RESTRICTIVE } ]
@@ -15140,7 +15245,6 @@ unreserved_keyword:
| LOCK_P
| LOCKED
| LOGGED
- | MAPPING
| MATCH
| MATERIALIZED
| MAXVALUE
@@ -15451,6 +15555,7 @@ reserved_keyword:
| LIMIT
| LOCALTIME
| LOCALTIMESTAMP
+ | MAPPING
| NOT
| NULL_P
| OFFSET
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index b5804f6..68a8a51 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -211,6 +211,9 @@ check_xact_readonly(Node *parsetree)
case T_CreateUserMappingStmt:
case T_AlterUserMappingStmt:
case T_DropUserMappingStmt:
+ case T_CreateRoutineMappingStmt:
+ case T_AlterRoutineMappingStmt:
+ case T_DropRoutineMappingStmt:
case T_AlterTableSpaceOptionsStmt:
case T_CreateForeignTableStmt:
case T_ImportForeignSchemaStmt:
@@ -1421,6 +1424,20 @@ ProcessUtilitySlow(ParseState *pstate,
commandCollected = true;
break;
+ case T_CreateRoutineMappingStmt:
+ address = CreateRoutineMapping((CreateRoutineMappingStmt *) parsetree);
+ break;
+
+ case T_AlterRoutineMappingStmt:
+ address = AlterRoutineMapping((AlterRoutineMappingStmt *) parsetree);
+ break;
+
+ case T_DropRoutineMappingStmt:
+ RemoveRoutineMapping((DropRoutineMappingStmt *) parsetree);
+ /* no commands stashed for DROP */
+ commandCollected = true;
+ break;
+
case T_CompositeTypeStmt: /* CREATE TYPE (composite) */
{
CompositeTypeStmt *stmt = (CompositeTypeStmt *) parsetree;
@@ -2250,6 +2267,18 @@ CreateCommandTag(Node *parsetree)
tag = "IMPORT FOREIGN SCHEMA";
break;
+ case T_CreateRoutineMappingStmt:
+ tag = "CREATE ROUTINE MAPPING";
+ break;
+
+ case T_AlterRoutineMappingStmt:
+ tag = "ALTER ROUTINE MAPPING";
+ break;
+
+ case T_DropRoutineMappingStmt:
+ tag = "DROP ROUTINE MAPPING";
+ break;
+
case T_DropStmt:
switch (((DropStmt *) parsetree)->removeType)
{
@@ -3006,6 +3035,9 @@ GetCommandLogLevel(Node *parsetree)
case T_CreateUserMappingStmt:
case T_AlterUserMappingStmt:
case T_DropUserMappingStmt:
+ case T_CreateRoutineMappingStmt:
+ case T_AlterRoutineMappingStmt:
+ case T_DropRoutineMappingStmt:
case T_ImportForeignSchemaStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index bba595a..a585101 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2838,8 +2838,7 @@ type_is_collatable(Oid typid)
* Currently this is only consulted for individual tables, not for inheritance
* trees, so we don't need an "inh" parameter.
*
- * Calling a hook at this point looks somewhat strange, but is required
- * because the optimizer calls this function without any other way for
+ * Calling a hook at this point looks somewhat strange, but is required * because the optimizer calls this function without any other way for
* plug-ins to control the result.
*/
int32
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 2b38178..355cffc 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -59,6 +59,7 @@
#include "catalog/pg_shdepend.h"
#include "catalog/pg_shdescription.h"
#include "catalog/pg_shseclabel.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_replication_origin.h"
#include "catalog/pg_statistic.h"
#include "catalog/pg_statistic_ext.h"
@@ -705,6 +706,39 @@ static const struct cachedesc cacheinfo[] = {
},
16
},
+ {RoutineMappingRelationId, /* ROUTINEMAPPINGOID */
+ RoutineMappingOidIndexId,
+ 1,
+ {
+ ObjectIdAttributeNumber,
+ 0,
+ 0,
+ 0
+ },
+ 2
+ },
+ {RoutineMappingRelationId, /* ROUTINEMAPPINGPROCSERVER */
+ RoutineMappingProcServerIndexId,
+ 2,
+ {
+ Anum_pg_routine_mapping_rmproc,
+ Anum_pg_routine_mapping_rmserver,
+ 0,
+ 0
+ },
+ 2
+ },
+ {RoutineMappingRelationId, /* ROUTINEMAPINGNAME */
+ RoutineMappingNameIndexId,
+ 1,
+ {
+ Anum_pg_routine_mapping_rmname,
+ 0,
+ 0,
+ 0
+ },
+ 2
+ },
{RewriteRelationId, /* RULERELNAME */
RewriteRelRulenameIndexId,
2,
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 46c271a..f027521 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -173,6 +173,7 @@ typedef enum ObjectClass
OCLASS_FDW, /* pg_foreign_data_wrapper */
OCLASS_FOREIGN_SERVER, /* pg_foreign_server */
OCLASS_USER_MAPPING, /* pg_user_mapping */
+ OCLASS_ROUTINE_MAPPING, /* pg_routine_mapping */
OCLASS_DEFACL, /* pg_default_acl */
OCLASS_EXTENSION, /* pg_extension */
OCLASS_EVENT_TRIGGER, /* pg_event_trigger */
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index 2491582..539e410 100644
--- a/src/include/catalog/indexing.h
+++ b/src/include/catalog/indexing.h
@@ -299,6 +299,13 @@ DECLARE_UNIQUE_INDEX(pg_user_mapping_oid_index, 174, on pg_user_mapping using bt
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
+DECLARE_UNIQUE_INDEX(pg_routine_mapping_oid_index, 6021, on pg_routine_mapping using btree(oid oid_ops));
+#define RoutineMappingOidIndexId 6021
+DECLARE_UNIQUE_INDEX(pg_routine_mapping_proc_server_index, 6022, on pg_routine_mapping using btree(rmproc oid_ops, rmserver oid_ops));
+#define RoutineMappingProcServerIndexId 6022
+DECLARE_UNIQUE_INDEX(pg_routine_mapping_name_index, 6023, on pg_routine_mapping using btree(rmname name_ops));
+#define RoutineMappingNameIndexId 6023
+
DECLARE_UNIQUE_INDEX(pg_foreign_table_relid_index, 3119, on pg_foreign_table using btree(ftrelid oid_ops));
#define ForeignTableRelidIndexId 3119
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 6b83723..3df9d49 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -150,6 +150,10 @@ extern Datum transformGenericOptions(Oid catalogId,
Datum oldOptions,
List *options,
Oid fdwvalidator);
+extern ObjectAddress CreateRoutineMapping(CreateRoutineMappingStmt *stmt);
+extern ObjectAddress AlterRoutineMapping(AlterRoutineMappingStmt *stmt);
+extern Oid RemoveRoutineMapping(DropRoutineMappingStmt *stmt);
+extern void RemoveRoutineMappingById(Oid rmId);
/* commands/amcmds.c */
extern ObjectAddress CreateAccessMethod(CreateAmStmt *stmt);
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index 3ca12e6..05393dd 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -68,6 +68,14 @@ typedef struct ForeignTable
List *options; /* ftoptions as DefElem list */
} ForeignTable;
+typedef struct RoutineMapping
+{
+ Oid rmid; /* Oid of routine mapping */
+ char *rmname;
+ Oid procid; /* local function oid */
+ Oid serverid; /* server oid */
+ List *options; /* rmoptions as DefElem list */
+} RoutineMapping;
extern ForeignServer *GetForeignServer(Oid serverid);
extern ForeignServer *GetForeignServerByName(const char *name, bool missing_ok);
@@ -76,6 +84,8 @@ extern ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
bool missing_ok);
extern ForeignTable *GetForeignTable(Oid relid);
+extern RoutineMapping *GetRoutineMapping(Oid rmid);
+extern RoutineMapping *GetRoutineMappingByName(const char *rmname, bool missing_ok);
extern List *GetForeignColumnOptions(Oid relid, AttrNumber attnum);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 697d3d7..70aff71 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -419,6 +419,9 @@ typedef enum NodeTag
T_CreateStatsStmt,
T_AlterCollationStmt,
T_CallStmt,
+ T_CreateRoutineMappingStmt,
+ T_AlterRoutineMappingStmt,
+ T_DropRoutineMappingStmt,
/*
* TAGS FOR PARSE TREE NODES (parsenodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 07ab1a3..98f761f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2316,6 +2316,35 @@ typedef struct ImportForeignSchemaStmt
List *options; /* list of options to pass to FDW */
} ImportForeignSchemaStmt;
+/* ----------------------
+ * Create/Drop ROUTINE MAPPING Statements
+ * ----------------------
+ */
+typedef struct CreateRoutineMappingStmt
+{
+ NodeTag type;
+ char *name; /* routine mapping name */
+ ObjectType objtype;
+ ObjectWithArgs *func; /* name and args of function */
+ char *servername; /* server name */
+ List *options; /* generic option to server */
+ bool if_not_exists;
+} CreateRoutineMappingStmt;
+
+typedef struct AlterRoutineMappingStmt
+{
+ NodeTag type;
+ char *name;
+ List *options;
+} AlterRoutineMappingStmt;
+
+typedef struct DropRoutineMappingStmt
+{
+ NodeTag type;
+ char *name;
+ bool missing_ok;
+} DropRoutineMappingStmt;
+
/*----------------------
* Create POLICY Statement
*----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 23db401..52752d7 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -243,7 +243,7 @@ PG_KEYWORD("location", LOCATION, UNRESERVED_KEYWORD)
PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD)
PG_KEYWORD("locked", LOCKED, UNRESERVED_KEYWORD)
PG_KEYWORD("logged", LOGGED, UNRESERVED_KEYWORD)
-PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD)
+PG_KEYWORD("mapping", MAPPING, RESERVED_KEYWORD)
PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD)
PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD)
PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD)
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 4f33358..96282ea 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -84,6 +84,9 @@ enum SysCacheIdentifier
RELOID,
REPLORIGIDENT,
REPLORIGNAME,
+ ROUTINEMAPPINGOID,
+ ROUTINEMAPPINGPROCSERVER,
+ ROUTINEMAPPINGNAME,
RULERELNAME,
SEQRELID,
STATEXTNAMENSP,
--
2.10.5
0002-Poc-postgres_fdw-support-routine-mappings.patchapplication/octet-stream; name=0002-Poc-postgres_fdw-support-routine-mappings.patchDownload
From 7132313f42c71b75405e34cdac3db6126229b549 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 31 Aug 2018 16:55:16 +0900
Subject: [PATCH 2/2] Poc: postgres_fdw support routine mappings.
---
contrib/postgres_fdw/deparse.c | 68 +++++++++++++++++++++++++++++++++---------
contrib/postgres_fdw/option.c | 4 +++
2 files changed, 58 insertions(+), 14 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 6001f4d..d5a673a 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -185,6 +185,8 @@ static void appendAggOrderBy(List *orderList, List *targetList,
static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
static Node *deparseSortGroupClause(Index ref, List *tlist, bool force_colno,
deparse_expr_cxt *context);
+static void ExtractRoutineMappingOptions(List *defelems, char **remote_func_schema,
+ char **remote_func_name);
/*
* Helper functions
@@ -3177,30 +3179,51 @@ appendOrderByClause(List *pathkeys, deparse_expr_cxt *context)
static void
appendFunctionName(Oid funcid, deparse_expr_cxt *context)
{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
+ ForeignServer *server = fpinfo->server;
StringInfo buf = context->buf;
- HeapTuple proctup;
+ HeapTuple proctup = NULL;
Form_pg_proc procform;
- const char *proname;
+ char *proname = NULL;
+ char *schemaname = NULL;
+ Oid rmId;
- proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
- if (!HeapTupleIsValid(proctup))
- elog(ERROR, "cache lookup failed for function %u", funcid);
- procform = (Form_pg_proc) GETSTRUCT(proctup);
+ rmId = GetSysCacheOid2(ROUTINEMAPPINGPROCSERVER,
+ ObjectIdGetDatum(funcid),
+ ObjectIdGetDatum(server->serverid));
- /* Print schema name only if it's not pg_catalog */
- if (procform->pronamespace != PG_CATALOG_NAMESPACE)
+ /* This function is mapped, get remote schema and function name */
+ if (OidIsValid(rmId))
{
- const char *schemaname;
+ RoutineMapping *rm;
- schemaname = get_namespace_name(procform->pronamespace);
- appendStringInfo(buf, "%s.", quote_identifier(schemaname));
+ rm = GetRoutineMapping(rmId);
+ ExtractRoutineMappingOptions(rm->options, &schemaname, &proname);
}
+ if (!schemaname || !proname)
+ {
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ /* Print schema name only if it's not pg_catalog */
+ if (!schemaname && procform->pronamespace != PG_CATALOG_NAMESPACE)
+ schemaname = get_namespace_name(procform->pronamespace);
+
+ if (!proname)
+ proname = NameStr(procform->proname);
+ }
+
+ if (schemaname)
+ appendStringInfo(buf, "%s.", quote_identifier(schemaname));
+
/* Always print the function name */
- proname = NameStr(procform->proname);
- appendStringInfoString(buf, quote_identifier(proname));
+ appendStringInfo(buf, "%s", quote_identifier(proname));
- ReleaseSysCache(proctup);
+ if (HeapTupleIsValid(proctup))
+ ReleaseSysCache(proctup);
}
/*
@@ -3342,3 +3365,20 @@ get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
/* Shouldn't get here */
elog(ERROR, "unexpected expression in subquery output");
}
+
+static void
+ExtractRoutineMappingOptions(List *defelems, char **remote_func_schema,
+ char **remote_func_name)
+{
+ ListCell *lc;
+
+ foreach(lc, defelems)
+ {
+ DefElem *d = (DefElem *) lfirst(lc);
+
+ if (strcmp(d->defname, "remote_func_schema") == 0)
+ *remote_func_schema = defGetString(d);
+ else if (strcmp(d->defname, "remote_func_name") == 0)
+ *remote_func_name = defGetString(d);
+ }
+}
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 6854f1b..2d5e21c 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,7 @@
#include "access/reloptions.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
#include "commands/extension.h"
@@ -177,6 +178,9 @@ InitPgFdwOptions(void)
/* fetch_size is available on both server and table */
{"fetch_size", ForeignServerRelationId, false},
{"fetch_size", ForeignTableRelationId, false},
+ /* Routine mapping */
+ {"remote_func_name", RoutineMappingRelationId, false},
+ {"remote_func_schema", RoutineMappingRelationId, false},
{NULL, InvalidOid, false}
};
--
2.10.5
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
On Tue, Sep 4, 2018 at 5:48 AM, David Fetter <david@fetter.org> wrote:
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 2Was there a file missing from the patch?
Oops, sorry I missed the header file. Attached the PoC patches again.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Attachments:
0002-Poc-postgres_fdw-support-routine-mappings.patchapplication/octet-stream; name=0002-Poc-postgres_fdw-support-routine-mappings.patchDownload
From b916c06031e5d1783a6d29c6bd95ff0498aea4f1 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 31 Aug 2018 16:55:16 +0900
Subject: [PATCH 2/2] Poc: postgres_fdw support routine mappings.
---
contrib/postgres_fdw/deparse.c | 68 +++++++++++++++++++++++++++++++++---------
contrib/postgres_fdw/option.c | 4 +++
2 files changed, 58 insertions(+), 14 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 6001f4d..d5a673a 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -185,6 +185,8 @@ static void appendAggOrderBy(List *orderList, List *targetList,
static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
static Node *deparseSortGroupClause(Index ref, List *tlist, bool force_colno,
deparse_expr_cxt *context);
+static void ExtractRoutineMappingOptions(List *defelems, char **remote_func_schema,
+ char **remote_func_name);
/*
* Helper functions
@@ -3177,30 +3179,51 @@ appendOrderByClause(List *pathkeys, deparse_expr_cxt *context)
static void
appendFunctionName(Oid funcid, deparse_expr_cxt *context)
{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
+ ForeignServer *server = fpinfo->server;
StringInfo buf = context->buf;
- HeapTuple proctup;
+ HeapTuple proctup = NULL;
Form_pg_proc procform;
- const char *proname;
+ char *proname = NULL;
+ char *schemaname = NULL;
+ Oid rmId;
- proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
- if (!HeapTupleIsValid(proctup))
- elog(ERROR, "cache lookup failed for function %u", funcid);
- procform = (Form_pg_proc) GETSTRUCT(proctup);
+ rmId = GetSysCacheOid2(ROUTINEMAPPINGPROCSERVER,
+ ObjectIdGetDatum(funcid),
+ ObjectIdGetDatum(server->serverid));
- /* Print schema name only if it's not pg_catalog */
- if (procform->pronamespace != PG_CATALOG_NAMESPACE)
+ /* This function is mapped, get remote schema and function name */
+ if (OidIsValid(rmId))
{
- const char *schemaname;
+ RoutineMapping *rm;
- schemaname = get_namespace_name(procform->pronamespace);
- appendStringInfo(buf, "%s.", quote_identifier(schemaname));
+ rm = GetRoutineMapping(rmId);
+ ExtractRoutineMappingOptions(rm->options, &schemaname, &proname);
}
+ if (!schemaname || !proname)
+ {
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ /* Print schema name only if it's not pg_catalog */
+ if (!schemaname && procform->pronamespace != PG_CATALOG_NAMESPACE)
+ schemaname = get_namespace_name(procform->pronamespace);
+
+ if (!proname)
+ proname = NameStr(procform->proname);
+ }
+
+ if (schemaname)
+ appendStringInfo(buf, "%s.", quote_identifier(schemaname));
+
/* Always print the function name */
- proname = NameStr(procform->proname);
- appendStringInfoString(buf, quote_identifier(proname));
+ appendStringInfo(buf, "%s", quote_identifier(proname));
- ReleaseSysCache(proctup);
+ if (HeapTupleIsValid(proctup))
+ ReleaseSysCache(proctup);
}
/*
@@ -3342,3 +3365,20 @@ get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
/* Shouldn't get here */
elog(ERROR, "unexpected expression in subquery output");
}
+
+static void
+ExtractRoutineMappingOptions(List *defelems, char **remote_func_schema,
+ char **remote_func_name)
+{
+ ListCell *lc;
+
+ foreach(lc, defelems)
+ {
+ DefElem *d = (DefElem *) lfirst(lc);
+
+ if (strcmp(d->defname, "remote_func_schema") == 0)
+ *remote_func_schema = defGetString(d);
+ else if (strcmp(d->defname, "remote_func_name") == 0)
+ *remote_func_name = defGetString(d);
+ }
+}
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 6854f1b..2d5e21c 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,7 @@
#include "access/reloptions.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
#include "commands/extension.h"
@@ -177,6 +178,9 @@ InitPgFdwOptions(void)
/* fetch_size is available on both server and table */
{"fetch_size", ForeignServerRelationId, false},
{"fetch_size", ForeignTableRelationId, false},
+ /* Routine mapping */
+ {"remote_func_name", RoutineMappingRelationId, false},
+ {"remote_func_schema", RoutineMappingRelationId, false},
{NULL, InvalidOid, false}
};
--
2.10.5
0001-PoC-Support-ROUTINE-MAPPING.patchapplication/octet-stream; name=0001-PoC-Support-ROUTINE-MAPPING.patchDownload
From b32355c36314ffc801b458b2d672f0d84f230fa5 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 31 Aug 2018 16:54:53 +0900
Subject: [PATCH 1/2] PoC: Support ROUTINE MAPPING.
---
src/backend/catalog/Makefile | 2 +-
src/backend/catalog/dependency.c | 9 ++
src/backend/catalog/objectaddress.c | 45 +++++++
src/backend/commands/alter.c | 1 +
src/backend/commands/event_trigger.c | 2 +
src/backend/commands/foreigncmds.c | 216 +++++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 1 +
src/backend/foreign/foreign.c | 61 +++++++++
src/backend/parser/gram.y | 115 +++++++++++++++-
src/backend/tcop/utility.c | 32 +++++
src/backend/utils/cache/lsyscache.c | 3 +-
src/backend/utils/cache/syscache.c | 34 +++++
src/include/catalog/dependency.h | 1 +
src/include/catalog/indexing.h | 7 +
src/include/catalog/pg_routine_mapping.h | 45 +++++++
src/include/commands/defrem.h | 4 +
src/include/foreign/foreign.h | 10 ++
src/include/nodes/nodes.h | 3 +
src/include/nodes/parsenodes.h | 29 +++++
src/include/parser/kwlist.h | 2 +-
src/include/utils/syscache.h | 3 +
21 files changed, 616 insertions(+), 9 deletions(-)
create mode 100644 src/include/catalog/pg_routine_mapping.h
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 0865240..0ba23e5 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -42,7 +42,7 @@ CATALOG_HEADERS := \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h pg_extension.h \
pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
- pg_foreign_table.h pg_policy.h pg_replication_origin.h \
+ pg_routine_mapping.h pg_foreign_table.h pg_policy.h pg_replication_origin.h \
pg_default_acl.h pg_init_privs.h pg_seclabel.h pg_shseclabel.h \
pg_collation.h pg_partitioned_table.h pg_range.h pg_transform.h \
pg_sequence.h pg_publication.h pg_publication_rel.h pg_subscription.h \
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 4f1d365..046c839 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -48,6 +48,7 @@
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
#include "catalog/pg_rewrite.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_subscription.h"
#include "catalog/pg_tablespace.h"
@@ -163,6 +164,7 @@ static const Oid object_classes[] = {
ForeignDataWrapperRelationId, /* OCLASS_FDW */
ForeignServerRelationId, /* OCLASS_FOREIGN_SERVER */
UserMappingRelationId, /* OCLASS_USER_MAPPING */
+ RoutineMappingRelationId, /* OCLASS_ROUTINE_MAPPING */
DefaultAclRelationId, /* OCLASS_DEFACL */
ExtensionRelationId, /* OCLASS_EXTENSION */
EventTriggerRelationId, /* OCLASS_EVENT_TRIGGER */
@@ -1252,6 +1254,10 @@ doDeletion(const ObjectAddress *object, int flags)
RemoveUserMappingById(object->objectId);
break;
+ case OCLASS_ROUTINE_MAPPING:
+ RemoveRoutineMappingById(object->objectId);
+ break;
+
case OCLASS_DEFACL:
RemoveDefaultACLById(object->objectId);
break;
@@ -2514,6 +2520,9 @@ getObjectClass(const ObjectAddress *object)
case UserMappingRelationId:
return OCLASS_USER_MAPPING;
+ case RoutineMappingRelationId:
+ return OCLASS_ROUTINE_MAPPING;
+
case DefaultAclRelationId:
return OCLASS_DEFACL;
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7db942d..2a40731 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -47,6 +47,7 @@
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
#include "catalog/pg_rewrite.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_subscription.h"
#include "catalog/pg_tablespace.h"
@@ -3339,6 +3340,25 @@ getObjectDescription(const ObjectAddress *object)
break;
}
+ case OCLASS_ROUTINE_MAPPING:
+ {
+ HeapTuple tup;
+ Oid funcid;
+ Form_pg_routine_mapping rmform;
+
+ tup = SearchSysCache1(ROUTINEMAPPINGOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for routine mapping %u",
+ object->objectId);
+ rmform = (Form_pg_routine_mapping) GETSTRUCT(tup);
+ funcid = rmform->rmproc;
+
+ ReleaseSysCache(tup);
+ appendStringInfoString(&buffer, NameStr(rmform->rmname));
+ break;
+ }
+
case OCLASS_DEFACL:
{
Relation defaclrel;
@@ -4038,6 +4058,10 @@ getObjectTypeDescription(const ObjectAddress *object)
appendStringInfoString(&buffer, "user mapping");
break;
+ case OCLASS_ROUTINE_MAPPING:
+ appendStringInfoString(&buffer, "routine mapping");
+ break;
+
case OCLASS_DEFACL:
appendStringInfoString(&buffer, "default acl");
break;
@@ -4897,6 +4921,27 @@ getObjectIdentityParts(const ObjectAddress *object,
break;
}
+ case OCLASS_ROUTINE_MAPPING:
+ {
+ HeapTuple tup;
+ Form_pg_routine_mapping rmform;
+
+ tup = SearchSysCache1(ROUTINEMAPPINGOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for routine mapping %u",
+ object->objectId);
+ rmform = (Form_pg_routine_mapping) GETSTRUCT(tup);
+
+ ReleaseSysCache(tup);
+
+ if (objname)
+ *objname = list_make1(pstrdup(NameStr(rmform->rmname)));
+
+ appendStringInfoString(&buffer, NameStr(rmform->rmname));
+ break;
+ }
+
case OCLASS_DEFACL:
{
Relation defaclrel;
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index eff325c..5352470 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -623,6 +623,7 @@ AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid,
case OCLASS_FDW:
case OCLASS_FOREIGN_SERVER:
case OCLASS_USER_MAPPING:
+ case OCLASS_ROUTINE_MAPPING:
case OCLASS_DEFACL:
case OCLASS_EXTENSION:
case OCLASS_EVENT_TRIGGER:
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index eecc85d..dd174cd 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -110,6 +110,7 @@ static event_trigger_support_data event_trigger_support[] = {
{"PUBLICATION", true},
{"ROLE", false},
{"ROUTINE", true},
+ {"ROUTINE MAPPING", true},
{"RULE", true},
{"SCHEMA", true},
{"SEQUENCE", true},
@@ -1202,6 +1203,7 @@ EventTriggerSupportsObjectClass(ObjectClass objclass)
case OCLASS_FDW:
case OCLASS_FOREIGN_SERVER:
case OCLASS_USER_MAPPING:
+ case OCLASS_ROUTINE_MAPPING:
case OCLASS_DEFACL:
case OCLASS_EXTENSION:
case OCLASS_POLICY:
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index e5dd995..e84f011 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -26,6 +26,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "catalog/pg_user_mapping.h"
+#include "catalog/pg_routine_mapping.h"
#include "commands/defrem.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
@@ -1662,3 +1663,218 @@ import_error_callback(void *arg)
errcontext("importing foreign table \"%s\"",
callback_arg->tablename);
}
+
+/*
+ * Create rouitne mapping
+ */
+ObjectAddress
+CreateRoutineMapping(CreateRoutineMappingStmt *stmt)
+{
+ Relation rel;
+ Datum options;
+ Datum values[Natts_pg_routine_mapping];
+ bool nulls[Natts_pg_routine_mapping];
+ Oid rmId;
+ Oid funcId;
+ NameData procname;
+ HeapTuple tuple;
+ ObjectAddress myself;
+ ObjectAddress referenced;
+ ForeignServer *srv;
+ ForeignDataWrapper *fdw;
+
+ rel = heap_open(RoutineMappingRelationId, RowExclusiveLock);
+
+ funcId = LookupFuncWithArgs(stmt->objtype, stmt->func, false);
+
+ /* @@@: acl check */
+
+ if (GetRoutineMappingByName(stmt->name, true) != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("routine mapping \"%s\" already exists",
+ stmt->name)));
+
+ srv = GetForeignServerByName(stmt->servername, false);
+ rmId = GetSysCacheOid2(ROUTINEMAPPINGPROCSERVER,
+ ObjectIdGetDatum(funcId),
+ ObjectIdGetDatum(srv->serverid));
+
+ if (OidIsValid(rmId))
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("rouitne mapping for \"%s\" already exists for server %s, skipping",
+ NameListToString(stmt->func->objname), stmt->servername)));
+ return InvalidObjectAddress;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("routine mapping for \"%s\" already exists for server %s",
+ NameListToString(stmt->func->objname), stmt->servername)));
+ }
+
+ fdw = GetForeignDataWrapper(srv->fdwid);
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(values));
+
+ namestrcpy(&procname, stmt->name);
+ values[Anum_pg_routine_mapping_rmname - 1] = NameGetDatum(&procname);
+ values[Anum_pg_routine_mapping_rmproc - 1] = ObjectIdGetDatum(funcId);
+ values[Anum_pg_routine_mapping_rmserver - 1] = ObjectIdGetDatum(srv->serverid);
+
+ options = transformGenericOptions(RoutineMappingRelationId,
+ PointerGetDatum(NULL),
+ stmt->options,
+ fdw->fdwvalidator);
+
+ if (PointerIsValid(DatumGetPointer(options)))
+ values[Anum_pg_routine_mapping_rmoptions - 1] = options;
+ else
+ nulls[Anum_pg_routine_mapping_rmoptions - 1] = true;
+
+ tuple = heap_form_tuple(rel->rd_att, values, nulls);
+
+ rmId = CatalogTupleInsert(rel, tuple);
+
+ /* Add dependency on the server and proc */
+ myself.classId = RoutineMappingRelationId;
+ myself.objectId = rmId;
+ myself.objectSubId = 0;
+
+ referenced.classId = ForeignServerRelationId;
+ referenced.objectId = srv->serverid;
+ referenced.objectSubId = 0;
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+ /* Post creation hook for new user mapping */
+ InvokeObjectPostCreateHook(RoutineMappingRelationId, rmId, 0);
+
+ heap_close(rel, RowExclusiveLock);
+
+ return myself;
+}
+
+Oid
+RemoveRoutineMapping(DropRoutineMappingStmt *stmt)
+{
+ RoutineMapping *rm;
+ ObjectAddress object;
+ Oid rmId;
+
+ rm = GetRoutineMappingByName(stmt->name, true);
+
+ if (!rm)
+ {
+ if (!stmt->missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("routine mapping \"%s\" does not exist",
+ stmt->name)));
+
+ ereport(NOTICE,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ (errmsg("routine mapping \"%s\" done not exist, skipping",
+ stmt->name))));
+ return InvalidOid;
+ }
+
+ rmId = rm->rmid;
+
+ object.classId = RoutineMappingRelationId;
+ object.objectId = rm->rmid;
+ object.objectSubId = 0;
+
+ performDeletion(&object, DROP_CASCADE, 0);
+
+ return rmId;
+}
+
+void
+RemoveRoutineMappingById(Oid rmId)
+{
+ HeapTuple tp;
+ Relation rel;
+
+ rel = heap_open(RoutineMappingRelationId, RowExclusiveLock);
+
+ tp = SearchSysCache1(ROUTINEMAPPINGOID, ObjectIdGetDatum(rmId));
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for routine mapping %u", rmId);
+
+ CatalogTupleDelete(rel, &tp->t_self);
+
+ ReleaseSysCache(tp);
+
+ heap_close(rel, RowExclusiveLock);
+}
+
+ObjectAddress
+AlterRoutineMapping(AlterRoutineMappingStmt *stmt)
+{
+ RoutineMapping *rm;
+ Relation rel;
+ Datum values[Natts_pg_routine_mapping];
+ bool nulls[Natts_pg_routine_mapping];
+ bool repl[Natts_pg_user_mapping];
+ HeapTuple tp;
+ ObjectAddress address;
+
+ rel = heap_open(RoutineMappingRelationId, RowExclusiveLock);
+
+ rm = GetRoutineMappingByName(stmt->name, false);
+
+ tp = SearchSysCacheCopy1(ROUTINEMAPPINGOID, ObjectIdGetDatum(rm->rmid));
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(repl, false, sizeof(repl));
+
+ if (stmt->options)
+ {
+ ForeignServer *server;
+ ForeignDataWrapper *fdw;
+ Datum datum;
+ bool isnull;
+
+ server = GetForeignServer(rm->serverid);
+ fdw = GetForeignDataWrapper(server->fdwid);
+
+ datum = SysCacheGetAttr(ROUTINEMAPPINGPROCSERVER,
+ tp,
+ Anum_pg_routine_mapping_rmoptions,
+ &isnull);
+
+ if (isnull)
+ datum = PointerGetDatum(NULL);
+
+ datum = transformGenericOptions(RoutineMappingRelationId,
+ datum,
+ stmt->options,
+ fdw->fdwvalidator);
+ if (PointerIsValid(DatumGetPointer(datum)))
+ values[Anum_pg_routine_mapping_rmoptions - 1] = datum;
+ else
+ nulls[Anum_pg_routine_mapping_rmoptions - 1] = true;
+
+ repl[Anum_pg_routine_mapping_rmoptions - 1] = true;
+ }
+
+ tp = heap_modify_tuple(tp, RelationGetDescr(rel),
+ values, nulls, repl);
+
+ CatalogTupleUpdate(rel, &tp->t_self, tp);
+
+ ObjectAddressSet(address, RoutineMappingRelationId, rm->rmid);
+
+ heap_freetuple(tp);
+
+ heap_close(rel, RowExclusiveLock);
+
+ return address;
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f46af41..687d67d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9648,6 +9648,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
case OCLASS_FDW:
case OCLASS_FOREIGN_SERVER:
case OCLASS_USER_MAPPING:
+ case OCLASS_ROUTINE_MAPPING:
case OCLASS_DEFACL:
case OCLASS_EXTENSION:
case OCLASS_EVENT_TRIGGER:
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index eac78a5..0a435af 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -17,6 +17,7 @@
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_user_mapping.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
@@ -465,6 +466,66 @@ IsImportableForeignTable(const char *tablename,
return false; /* shouldn't get here */
}
+/*
+ * GetRoutineMapping - look up the routine mapping.
+ */
+RoutineMapping *
+GetRoutineMapping(Oid rmid)
+{
+ Form_pg_routine_mapping rmform;
+ RoutineMapping *rm;
+ HeapTuple tp;
+ Datum datum;
+ bool isnull;
+
+ tp = SearchSysCache1(ROUTINEMAPPINGOID, ObjectIdGetDatum(rmid));
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for routine mapping");
+
+ rmform = (Form_pg_routine_mapping) GETSTRUCT(tp);
+
+ rm = (RoutineMapping *) palloc(sizeof(RoutineMapping));
+ rm->rmid = rmid;
+ rm->rmname = pstrdup(NameStr(rmform->rmname));
+ rm->procid = rmform->rmproc;
+ rm->serverid = rmform->rmserver;
+
+ datum = SysCacheGetAttr(ROUTINEMAPPINGPROCSERVER,
+ tp,
+ Anum_pg_routine_mapping_rmoptions,
+ &isnull);
+
+ if (isnull)
+ rm->options = NIL;
+ else
+ rm->options = untransformRelOptions(datum);
+
+ ReleaseSysCache(tp);
+
+ return rm;
+}
+
+/*
+ * GetRoutineMappingByName - look up the routine map by name.
+ */
+RoutineMapping *
+GetRoutineMappingByName(const char *rmname, bool missing_ok)
+{
+ Oid rmid;
+
+ rmid = GetSysCacheOid1(ROUTINEMAPPINGNAME, CStringGetDatum(rmname));
+
+ if (!OidIsValid(rmid) && !missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("routine mapping \"%s\" does not exist", rmname)));
+
+ if (!OidIsValid(rmid))
+ return NULL;
+
+ return GetRoutineMapping(rmid);
+}
/*
* deflist_to_tuplestore - Helper function to convert DefElem list to
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4bd2223..0275a63 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -250,7 +250,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
AlterObjectDependsStmt AlterObjectSchemaStmt AlterOwnerStmt
AlterOperatorStmt AlterSeqStmt AlterSystemStmt AlterTableStmt
AlterTblSpcStmt AlterExtensionStmt AlterExtensionContentsStmt AlterForeignTableStmt
- AlterCompositeTypeStmt AlterUserMappingStmt
+ AlterCompositeTypeStmt AlterUserMappingStmt AlterRoutineMappingStmt
AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt
AlterDefaultPrivilegesStmt DefACLAction
AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt
@@ -260,10 +260,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt
CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
- CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
- CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
+ CreateUserStmt CreateUserMappingStmt CreateRoutineMappingStmt CreateRoleStmt
+ CreatePolicyStmt CreatedbStmt
+ DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt
- DropAssertStmt DropCastStmt DropRoleStmt
+ DropAssertStmt DropCastStmt DropRoleStmt DropRoutineMappingStmt
DropdbStmt DropTableSpaceStmt
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
@@ -846,6 +847,7 @@ stmt :
| AlterPublicationStmt
| AlterRoleSetStmt
| AlterRoleStmt
+ | AlterRoutineMappingStmt
| AlterSubscriptionStmt
| AlterTSConfigurationStmt
| AlterTSDictionaryStmt
@@ -886,6 +888,7 @@ stmt :
| CreateTransformStmt
| CreateTrigStmt
| CreateEventTrigStmt
+ | CreateRoutineMappingStmt
| CreateRoleStmt
| CreateUserStmt
| CreateUserMappingStmt
@@ -907,6 +910,7 @@ stmt :
| DropTableSpaceStmt
| DropTransformStmt
| DropRoleStmt
+ | DropRoutineMappingStmt
| DropUserMappingStmt
| DropdbStmt
| ExecuteStmt
@@ -5224,6 +5228,107 @@ AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name alter_generi
/*****************************************************************************
*
+ * QUERY:
+ * CREATE ROUTINE MAPPING [IF NOT EXISTS] name
+ * FOR [FUNCTION|PROCEDUER] <function_with_args>
+ * SERVER name [OPTIONS]
+ *
+ *****************************************************************************/
+
+CreateRoutineMappingStmt: CREATE ROUTINE MAPPING name
+ FOR FUNCTION function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $4;
+ n->objtype = OBJECT_FUNCTION;
+ n->func = $7;
+ n->servername = $9;
+ n->options = $10;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE ROUTINE MAPPING IF_P NOT EXISTS name
+ FOR FUNCTION function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $7;
+ n->objtype = OBJECT_FUNCTION;
+ n->func = $10;
+ n->servername = $12;
+ n->options = $13;
+ n->if_not_exists = true;
+ $$ = (Node *) n;
+ }
+ | CREATE ROUTINE MAPPING name
+ FOR PROCEDURE function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $4;
+ n->objtype = OBJECT_PROCEDURE;
+ n->func = $7;
+ n->servername = $9;
+ n->options = $10;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE ROUTINE MAPPING IF_P NOT EXISTS name
+ FOR PROCEDURE function_with_argtypes
+ SERVER name create_generic_options
+ {
+ CreateRoutineMappingStmt *n = makeNode(CreateRoutineMappingStmt);
+ n->name = $7;
+ n->objtype = OBJECT_PROCEDURE;
+ n->func = $10;
+ n->servername = $12;
+ n->options = $13;
+ n->if_not_exists = true;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY:
+ * ALTER ROUTINE MAPPING name OPTION
+
+ *****************************************************************************/
+AlterRoutineMappingStmt: ALTER ROUTINE MAPPING name alter_generic_options
+ {
+ AlterRoutineMappingStmt *n = makeNode(AlterRoutineMappingStmt);
+ n->name = $4;
+ n->options = $5;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY:
+ * DROP ROUTINE MAPPING name
+
+ *****************************************************************************/
+
+DropRoutineMappingStmt: DROP ROUTINE MAPPING name
+ {
+ DropRoutineMappingStmt *n = makeNode(DropRoutineMappingStmt);
+ n->name = $4;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
+ | DROP ROUTINE MAPPING IF_P EXISTS name
+ {
+ DropRoutineMappingStmt *n = makeNode(DropRoutineMappingStmt);
+ n->name = $6;
+ n->missing_ok = true;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
* QUERIES:
* CREATE POLICY name ON table
* [AS { PERMISSIVE | RESTRICTIVE } ]
@@ -15140,7 +15245,6 @@ unreserved_keyword:
| LOCK_P
| LOCKED
| LOGGED
- | MAPPING
| MATCH
| MATERIALIZED
| MAXVALUE
@@ -15451,6 +15555,7 @@ reserved_keyword:
| LIMIT
| LOCALTIME
| LOCALTIMESTAMP
+ | MAPPING
| NOT
| NULL_P
| OFFSET
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index b5804f6..68a8a51 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -211,6 +211,9 @@ check_xact_readonly(Node *parsetree)
case T_CreateUserMappingStmt:
case T_AlterUserMappingStmt:
case T_DropUserMappingStmt:
+ case T_CreateRoutineMappingStmt:
+ case T_AlterRoutineMappingStmt:
+ case T_DropRoutineMappingStmt:
case T_AlterTableSpaceOptionsStmt:
case T_CreateForeignTableStmt:
case T_ImportForeignSchemaStmt:
@@ -1421,6 +1424,20 @@ ProcessUtilitySlow(ParseState *pstate,
commandCollected = true;
break;
+ case T_CreateRoutineMappingStmt:
+ address = CreateRoutineMapping((CreateRoutineMappingStmt *) parsetree);
+ break;
+
+ case T_AlterRoutineMappingStmt:
+ address = AlterRoutineMapping((AlterRoutineMappingStmt *) parsetree);
+ break;
+
+ case T_DropRoutineMappingStmt:
+ RemoveRoutineMapping((DropRoutineMappingStmt *) parsetree);
+ /* no commands stashed for DROP */
+ commandCollected = true;
+ break;
+
case T_CompositeTypeStmt: /* CREATE TYPE (composite) */
{
CompositeTypeStmt *stmt = (CompositeTypeStmt *) parsetree;
@@ -2250,6 +2267,18 @@ CreateCommandTag(Node *parsetree)
tag = "IMPORT FOREIGN SCHEMA";
break;
+ case T_CreateRoutineMappingStmt:
+ tag = "CREATE ROUTINE MAPPING";
+ break;
+
+ case T_AlterRoutineMappingStmt:
+ tag = "ALTER ROUTINE MAPPING";
+ break;
+
+ case T_DropRoutineMappingStmt:
+ tag = "DROP ROUTINE MAPPING";
+ break;
+
case T_DropStmt:
switch (((DropStmt *) parsetree)->removeType)
{
@@ -3006,6 +3035,9 @@ GetCommandLogLevel(Node *parsetree)
case T_CreateUserMappingStmt:
case T_AlterUserMappingStmt:
case T_DropUserMappingStmt:
+ case T_CreateRoutineMappingStmt:
+ case T_AlterRoutineMappingStmt:
+ case T_DropRoutineMappingStmt:
case T_ImportForeignSchemaStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index bba595a..a585101 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2838,8 +2838,7 @@ type_is_collatable(Oid typid)
* Currently this is only consulted for individual tables, not for inheritance
* trees, so we don't need an "inh" parameter.
*
- * Calling a hook at this point looks somewhat strange, but is required
- * because the optimizer calls this function without any other way for
+ * Calling a hook at this point looks somewhat strange, but is required * because the optimizer calls this function without any other way for
* plug-ins to control the result.
*/
int32
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 2b38178..355cffc 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -59,6 +59,7 @@
#include "catalog/pg_shdepend.h"
#include "catalog/pg_shdescription.h"
#include "catalog/pg_shseclabel.h"
+#include "catalog/pg_routine_mapping.h"
#include "catalog/pg_replication_origin.h"
#include "catalog/pg_statistic.h"
#include "catalog/pg_statistic_ext.h"
@@ -705,6 +706,39 @@ static const struct cachedesc cacheinfo[] = {
},
16
},
+ {RoutineMappingRelationId, /* ROUTINEMAPPINGOID */
+ RoutineMappingOidIndexId,
+ 1,
+ {
+ ObjectIdAttributeNumber,
+ 0,
+ 0,
+ 0
+ },
+ 2
+ },
+ {RoutineMappingRelationId, /* ROUTINEMAPPINGPROCSERVER */
+ RoutineMappingProcServerIndexId,
+ 2,
+ {
+ Anum_pg_routine_mapping_rmproc,
+ Anum_pg_routine_mapping_rmserver,
+ 0,
+ 0
+ },
+ 2
+ },
+ {RoutineMappingRelationId, /* ROUTINEMAPINGNAME */
+ RoutineMappingNameIndexId,
+ 1,
+ {
+ Anum_pg_routine_mapping_rmname,
+ 0,
+ 0,
+ 0
+ },
+ 2
+ },
{RewriteRelationId, /* RULERELNAME */
RewriteRelRulenameIndexId,
2,
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 46c271a..f027521 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -173,6 +173,7 @@ typedef enum ObjectClass
OCLASS_FDW, /* pg_foreign_data_wrapper */
OCLASS_FOREIGN_SERVER, /* pg_foreign_server */
OCLASS_USER_MAPPING, /* pg_user_mapping */
+ OCLASS_ROUTINE_MAPPING, /* pg_routine_mapping */
OCLASS_DEFACL, /* pg_default_acl */
OCLASS_EXTENSION, /* pg_extension */
OCLASS_EVENT_TRIGGER, /* pg_event_trigger */
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index 2491582..539e410 100644
--- a/src/include/catalog/indexing.h
+++ b/src/include/catalog/indexing.h
@@ -299,6 +299,13 @@ DECLARE_UNIQUE_INDEX(pg_user_mapping_oid_index, 174, on pg_user_mapping using bt
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
+DECLARE_UNIQUE_INDEX(pg_routine_mapping_oid_index, 6021, on pg_routine_mapping using btree(oid oid_ops));
+#define RoutineMappingOidIndexId 6021
+DECLARE_UNIQUE_INDEX(pg_routine_mapping_proc_server_index, 6022, on pg_routine_mapping using btree(rmproc oid_ops, rmserver oid_ops));
+#define RoutineMappingProcServerIndexId 6022
+DECLARE_UNIQUE_INDEX(pg_routine_mapping_name_index, 6023, on pg_routine_mapping using btree(rmname name_ops));
+#define RoutineMappingNameIndexId 6023
+
DECLARE_UNIQUE_INDEX(pg_foreign_table_relid_index, 3119, on pg_foreign_table using btree(ftrelid oid_ops));
#define ForeignTableRelidIndexId 3119
diff --git a/src/include/catalog/pg_routine_mapping.h b/src/include/catalog/pg_routine_mapping.h
new file mode 100644
index 0000000..cf3e7cb
--- /dev/null
+++ b/src/include/catalog/pg_routine_mapping.h
@@ -0,0 +1,45 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_routine_mapping.h
+ * definition of the "routine mapping" system catalog (pg_routine_mapping)
+ *
+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/catalog/pg_routine_mapping.h
+ *
+ * NOTES
+ * The Catalog.pm module reads this file and derives schema
+ * information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_ROUTINE_MAPPING_H
+#define PG_ROUTINE_MAPPING_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_routine_mapping_d.h"
+
+/* ----------------
+ * pg_routine_mapping definition. cpp turns this into
+ * typedef struct FormData_pg_routine_mapping
+ * ----------------
+ */
+CATALOG(pg_routine_mapping,6020,RoutineMappingRelationId)
+{
+ NameData rmname;
+ Oid rmproc;
+ Oid rmserver;
+#ifdef CATALOG_VARLEN
+ text rmoptions[1];
+#endif
+} FormData_pg_routine_mapping;
+
+/* ----------------
+ * Form_pg_routine_mapping corresponds to a pointer to a tuple with
+ * the format of pg_routine_mapping relation.
+ * ----------------
+ */
+typedef FormData_pg_routine_mapping *Form_pg_routine_mapping;
+
+#endif /* PG_ROUTINE_MAPPING_H */
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 6b83723..3df9d49 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -150,6 +150,10 @@ extern Datum transformGenericOptions(Oid catalogId,
Datum oldOptions,
List *options,
Oid fdwvalidator);
+extern ObjectAddress CreateRoutineMapping(CreateRoutineMappingStmt *stmt);
+extern ObjectAddress AlterRoutineMapping(AlterRoutineMappingStmt *stmt);
+extern Oid RemoveRoutineMapping(DropRoutineMappingStmt *stmt);
+extern void RemoveRoutineMappingById(Oid rmId);
/* commands/amcmds.c */
extern ObjectAddress CreateAccessMethod(CreateAmStmt *stmt);
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index 3ca12e6..05393dd 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -68,6 +68,14 @@ typedef struct ForeignTable
List *options; /* ftoptions as DefElem list */
} ForeignTable;
+typedef struct RoutineMapping
+{
+ Oid rmid; /* Oid of routine mapping */
+ char *rmname;
+ Oid procid; /* local function oid */
+ Oid serverid; /* server oid */
+ List *options; /* rmoptions as DefElem list */
+} RoutineMapping;
extern ForeignServer *GetForeignServer(Oid serverid);
extern ForeignServer *GetForeignServerByName(const char *name, bool missing_ok);
@@ -76,6 +84,8 @@ extern ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
bool missing_ok);
extern ForeignTable *GetForeignTable(Oid relid);
+extern RoutineMapping *GetRoutineMapping(Oid rmid);
+extern RoutineMapping *GetRoutineMappingByName(const char *rmname, bool missing_ok);
extern List *GetForeignColumnOptions(Oid relid, AttrNumber attnum);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 697d3d7..70aff71 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -419,6 +419,9 @@ typedef enum NodeTag
T_CreateStatsStmt,
T_AlterCollationStmt,
T_CallStmt,
+ T_CreateRoutineMappingStmt,
+ T_AlterRoutineMappingStmt,
+ T_DropRoutineMappingStmt,
/*
* TAGS FOR PARSE TREE NODES (parsenodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 07ab1a3..98f761f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2316,6 +2316,35 @@ typedef struct ImportForeignSchemaStmt
List *options; /* list of options to pass to FDW */
} ImportForeignSchemaStmt;
+/* ----------------------
+ * Create/Drop ROUTINE MAPPING Statements
+ * ----------------------
+ */
+typedef struct CreateRoutineMappingStmt
+{
+ NodeTag type;
+ char *name; /* routine mapping name */
+ ObjectType objtype;
+ ObjectWithArgs *func; /* name and args of function */
+ char *servername; /* server name */
+ List *options; /* generic option to server */
+ bool if_not_exists;
+} CreateRoutineMappingStmt;
+
+typedef struct AlterRoutineMappingStmt
+{
+ NodeTag type;
+ char *name;
+ List *options;
+} AlterRoutineMappingStmt;
+
+typedef struct DropRoutineMappingStmt
+{
+ NodeTag type;
+ char *name;
+ bool missing_ok;
+} DropRoutineMappingStmt;
+
/*----------------------
* Create POLICY Statement
*----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 23db401..52752d7 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -243,7 +243,7 @@ PG_KEYWORD("location", LOCATION, UNRESERVED_KEYWORD)
PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD)
PG_KEYWORD("locked", LOCKED, UNRESERVED_KEYWORD)
PG_KEYWORD("logged", LOGGED, UNRESERVED_KEYWORD)
-PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD)
+PG_KEYWORD("mapping", MAPPING, RESERVED_KEYWORD)
PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD)
PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD)
PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD)
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 4f33358..96282ea 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -84,6 +84,9 @@ enum SysCacheIdentifier
RELOID,
REPLORIGIDENT,
REPLORIGNAME,
+ ROUTINEMAPPINGOID,
+ ROUTINEMAPPINGPROCSERVER,
+ ROUTINEMAPPINGNAME,
RULERELNAME,
SEQRELID,
STATEXTNAMENSP,
--
2.10.5
Hello.
At Tue, 4 Sep 2018 09:34:21 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in <CAD21AoCBfTKRFPwboss4xVEoVwUmi0gKBgwsWQijviJP3hScwQ@mail.gmail.com>
On Tue, Sep 4, 2018 at 5:48 AM, David Fetter <david@fetter.org> wrote:
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.
Do you mean that ISO/IEC 9075-9:2016 (right?) is defining that
(and we must follow it)? Or does it comes by referring to
something like [1]http://cs.unibo.it/~montesi/CBD/Articoli/SQL-MED.pdf? As far as I see David's mail upthread,
OPTIONS is not precisely defined.
[1]: http://cs.unibo.it/~montesi/CBD/Articoli/SQL-MED.pdf
Unfortunately I don't have access to the document nor concrete
use cases. With a rough idea of "remote mapping", I can guess the
followng four use cases. Each example syntax is just a guess
without any consideration on implementability or other
restrictions. The patch looks currently covering B.
A. Just notify a function can be just pushed down.
ex. SELECT foo(1, 'bar'); Remote: SELECT foo(1, 'bar');
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem;
(or same as B)
B. Replace function name with the remote equivalent.
ex. SELECT foo(1, 'bar'); Remote: SELECT hoge(1, 'bar');
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem
OPTIONS (remote_func_name 'hoge'));
C. Adjust function specification with remote.
ex. SELECT foo(1, 'bar'); Remote: SELECT hoge('bar', 1, true);
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem
OPTIONS (remote_expression 'hoge($2,$1,true)');
D. Replace with an equivalent remote expression.
ex. SELECT foo(1, 'bar'); Remote: SELECT ('bar' || to_char(1 % 10));
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem
OPTIONS (remote_expression '$2 || to_char($1 % 10)');
I haven't looked the patch in depth, but the core side looks
generic and the FDW side is extensible to A, C and D. I think B
is enough as a starter. I don't mean that we should implement all
of them. They are just possibilities.
I have some comments on the patch.
It doesn't seem working. Am I missing something?
====
create server sv1 foreign data wrapper postgres_fdw options (host '/tmp', port '5432', dbname 'postgres');
create table lt (a int);
create foreign table ft (a int) server sv1 options (table_name 'lt');
create function lhoge(int) returns int as 'begin return $1 * 2; end;' language plpgsql;
create routine mapping rm1 for function lhoge(int) server sv1 options (remote_func_name 'rhoge');
explain verbose select * from ft where a = lhoge(3);
QUERY PLAN
------------------------------------------------------------------
Foreign Scan on public.ft (cost=100.00..936.31 rows=15 width=4)
Output: a
Filter: (ft.a = lhoge(3))
Remote SQL: SELECT a FROM public.lt
(4 rows)
====
Perhaps it cannot push down simple SQL local functions. (I'm not
sure we should do that.)
Can't we specify remote schema in remote_func_name just as
(remote_func_name "fooschema.funcname")?
Can't we provide the syntax without making MAPPING reserved?
Chainging the syntax for ALTER/DROP ROUTINE MAPPING like USER
MAPPING, specifically to ALTER/DROP ROUTINE MAPPING FOR FUNCTION
fname SERVER sname would evade the conflict.
Can't we just push down the function itself with no option defined?
CREATE ROUTINE MAPPING rm1 FOR FUNCTION pg_backend_pid() SERVER sv1;
funcid is not used in objectaddress.c.
(The patch is missing a LF in a comment in lsyscache.c)
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
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.huinker@gmail.com> wrote:
Show quoted text
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.
On Mon, Sep 10, 2018 at 3:28 AM Hannu Krosing <hannu.krosing@2ndquadrant.com>
wrote:
Hi Corey
Have you looked at pl/proxy ?
I have, a long while ago.
It does this and then some (sharding)
PL/proxy isn't a part of the SQL Standard.
PL/proxy only connects to other libpq-speaking databases.
The hope with routine mapping is that other data sources that do not easily
conform to a rows-and-columns metaphor can still expose their data to
postgresql.
Thank you for the comment.
On Mon, Sep 10, 2018 at 4:16 PM, Kyotaro HORIGUCHI
<horiguchi.kyotaro@lab.ntt.co.jp> wrote:
Hello.
At Tue, 4 Sep 2018 09:34:21 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in <CAD21AoCBfTKRFPwboss4xVEoVwUmi0gKBgwsWQijviJP3hScwQ@mail.gmail.com>
On Tue, Sep 4, 2018 at 5:48 AM, David Fetter <david@fetter.org> wrote:
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.Do you mean that ISO/IEC 9075-9:2016 (right?) is defining that
(and we must follow it)? Or does it comes by referring to
something like [1]? As far as I see David's mail upthread,
OPTIONS is not precisely defined.
Yeah, I read [1] and the final committee draft ISO/IEC 9075-9:2006, it
might be old though.
[1] http://cs.unibo.it/~montesi/CBD/Articoli/SQL-MED.pdf
Unfortunately I don't have access to the document nor concrete
use cases. With a rough idea of "remote mapping", I can guess the
followng four use cases. Each example syntax is just a guess
without any consideration on implementability or other
restrictions. The patch looks currently covering B.
Thank you for summarizing.
A. Just notify a function can be just pushed down.
ex. SELECT foo(1, 'bar'); Remote: SELECT foo(1, 'bar');
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem;
(or same as B)B. Replace function name with the remote equivalent.
ex. SELECT foo(1, 'bar'); Remote: SELECT hoge(1, 'bar');
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem
OPTIONS (remote_func_name 'hoge'));C. Adjust function specification with remote.
ex. SELECT foo(1, 'bar'); Remote: SELECT hoge('bar', 1, true);
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem
OPTIONS (remote_expression 'hoge($2,$1,true)');D. Replace with an equivalent remote expression.
ex. SELECT foo(1, 'bar'); Remote: SELECT ('bar' || to_char(1 % 10));
CREATE ROUTINE MAPPING foomap FOR foo(int, text) SERVER rem
OPTIONS (remote_expression '$2 || to_char($1 % 10)');I haven't looked the patch in depth, but the core side looks
generic and the FDW side is extensible to A, C and D. I think B
is enough as a starter.
I don't mean that we should implement all of them. They are just possibilities.
I agree that this feature covers A and B as the first step. But I'm
concerned that for D (and maybe for C?) the volatility of mapped
function could be changed. That is, currently we allow to push down
only immutable functions but they might break it. Also, can the
replacing a function with any expression be a risk of sql injections?
Also, according to the standard the routine mapping seems to work when
columns of referenced foreign table are passed to the function that is
mapped to the remote function. The functions in WHERE clause will
obviously be mapped but I'm not sure for function in target lists.
I have some comments on the patch.
It doesn't seem working. Am I missing something?
====
create server sv1 foreign data wrapper postgres_fdw options (host '/tmp', port '5432', dbname 'postgres');
create table lt (a int);
create foreign table ft (a int) server sv1 options (table_name 'lt');
create function lhoge(int) returns int as 'begin return $1 * 2; end;' language plpgsql;
create routine mapping rm1 for function lhoge(int) server sv1 options (remote_func_name 'rhoge');
explain verbose select * from ft where a = lhoge(3);
QUERY PLAN
------------------------------------------------------------------
Foreign Scan on public.ft (cost=100.00..936.31 rows=15 width=4)
Output: a
Filter: (ft.a = lhoge(3))
Remote SQL: SELECT a FROM public.lt
(4 rows)
====Perhaps it cannot push down simple SQL local functions. (I'm not
sure we should do that.)
Yeah, the current patch maps only when the function can be pushed down
to the foreign server. So maybe you can use either the built-in
functions or functions that are provided an extension that is listed
in 'extension' option.
Can't we specify remote schema in remote_func_name just as
(remote_func_name "fooschema.funcname")?
That's a possible option.
Can't we provide the syntax without making MAPPING reserved?
I think it's possible but I haven't tried it hard yet. The problem I
faced is that, for example, without that the "DROP ROUTINE MAPPING
name" where the name can be the ColId conflicts with the "DROP ROUTINE
function_with_argtype_list [CASCADE | RESTRICT]" where
function_with_argtype_list can be the ColId as well.
Chainging the syntax for ALTER/DROP ROUTINE MAPPING like USER
MAPPING, specifically to ALTER/DROP ROUTINE MAPPING FOR FUNCTION
fname SERVER sname would evade the conflict.
Hmm, the syntax of ALTER/DROP ROUTINE MAPPING follow the standard.
Maybe we can discuss later.
Can't we just push down the function itself with no option defined?
CREATE ROUTINE MAPPING rm1 FOR FUNCTION pg_backend_pid() SERVER sv1;
Yeah, it would be an good idea.
funcid is not used in objectaddress.c.
(The patch is missing a LF in a comment in lsyscache.c)
Thanks, I'll fix them in the next patch.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
On Mon, Sep 10, 2018 at 09:28:31AM +0200, Hannu Krosing wrote:
Hi Corey
Have you looked at pl/proxy ?
DBI-Link pre-dated PL/proxy by some years, and was a good bit more
flexible as to what types of functions it could send where. Neither
has a capability fundamentally similar to this because neither had any
way to interact with the planner, other quals, etc.
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
[ I wasn't paying much attention to this thread at the time, but
Kyotaro-san just drew my attention to it again ]
Masahiko Sawada <sawada.mshk@gmail.com> writes:
I agree that this feature covers A and B as the first step. But I'm
concerned that for D (and maybe for C?) the volatility of mapped
function could be changed. That is, currently we allow to push down
only immutable functions but they might break it. Also, can the
replacing a function with any expression be a risk of sql injections?
Yes, I'm afraid that there's serious security issues here that
the SQL standard fails to address. Let's assume that user A has
created a function F(), and user B has created a foreign server S.
What privileges should be required for user C to create a mapping
for F on S? AFAICS, the spec only requires C to have USAGE on S.
This means that all C needs is USAGE on S to create a trojan horse
that will execute arbitrary code when any other user D executes
a query using F on S. C doesn't need to have any privilege on F
at all, and USAGE is not exactly strong privilege on S --- you
certainly wouldn't expect that "USAGE" translates to "I can
backdoor anybody else's usage of this server".
I see that SQL:2011's access rules for "CREATE ROUTINE MAPPING" are
1) The applicable privileges shall include the USAGE privilege on
the foreign server identified by FSN.
2) Additional privileges, if any, necessary to execute <routine
mapping definition> are implementation-defined.
It seems to me that (2) should be read as "we know we blew it here,
but we're leaving it up to implementors to fix this".
Some of the alternatives that were discussed upthread basically replace
this whole idea with attaching properties to the original function F.
I think I like that a lot better from a security perspective. If you are
calling F in your query, you are already placing trust in F's owner.
Better to let F's owner define how it maps to functions on remote servers
than to let random third parties define that.
regards, tom lane