[Proposal] Arbitrary queries in postgres_fdw
Dear all,
We stumbled upon a few cases in which retrieving information from the
foreign server may turn pretty useful before creating any foreign
table, especially info related to the catalog. E.g: a list of schemas
or tables the user has access to.
I thought of using dblink for it, but that requires duplication of
server and user mapping details and it adds its own management of
connections.
Then I thought a better approach may be a mix of both: a function to
issue arbitrary queries to the foreign server reusing all the details
encapsulated in the server and user mapping. It would use the same
pool of connections.
E.g:
CREATE FUNCTION postgres_fdw_query(server name, sql text)
RETURNS SETOF record
SELECT * FROM postgres_fdw_query('foreign_server', $$SELECT table_name,
table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name$$
) AS schemas(table_name text, table_type text);
Find attached a patch with a working PoC (with some code from
dblink). It is not meant to be perfect yet.
Is this something you may be interested in having as part of
postgres_fdw? Thoughts?
Thanks
-Rafa de la Torre
Attachments:
postgres_fdw_query.patchtext/x-patch; charset=US-ASCII; name=postgres_fdw_query.patchDownload
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 85394b4f1f..85a4ecb900 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -8,7 +8,7 @@ PG_CPPFLAGS = -I$(libpq_srcdir)
SHLIB_LINK_INTERNAL = $(libpq)
EXTENSION = postgres_fdw
-DATA = postgres_fdw--1.0.sql
+DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1.sql
REGRESS = postgres_fdw
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 88dbaa2493..c83d727192 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8801,3 +8801,60 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
-- Clean-up
RESET enable_partitionwise_aggregate;
+-- ===================================================================
+-- test postgres_fdw_query(server name, sql text)
+-- ===================================================================
+-- Most simple SELECT through postgres_fdw_query
+SELECT * FROM postgres_fdw_query('loopback', 'SELECT 42') AS t(i int);
+ i
+----
+ 42
+(1 row)
+
+-- Select the effective role configured in the user mapping
+SELECT * FROM postgres_fdw_query('loopback', 'SELECT current_user')
+ AS t(role_name name);
+ role_name
+-----------
+ postgres
+(1 row)
+
+-- Select schemas owned by the role configured in the user mapping
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT s.nspname
+ FROM pg_catalog.pg_namespace s
+ JOIN pg_catalog.pg_user u ON u.usesysid = s.nspowner
+ WHERE u.usename = current_user
+ ORDER BY s.nspname$$
+) AS schemas(schema_name name);
+ schema_name
+--------------------
+ S 1
+ import_dest1
+ import_dest2
+ import_dest3
+ import_dest4
+ import_dest5
+ import_source
+ information_schema
+ pg_catalog
+ pg_temp_1
+ pg_toast
+ pg_toast_temp_1
+ public
+(13 rows)
+
+-- Select tables and views in a given foreign schema that the role
+-- configured in the user mapping has access to
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT table_name, table_type
+ FROM information_schema.tables
+ WHERE table_schema = 'S 1'
+ ORDER BY table_name$$
+) AS schemas(table_name text, table_type text);
+ table_name | table_type
+------------+------------
+ T 1 | BASE TABLE
+ T 2 | BASE TABLE
+ T 3 | BASE TABLE
+ T 4 | BASE TABLE
+(4 rows)
+
diff --git a/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql
new file mode 100644
index 0000000000..15a7c83519
--- /dev/null
+++ b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql
@@ -0,0 +1,7 @@
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION postgres_fdw" to load this file. \quit
+
+CREATE FUNCTION postgres_fdw_query(server name, sql text)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
diff --git a/contrib/postgres_fdw/postgres_fdw--1.1.sql b/contrib/postgres_fdw/postgres_fdw--1.1.sql
new file mode 100644
index 0000000000..1f4dd1f32b
--- /dev/null
+++ b/contrib/postgres_fdw/postgres_fdw--1.1.sql
@@ -0,0 +1,21 @@
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION postgres_fdw" to load this file. \quit
+
+CREATE FUNCTION postgres_fdw_handler()
+RETURNS fdw_handler
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION postgres_fdw_validator(text[], oid)
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION postgres_fdw_query(server name, sql text)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+
+CREATE FOREIGN DATA WRAPPER postgres_fdw
+ HANDLER postgres_fdw_handler
+ VALIDATOR postgres_fdw_validator;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 025f922b4c..a8689fe591 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5900,3 +5900,170 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
/* We didn't find any suitable equivalence class expression */
return NULL;
}
+
+
+
+static void prepTuplestoreResult(FunctionCallInfo fcinfo);
+
+PG_FUNCTION_INFO_V1(postgres_fdw_query);
+
+Datum
+postgres_fdw_query(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ Name server_name;
+ text *sql_text;
+ char *server;
+ char *sql;
+ Oid userid;
+ PGconn *conn;
+ UserMapping *user_mapping;
+ ForeignServer *foreign_server;
+ PGresult *res = NULL;
+ TupleDesc tupdesc;
+ int ntuples;
+ int nfields;
+
+ prepTuplestoreResult(fcinfo);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ /* One-time setup code appears here: */
+
+ // Get input args
+ server_name = PG_GETARG_NAME(0);
+ sql_text = PG_GETARG_TEXT_PP(1);
+
+ server = NameStr(*server_name);
+ sql = text_to_cstring(sql_text);
+
+ elog(DEBUG3, "server = %s", server);
+ elog(DEBUG3, "sql = %s", sql);
+
+ // Get a connection to the server with the current user
+ userid = GetUserId();
+ foreign_server = GetForeignServerByName(server, false);
+ user_mapping = GetUserMapping(userid, foreign_server->serverid);
+ conn = GetConnection(user_mapping, false);
+
+ // Execute the sql query
+ PG_TRY();
+ {
+ res = pgfdw_exec_query(conn, sql);
+ nfields = PQnfields(res);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(ERROR, res, conn, false, sql);
+
+ /* get a tuple descriptor for our result type */
+ switch (get_call_result_type(fcinfo, NULL, &tupdesc))
+ {
+ case TYPEFUNC_COMPOSITE:
+ /* success */
+ break;
+ case TYPEFUNC_RECORD:
+ /* failed to determine actual type of RECORD */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("function returning record called in context "
+ "that cannot accept type record")));
+ break;
+ default:
+ /* result type isn't composite */
+ elog(ERROR, "return type must be a row type");
+ break;
+ }
+
+ /* make sure we have a persistent copy of the tupdesc */
+ tupdesc = CreateTupleDescCopy(tupdesc);
+ ntuples = PQntuples(res);
+ nfields = PQnfields(res);
+
+ /* check result and tuple descriptor have the same number of columns */
+ if (nfields != tupdesc->natts)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("remote query result rowtype does not match "
+ "the specified FROM clause rowtype")));
+
+ if (ntuples > 0)
+ {
+ AttInMetadata *attinmeta;
+ Tuplestorestate *tupstore;
+ MemoryContext oldcontext;
+ int row;
+ char **values;
+
+ attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+ oldcontext = MemoryContextSwitchTo(
+ rsinfo->econtext->ecxt_per_query_memory);
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+ MemoryContextSwitchTo(oldcontext);
+
+ values = (char **) palloc(nfields * sizeof(char *));
+
+ /* put all tuples into the tuplestore */
+ for (row = 0; row < ntuples; row++)
+ {
+ HeapTuple tuple;
+ int i;
+
+ for (i = 0; i < nfields; i++)
+ {
+ if (PQgetisnull(res, row, i))
+ values[i] = NULL;
+ else
+ values[i] = PQgetvalue(res, row, i);
+ }
+
+ /* build the tuple and put it into the tuplestore. */
+ tuple = BuildTupleFromCStrings(attinmeta, values);
+ tuplestore_puttuple(tupstore, tuple);
+ }
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+ }
+
+ PQclear(res);
+ }
+ PG_CATCH();
+ {
+ if (res)
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ ReleaseConnection(conn);
+ return (Datum) 0;
+}
+
+/*
+ * Verify function caller can handle a tuplestore result, and set up for that.
+ *
+ * Note: if the caller returns without actually creating a tuplestore, the
+ * executor will treat the function result as an empty set.
+ */
+static void
+prepTuplestoreResult(FunctionCallInfo fcinfo)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ /* check to see if query supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not allowed in this context")));
+
+ /* let the executor know we're sending back a tuplestore */
+ rsinfo->returnMode = SFRM_Materialize;
+
+ /* caller must fill these to return a non-empty result */
+ rsinfo->setResult = NULL;
+ rsinfo->setDesc = NULL;
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.control b/contrib/postgres_fdw/postgres_fdw.control
index f9ed490752..d489382064 100644
--- a/contrib/postgres_fdw/postgres_fdw.control
+++ b/contrib/postgres_fdw/postgres_fdw.control
@@ -1,5 +1,5 @@
# postgres_fdw extension
comment = 'foreign-data wrapper for remote PostgreSQL servers'
-default_version = '1.0'
+default_version = '1.1'
module_pathname = '$libdir/postgres_fdw'
relocatable = true
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index a87c57df7b..8e79ee205f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2407,3 +2407,31 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
-- Clean-up
RESET enable_partitionwise_aggregate;
+
+
+-- ===================================================================
+-- test postgres_fdw_query(server name, sql text)
+-- ===================================================================
+
+-- Most simple SELECT through postgres_fdw_query
+SELECT * FROM postgres_fdw_query('loopback', 'SELECT 42') AS t(i int);
+
+-- Select the effective role configured in the user mapping
+SELECT * FROM postgres_fdw_query('loopback', 'SELECT current_user')
+ AS t(role_name name);
+
+-- Select schemas owned by the role configured in the user mapping
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT s.nspname
+ FROM pg_catalog.pg_namespace s
+ JOIN pg_catalog.pg_user u ON u.usesysid = s.nspowner
+ WHERE u.usename = current_user
+ ORDER BY s.nspname$$
+) AS schemas(schema_name name);
+
+-- Select tables and views in a given foreign schema that the role
+-- configured in the user mapping has access to
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT table_name, table_type
+ FROM information_schema.tables
+ WHERE table_schema = 'S 1'
+ ORDER BY table_name$$
+) AS schemas(table_name text, table_type text);
rtorre@carto.com writes:
We stumbled upon a few cases in which retrieving information from the
foreign server may turn pretty useful before creating any foreign
table, especially info related to the catalog. E.g: a list of schemas
or tables the user has access to.
I thought of using dblink for it, but that requires duplication of
server and user mapping details and it adds its own management of
connections.
Then I thought a better approach may be a mix of both: a function to
issue arbitrary queries to the foreign server reusing all the details
encapsulated in the server and user mapping. It would use the same
pool of connections.
dblink can already reference a postgres_fdw "server" for connection
details, so I think this problem is already solved from the usability
end of things. And allowing arbitrary queries to go over a postgres_fdw
connection would be absolutely disastrous from a debuggability and
maintainability standpoint, because they might change the remote
session's state in ways that postgres_fdw isn't prepared to handle.
(In a dblink connection, the remote session's state is the user's
responsibility to manage, but this isn't the case for postgres_fdw.)
So I think this proposal has to be firmly rejected.
regards, tom lane
On Fri, Oct 25, 2019 at 05:17:18PM +0200, rtorre@carto.com wrote:
Dear all,
We stumbled upon a few cases in which retrieving information from the
foreign server may turn pretty useful before creating any foreign
table, especially info related to the catalog. E.g: a list of schemas
or tables the user has access to.I thought of using dblink for it, but that requires duplication of
server and user mapping details and it adds its own management of
connections.Then I thought a better approach may be a mix of both: a function to
issue arbitrary queries to the foreign server reusing all the details
encapsulated in the server and user mapping. It would use the same
pool of connections.
There's a SQL MED standard feature for CREATE ROUTINE MAPPING that
does something similar to this. Might it be possible to incorporate
it into the previous patch that implemented that feature?
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 Sun, Oct 27, 2019 at 7:07 PM David Fetter <david@fetter.org> wrote:
There's a SQL MED standard feature for CREATE ROUTINE MAPPING that
does something similar to this. Might it be possible to incorporate
it into the previous patch that implemented that feature?
Thanks for the idea, David. I'll investigate it and hopefully
come up with a more standard proposal.
Best regards
-Rafa
On Fri, Oct 25, 2019 at 12:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
end of things. And allowing arbitrary queries to go over a postgres_fdw
connection would be absolutely disastrous from a debuggability and
maintainability standpoint, because they might change the remote
session's state in ways that postgres_fdw isn't prepared to handle.
(In a dblink connection, the remote session's state is the user's
responsibility to manage, but this isn't the case for postgres_fdw.)
So I think this proposal has to be firmly rejected.
I think the reduction in debuggability and maintainability has to be
balanced against a possible significant gain in usability. I mean,
you could document that if the values of certain GUCs are changed, or
if you create and drop prepared statements with certain names, it
might cause queries in the same session issued through the regular
foreign table API to produce wrong answers. That would still leave an
enormous number of queries that users could issue with absolutely no
problems. I really don't see a bona fide maintainability problem here.
When someone produces a reproducible test case showing that they did
one of the things we told them not to do, then we'll tell them to read
the fine manual and move on.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Sun, Oct 27, 2019 at 7:07 PM David Fetter <david@fetter.org> wrote:
There's a SQL MED standard feature for CREATE ROUTINE MAPPING that
does something similar to this. Might it be possible to incorporate
it into the previous patch that implemented that feature?
Supporting CREATE ROUTINE MAPPING goes a level beyond
postgres_fdw. It'd require adding new DDL syntax elements to the
parser, catalog and callbacks for the FDW's to support them.
For the record, there's a very interesting thread on this topic (that
you participated in):
/messages/by-id/CADkLM=dK0dmkzLhaLPpnjN2wBF5GRpvzOr=eW0EWdCnG-OHnpQ@mail.gmail.com
I know they have different semantics and may turn more limiting, but
for certain use cases, the `extensions` parameter of postgres_fdw may
come in handy (shipping function calls to the foreign end from
extensions present in both local and foreign).
For my use case, which is retrieving catalog info before any CREATE
FOREIGN TABLE, CREATE ROUTINE MAPPING is not really a good fit.
Thank you for pointing out anyway.
-Rafa
On Fri, Oct 25, 2019 at 12:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
end of things. And allowing arbitrary queries to go over a postgres_fdw
connection would be absolutely disastrous from a debuggability and
maintainability standpoint, because they might change the remote
session's state in ways that postgres_fdw isn't prepared to handle.
(In a dblink connection, the remote session's state is the user's
responsibility to manage, but this isn't the case for postgres_fdw.)
So I think this proposal has to be firmly rejected.
On Mon, Oct 28, 2019 at 1:54 PM Robert Haas <robertmhaas@gmail.com> wrote:
I think the reduction in debuggability and maintainability has to be
balanced against a possible significant gain in usability. I mean,
you could document that if the values of certain GUCs are changed, or
if you create and drop prepared statements with certain names, it
might cause queries in the same session issued through the regular
foreign table API to produce wrong answers. That would still leave an
enormous number of queries that users could issue with absolutely no
problems.
I understand both points, the alternatives and the tradeoffs.
My motivations not use dblink are twofold: to purposefully reuse the
connection pool in postgres_fdw, and to avoid installing another
extension. I cannot speak to whether this can be advantageous to
others to accept the tradeoffs.
If you are still interested, I'm willing to listen to the feedback and
continue improving the patch. Otherwise we can settle it here and (of
course!) I won't take any offense because of that.
Find attached v2 of the patch with the following changes:
- added support for commands, as it failed upon PGRES_COMMAND_OK (with
tests with prepared statements)
- documentation for the new function, with the mentioned caveats
- removed the test with the `SELECT current_user`, because it produced
different results depending on the execution environment.
Regards
-Rafa
Attachments:
postgres_fdw_query_v2.patchtext/x-patch; charset=US-ASCII; name=postgres_fdw_query_v2.patchDownload
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 85394b4f1f..85a4ecb900 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -8,7 +8,7 @@ PG_CPPFLAGS = -I$(libpq_srcdir)
SHLIB_LINK_INTERNAL = $(libpq)
EXTENSION = postgres_fdw
-DATA = postgres_fdw--1.0.sql
+DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1.sql
REGRESS = postgres_fdw
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 88dbaa2493..2219235731 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8801,3 +8801,72 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
-- Clean-up
RESET enable_partitionwise_aggregate;
+-- ===================================================================
+-- test postgres_fdw_query(server name, sql text)
+-- ===================================================================
+-- Most simple SELECT through postgres_fdw_query
+SELECT * FROM postgres_fdw_query('loopback', 'SELECT 42') AS t(i int);
+ i
+----
+ 42
+(1 row)
+
+-- Select schemas owned by the role configured in the user mapping
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT s.nspname
+ FROM pg_catalog.pg_namespace s
+ JOIN pg_catalog.pg_user u ON u.usesysid = s.nspowner
+ WHERE u.usename = current_user
+ ORDER BY s.nspname$$
+) AS schemas(schema_name name);
+ schema_name
+--------------------
+ S 1
+ import_dest1
+ import_dest2
+ import_dest3
+ import_dest4
+ import_dest5
+ import_source
+ information_schema
+ pg_catalog
+ pg_temp_1
+ pg_toast
+ pg_toast_temp_1
+ public
+(13 rows)
+
+-- Select tables and views in a given foreign schema that the role
+-- configured in the user mapping has access to
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT table_name, table_type
+ FROM information_schema.tables
+ WHERE table_schema = 'S 1'
+ ORDER BY table_name$$
+) AS schemas(table_name text, table_type text);
+ table_name | table_type
+------------+------------
+ T 1 | BASE TABLE
+ T 2 | BASE TABLE
+ T 3 | BASE TABLE
+ T 4 | BASE TABLE
+(4 rows)
+
+-- Test we can send commands (e.g: prepared statements)
+SELECT * FROM postgres_fdw_query('loopback', $$PREPARE fooplan (int) AS
+SELECT $1 + 42$$) AS t(res text);
+ res
+---------
+ PREPARE
+(1 row)
+
+SELECT * FROM postgres_fdw_query('loopback', 'EXECUTE fooplan (1)') AS t(i int);
+ i
+----
+ 43
+(1 row)
+
+SELECT * FROM postgres_fdw_query('loopback', 'DEALLOCATE fooplan') AS t(res text);
+ res
+------------
+ DEALLOCATE
+(1 row)
+
diff --git a/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql
new file mode 100644
index 0000000000..15a7c83519
--- /dev/null
+++ b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql
@@ -0,0 +1,7 @@
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION postgres_fdw" to load this file. \quit
+
+CREATE FUNCTION postgres_fdw_query(server name, sql text)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
diff --git a/contrib/postgres_fdw/postgres_fdw--1.1.sql b/contrib/postgres_fdw/postgres_fdw--1.1.sql
new file mode 100644
index 0000000000..1f4dd1f32b
--- /dev/null
+++ b/contrib/postgres_fdw/postgres_fdw--1.1.sql
@@ -0,0 +1,21 @@
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION postgres_fdw" to load this file. \quit
+
+CREATE FUNCTION postgres_fdw_handler()
+RETURNS fdw_handler
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION postgres_fdw_validator(text[], oid)
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION postgres_fdw_query(server name, sql text)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+
+CREATE FOREIGN DATA WRAPPER postgres_fdw
+ HANDLER postgres_fdw_handler
+ VALIDATOR postgres_fdw_validator;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 025f922b4c..ea88da9209 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5900,3 +5900,200 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
/* We didn't find any suitable equivalence class expression */
return NULL;
}
+
+
+
+static void prepTuplestoreResult(FunctionCallInfo fcinfo);
+
+PG_FUNCTION_INFO_V1(postgres_fdw_query);
+
+Datum
+postgres_fdw_query(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ Name server_name;
+ text *sql_text;
+ char *server;
+ char *sql;
+ Oid userid;
+ PGconn *conn;
+ UserMapping *user_mapping;
+ ForeignServer *foreign_server;
+ PGresult *res = NULL;
+ TupleDesc tupdesc;
+ int ntuples;
+ int nfields;
+ bool is_sql_cmd;
+
+ prepTuplestoreResult(fcinfo);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ /* One-time setup code appears here: */
+
+ // Get input args
+ server_name = PG_GETARG_NAME(0);
+ sql_text = PG_GETARG_TEXT_PP(1);
+
+ server = NameStr(*server_name);
+ sql = text_to_cstring(sql_text);
+
+ elog(DEBUG3, "server = %s", server);
+ elog(DEBUG3, "sql = %s", sql);
+
+ // Get a connection to the server with the current user
+ userid = GetUserId();
+ foreign_server = GetForeignServerByName(server, false);
+ user_mapping = GetUserMapping(userid, foreign_server->serverid);
+ conn = GetConnection(user_mapping, false);
+
+ // Execute the sql query
+ PG_TRY();
+ {
+ res = pgfdw_exec_query(conn, sql);
+
+ if (PQresultStatus(res) == PGRES_COMMAND_OK)
+ {
+ is_sql_cmd = true;
+
+ /*
+ * need a tuple descriptor representing one TEXT column to return
+ * the command status string as our result tuple
+ */
+ tupdesc = CreateTemplateTupleDesc(1, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
+ TEXTOID, -1, 0);
+ ntuples = 1;
+ nfields = 1;
+ }
+ else
+ {
+ is_sql_cmd = false;
+
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(ERROR, res, conn, false, sql);
+
+ nfields = PQnfields(res);
+
+ /* get a tuple descriptor for our result type */
+ switch (get_call_result_type(fcinfo, NULL, &tupdesc))
+ {
+ case TYPEFUNC_COMPOSITE:
+ /* success */
+ break;
+ case TYPEFUNC_RECORD:
+ /* failed to determine actual type of RECORD */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("function returning record called in context "
+ "that cannot accept type record")));
+ break;
+ default:
+ /* result type isn't composite */
+ elog(ERROR, "return type must be a row type");
+ break;
+ }
+
+ /* make sure we have a persistent copy of the tupdesc */
+ tupdesc = CreateTupleDescCopy(tupdesc);
+ ntuples = PQntuples(res);
+ nfields = PQnfields(res);
+ }
+
+ /* check result and tuple descriptor have the same number of columns */
+ if (nfields != tupdesc->natts)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("remote query result rowtype does not match "
+ "the specified FROM clause rowtype")));
+
+ if (ntuples > 0)
+ {
+ AttInMetadata *attinmeta;
+ Tuplestorestate *tupstore;
+ MemoryContext oldcontext;
+ int row;
+ char **values;
+
+ attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+ oldcontext = MemoryContextSwitchTo(
+ rsinfo->econtext->ecxt_per_query_memory);
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+ MemoryContextSwitchTo(oldcontext);
+
+ values = (char **) palloc(nfields * sizeof(char *));
+
+ /* put all tuples into the tuplestore */
+ for (row = 0; row < ntuples; row++)
+ {
+ HeapTuple tuple;
+
+ if (is_sql_cmd)
+ {
+ values[0] = PQcmdStatus(res);
+ }
+ else
+ {
+ int i;
+
+ for (i = 0; i < nfields; i++)
+ {
+ if (PQgetisnull(res, row, i))
+ values[i] = NULL;
+ else
+ values[i] = PQgetvalue(res, row, i);
+ }
+ }
+
+ /* build the tuple and put it into the tuplestore. */
+ tuple = BuildTupleFromCStrings(attinmeta, values);
+ tuplestore_puttuple(tupstore, tuple);
+ }
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+ }
+
+ PQclear(res);
+ }
+ PG_CATCH();
+ {
+ if (res)
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ ReleaseConnection(conn);
+ return (Datum) 0;
+}
+
+/*
+ * Verify function caller can handle a tuplestore result, and set up for that.
+ *
+ * Note: if the caller returns without actually creating a tuplestore, the
+ * executor will treat the function result as an empty set.
+ */
+static void
+prepTuplestoreResult(FunctionCallInfo fcinfo)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ /* check to see if query supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not allowed in this context")));
+
+ /* let the executor know we're sending back a tuplestore */
+ rsinfo->returnMode = SFRM_Materialize;
+
+ /* caller must fill these to return a non-empty result */
+ rsinfo->setResult = NULL;
+ rsinfo->setDesc = NULL;
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.control b/contrib/postgres_fdw/postgres_fdw.control
index f9ed490752..d489382064 100644
--- a/contrib/postgres_fdw/postgres_fdw.control
+++ b/contrib/postgres_fdw/postgres_fdw.control
@@ -1,5 +1,5 @@
# postgres_fdw extension
comment = 'foreign-data wrapper for remote PostgreSQL servers'
-default_version = '1.0'
+default_version = '1.1'
module_pathname = '$libdir/postgres_fdw'
relocatable = true
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index a87c57df7b..d62fc229f6 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2407,3 +2407,33 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
-- Clean-up
RESET enable_partitionwise_aggregate;
+
+
+-- ===================================================================
+-- test postgres_fdw_query(server name, sql text)
+-- ===================================================================
+
+-- Most simple SELECT through postgres_fdw_query
+SELECT * FROM postgres_fdw_query('loopback', 'SELECT 42') AS t(i int);
+
+-- Select schemas owned by the role configured in the user mapping
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT s.nspname
+ FROM pg_catalog.pg_namespace s
+ JOIN pg_catalog.pg_user u ON u.usesysid = s.nspowner
+ WHERE u.usename = current_user
+ ORDER BY s.nspname$$
+) AS schemas(schema_name name);
+
+-- Select tables and views in a given foreign schema that the role
+-- configured in the user mapping has access to
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT table_name, table_type
+ FROM information_schema.tables
+ WHERE table_schema = 'S 1'
+ ORDER BY table_name$$
+) AS schemas(table_name text, table_type text);
+
+-- Test we can send commands (e.g: prepared statements)
+SELECT * FROM postgres_fdw_query('loopback', $$PREPARE fooplan (int) AS
+SELECT $1 + 42$$) AS t(res text);
+SELECT * FROM postgres_fdw_query('loopback', 'EXECUTE fooplan (1)') AS t(i int);
+SELECT * FROM postgres_fdw_query('loopback', 'DEALLOCATE fooplan') AS t(res text);
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 737336f651..c3bc002d24 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -443,7 +443,7 @@
</sect3>
</sect2>
- <sect2>
+ <sect2 id="postgres-fdw-connection-management">
<title>Connection Management</title>
<para>
@@ -528,7 +528,7 @@
</para>
</sect2>
- <sect2>
+ <sect2 id="postgres-fdw-execution-environment">
<title>Remote Query Execution Environment</title>
<para>
@@ -584,6 +584,185 @@
</para>
</sect2>
+ <sect2>
+ <title>Remote Execution of Arbitrary Queries</title>
+
+ <para>
+ In some instances it may be useful to ship queries to a remote server not
+ depending upon the local planning on a foreign table. To that end, <xref
+ linkend="postgres-fdw-query"/> can be used.
+ </para>
+ <para>
+ Mind that <emphasis>it is the user's responsibility</emphasis> to ensure
+ session state is not affected by queries sent through
+ <function>postgres_fdw_query()</function> to the remote server.
+ </para>
+
+ <refentry id="postgres-fdw-query" xreflabel="postgres_fdw_query">
+ <indexterm>
+ <primary>postgres-fdw</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>postgres_fdw_query</refentrytitle>
+ <manvolnum>3</manvolnum>
+ </refmeta>
+
+ <refnamediv>
+ <refname>postgres_fdw_query</refname>
+ <refpurpose>executes a query in a foreign server</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+postgres_fdw_query(server name, sql text) returns setof record
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>postgres_fdw_query</function> executes a query (usually a <command>SELECT</command>,
+ but it can be any SQL statement that returns rows) in a foreign server.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><parameter>server</parameter></term>
+ <listitem>
+ <para>
+ Name of the foreign server to use, defined with
+ <command>CREATE SERVER</command> and accessed with the applicable user
+ mapping.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><parameter>sql</parameter></term>
+ <listitem>
+ <para>
+ The SQL query that you wish to execute in the foreign server,
+ for example <literal>SELECT * FROM foo</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The function returns the row(s) produced by the query. Since
+ <function>postgres_fdw_query</function> can be used with any query, it is
+ declared to return <type>record</type>, rather than specifying any
+ particular set of columns. This means that you must specify the expected
+ set of columns in the calling query — otherwise
+ <productname>PostgreSQL</productname> would not know what to expect. Here
+ is an example:
+
+<programlisting>
+SELECT * FROM postgres_fdw_query('foreign_server', $$SELECT table_name,
+table_type
+ FROM information_schema.tables
+ WHERE table_schema = 'public'
+ ORDER BY table_name$$
+) AS schemas(table_name text, table_type text);
+</programlisting>
+
+ The <quote>alias</quote> part of the <literal>FROM</literal> clause must
+ specify the column names and types that the function will return.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Caveats</title>
+
+ <para>
+ The function <function>postgres_fdw_query</function> does not perform any
+ checks on the input <parameter>sql</parameter>.
+ </para>
+
+ <para>
+ It also has the potential to change the state of the remote session. In
+ particular, it is recommended <emphasis>not</emphasis> to change
+ session-level settings and avoid using prepared statements prefixed with
+ <literal>pgsql_fdw_prep_</literal>. Otherwise it can interfere with the
+ regular functioning of <literal>postgres_fdw</literal>. It it is the
+ caller's responsibility not to interfere the session state managed by
+ <literal>postgres_fdw</literal>.
+ </para>
+
+ <para>
+ Please see <xref linkend="postgres-fdw-connection-management" /> and <xref
+ linkend="postgres-fdw-execution-environment" /> to understand how
+ connections and sessions are managed by <literal>postgres_fdw</literal>.
+ </para>
+
+ <para>
+ As an alternative, the older <xref linkend="dblink"/> module offers
+ similar functionality and sessions are managed independently from
+ <literal>postgres_fdw</literal>.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+<screen>
+-- Simple query
+SELECT * FROM postgres_fdw_query('loopback', 'SELECT 42') AS t(i int);
+ i
+----
+ 42
+(1 row)
+
+-- Retrieve info from the foreign information_schema
+SELECT * FROM postgres_fdw_query('loopback', $$SELECT table_name, table_type
+ FROM information_schema.tables
+ WHERE table_schema = 'S 1'
+ ORDER BY table_name$$
+) AS schemas(table_name text, table_type text);
+ table_name | table_type
+------------+------------
+ T 1 | BASE TABLE
+ T 2 | BASE TABLE
+ T 3 | BASE TABLE
+ T 4 | BASE TABLE
+(4 rows)
+
+-- Prepared statements
+SELECT * FROM postgres_fdw_query('loopback', $$PREPARE fooplan (int) AS
+SELECT $1 + 42$$) AS t(res text);
+ res
+---------
+ PREPARE
+(1 row)
+
+SELECT * FROM postgres_fdw_query('loopback', 'EXECUTE fooplan (1)') AS t(i int);
+ i
+----
+ 43
+(1 row)
+
+SELECT * FROM postgres_fdw_query('loopback', 'DEALLOCATE fooplan') AS t(res text);
+ res
+------------
+ DEALLOCATE
+(1 row)
+</screen>
+ </refsect1>
+ </refentry>
+
+ </sect2>
+
<sect2>
<title>Cross-Version Compatibility</title>
On Tue, Nov 05, 2019 at 11:09:34AM +0100, rtorre@carto.com wrote:
On Sun, Oct 27, 2019 at 7:07 PM David Fetter <david@fetter.org> wrote:
There's a SQL MED standard feature for CREATE ROUTINE MAPPING that
does something similar to this. Might it be possible to incorporate
it into the previous patch that implemented that feature?Supporting CREATE ROUTINE MAPPING goes a level beyond
postgres_fdw. It'd require adding new DDL syntax elements to the
parser, catalog and callbacks for the FDW's to support them.For the record, there's a very interesting thread on this topic (that
you participated in):
/messages/by-id/CADkLM=dK0dmkzLhaLPpnjN2wBF5GRpvzOr=eW0EWdCnG-OHnpQ@mail.gmail.comI know they have different semantics and may turn more limiting, but
for certain use cases, the `extensions` parameter of postgres_fdw may
come in handy (shipping function calls to the foreign end from
extensions present in both local and foreign).For my use case, which is retrieving catalog info before any CREATE
FOREIGN TABLE,
Could you use IMPORT FOREIGN SCHEMA for that? I seem to recall that
I've managed to import information_schema successfully.
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, Nov 5, 2019 at 7:41 PM David Fetter <david@fetter.org> wrote:
Could you use IMPORT FOREIGN SCHEMA for that? I seem to recall that
I've managed to import information_schema successfully.
Yes, I tried it and I can import and operate on the
information_schema, which actually covers part of my needs. It does so
at the expense of polluting the catalog with foreign tables, but I can
live with it. Thanks for pointing out.
There are other cases that can be covered with either this proposal or
CREATE ROUTINE MAPPING, but not with the current state of things (as
far as I know). E.g: calling version() or postgis_version() on the
foreign end.
It's largely a matter of convenience vs development effort. That said,
I understand this may not make the design quality cut.
Regards
-Rafa