Here's a third revision that allows the 'extensions' option on the
wrapper as well, so that supported extensions can be declared once in
one place.
Since the "CREATE FOREIGN DATA WRAPPER" statement is actually called
inside the "CREATE EXTENSION" script for postgres_fdw, the way to get
this option is actually to alter the wrapper,
ALTER FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( extensions 'seg' );
Right now declaring extensions at different levels is additive, I
didn't add the option to revoke an extension for a particular server
definition (the "cube,-postgis" entry for example). If that's a
deal-breaker, I can add it too, but it felt like something that could
wait for a user to positively declare "I must have that feature!"
P.
On Fri, Jul 17, 2015 at 5:58 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote:
>
> On July 17, 2015 at 5:57:42 AM, Simon Riggs
> (si...@2ndquadrant.com(mailto:si...@2ndquadrant.com)) wrote:
>
>> Options already exist on CREATE FOREIGN DATA WRAPPER, so it should be easy
>> to support that.
>>
>> I'd rather add it once on the wrapper than be forced to list all the options
>> on every foreign server, unless required to do so.
>
> Gotcha, that does make sense.
>
> P.
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 81cb2b4..9c5136e 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -34,11 +34,15 @@
#include "postgres_fdw.h"
+#include "access/genam.h"
#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "access/transam.h"
+#include "catalog/dependency.h"
+#include "catalog/indexing.h"
#include "catalog/pg_collation.h"
+#include "catalog/pg_depend.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
@@ -49,8 +53,10 @@
#include "optimizer/var.h"
#include "parser/parsetree.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
+#include "utils/snapmgr.h"
#include "utils/syscache.h"
@@ -136,6 +142,7 @@ static void printRemoteParam(int paramindex, Oid paramtype,
int32 paramtypmod,
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
+static bool is_in_extension(Oid procid, PgFdwRelationInfo *fpinfo);
/*
@@ -229,6 +236,9 @@ foreign_expr_walker(Node *node,
Oid collation;
FDWCollateState state;
+ /* Access extension metadata from fpinfo on baserel */
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo
*)(glob_cxt->foreignrel->fdw_private);
+
/* Need do nothing for empty subexpressions */
if (node == NULL)
return true;
@@ -361,7 +371,7 @@ foreign_expr_walker(Node *node,
* can't be sent to remote because it might
have incompatible
* semantics on remote side.
*/
- if (!is_builtin(fe->funcid))
+ if (!is_builtin(fe->funcid) &&
!is_in_extension(fe->funcid, fpinfo))
return false;
/*
@@ -407,7 +417,7 @@ foreign_expr_walker(Node *node,
* (If the operator is, surely its underlying
function is
* too.)
*/
- if (!is_builtin(oe->opno))
+ if (!is_builtin(oe->opno) &&
!is_in_extension(oe->opno, fpinfo))
return false;
/*
@@ -445,7 +455,7 @@ foreign_expr_walker(Node *node,
/*
* Again, only built-in operators can be sent
to remote.
*/
- if (!is_builtin(oe->opno))
+ if (!is_builtin(oe->opno) &&
!is_in_extension(oe->opno, fpinfo))
return false;
/*
@@ -591,7 +601,7 @@ foreign_expr_walker(Node *node,
* If result type of given expression is not built-in, it can't be sent
to
* remote because it might have incompatible semantics on remote side.
*/
- if (check_type && !is_builtin(exprType(node)))
+ if (check_type && !is_builtin(exprType(node)) &&
!is_in_extension(exprType(node), fpinfo))
return false;
/*
@@ -669,6 +679,65 @@ is_builtin(Oid oid)
/*
+ * Returns true if given operator/function is part of an extension declared in
the
+ * server options.
+ */
+static bool
+is_in_extension(Oid procnumber, PgFdwRelationInfo *fpinfo)
+{
+ static int nkeys = 1;
+ ScanKeyData key[nkeys];
+ HeapTuple tup;
+ Relation depRel;
+ SysScanDesc scan;
+ int nresults = 0;
+
+ /* Always return false if we don't have any declared extensions */
+ if ( ! fpinfo->extensions )
+ return false;
+
+ /* We need this relation to scan */
+ depRel = heap_open(DependRelationId, RowExclusiveLock);
+
+ /* Scan the system dependency table for a all entries this operator */
+ /* depends on, then iterate through and see if one of them */
+ /* is a registered extension */
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(procnumber));
+
+ scan = systable_beginscan(depRel, DependDependerIndexId, true,
+
GetCatalogSnapshot(depRel->rd_id), nkeys, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(tup);
+
+ if ( foundDep->deptype == DEPENDENCY_EXTENSION )
+ {
+ List *extlist = fpinfo->extensions;
+ ListCell *ext;
+
+ foreach(ext, extlist)
+ {
+ Oid extension_oid = (Oid) lfirst(ext);
+ if ( foundDep->refobjid == extension_oid )
+ {
+ nresults++;
+ }
+ }
+ }
+ if ( nresults > 0 ) break;
+ }
+
+ systable_endscan(scan);
+ relation_close(depRel, RowExclusiveLock);
+
+ return nresults > 0;
+}
+
+/*
* Construct a simple SELECT statement that retrieves desired columns
* of the specified foreign table, and append it to "buf". The output
* contains just "SELECT ... FROM tablename".
@@ -1404,8 +1473,7 @@ deparseConst(Const *node, deparse_expr_cxt *context)
}
if (needlabel)
appendStringInfo(buf, "::%s",
-
format_type_with_typemod(node->consttype,
-
node->consttypmod));
+ format_type_be_qualified(node->consttype));
}
/*
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 7547ec2..352f50f 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -15,10 +15,13 @@
#include "postgres_fdw.h"
#include "access/reloptions.h"
+#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/extension.h"
+#include "utils/builtins.h"
/*
@@ -124,6 +127,10 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
errmsg("%s requires a
non-negative numeric value",
def->defname)));
}
+ else if (strcmp(def->defname, "extensions") == 0)
+ {
+ extractExtensionList(defGetString(def), NULL);
+ }
}
PG_RETURN_VOID();
@@ -153,6 +160,9 @@ InitPgFdwOptions(void)
/* updatable is available on both server and table */
{"updatable", ForeignServerRelationId, false},
{"updatable", ForeignTableRelationId, false},
+ /* extensions is available on both wrapper and server */
+ {"extensions", ForeignServerRelationId, false},
+ {"extensions", ForeignDataWrapperRelationId, false},
{NULL, InvalidOid, false}
};
@@ -293,3 +303,49 @@ ExtractConnectionOptions(List *defelems, const char
**keywords,
}
return i;
}
+
+bool
+extractExtensionList(char *extensionString, List **extensionOids)
+{
+ List *extlist;
+ ListCell *l, *o;
+
+ if ( ! SplitIdentifierString(extensionString, ',', &extlist) )
+ {
+ list_free(extlist);
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unable to parse extension list \"%s\"",
+ extensionString)));
+ }
+
+ foreach(l, extlist)
+ {
+ const char *extension_name = (const char *) lfirst(l);
+ Oid extension_oid = get_extension_oid(extension_name, true);
+ if ( extension_oid == InvalidOid )
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("the \"%s\" extension must be installed
locally before it can be used on a remote server",
+ extension_name)));
+ }
+ else if ( extensionOids )
+ {
+ bool found = false;
+ /* Only add this extension Oid to the list */
+ /* if we don't already have it */
+ foreach(o, *extensionOids)
+ {
+ Oid oid = (Oid) lfirst(o);
+ if ( oid == extension_oid )
+ found = true;
+ }
+ if ( ! found )
+ *extensionOids = lappend_oid(*extensionOids,
extension_oid);
+ }
+ }
+
+ list_free(extlist);
+ return true;
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.c
b/contrib/postgres_fdw/postgres_fdw.c
index e4d799c..05ca1e0 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "commands/defrem.h"
+#include "commands/extension.h"
#include "commands/explain.h"
#include "commands/vacuum.h"
#include "foreign/fdwapi.h"
@@ -47,39 +48,6 @@ PG_MODULE_MAGIC;
/* Default CPU cost to process 1 row (above and beyond cpu_tuple_cost). */
#define DEFAULT_FDW_TUPLE_COST 0.01
-/*
- * FDW-specific planner information kept in RelOptInfo.fdw_private for a
- * foreign table. This information is collected by postgresGetForeignRelSize.
- */
-typedef struct PgFdwRelationInfo
-{
- /* baserestrictinfo clauses, broken down into safe and unsafe subsets.
*/
- List *remote_conds;
- List *local_conds;
-
- /* Bitmap of attr numbers we need to fetch from the remote server. */
- Bitmapset *attrs_used;
-
- /* Cost and selectivity of local_conds. */
- QualCost local_conds_cost;
- Selectivity local_conds_sel;
-
- /* Estimated size and cost for a scan with baserestrictinfo quals. */
- double rows;
- int width;
- Cost startup_cost;
- Cost total_cost;
-
- /* Options extracted from catalogs. */
- bool use_remote_estimate;
- Cost fdw_startup_cost;
- Cost fdw_tuple_cost;
-
- /* Cached catalog information. */
- ForeignTable *table;
- ForeignServer *server;
- UserMapping *user; /* only set in
use_remote_estimate mode */
-} PgFdwRelationInfo;
/*
* Indexes of FDW-private information stored in fdw_private lists.
@@ -397,6 +365,7 @@ postgresGetForeignRelSize(PlannerInfo *root,
/* Look up foreign-table catalog info. */
fpinfo->table = GetForeignTable(foreigntableid);
fpinfo->server = GetForeignServer(fpinfo->table->serverid);
+ fpinfo->wrapper = GetForeignDataWrapper(fpinfo->server->fdwid);
/*
* Extract user-settable option values. Note that per-table setting of
@@ -405,7 +374,15 @@ postgresGetForeignRelSize(PlannerInfo *root,
fpinfo->use_remote_estimate = false;
fpinfo->fdw_startup_cost = DEFAULT_FDW_STARTUP_COST;
fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
+ fpinfo->extensions = NIL;
+ foreach(lc, fpinfo->wrapper->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "extensions") == 0)
+ extractExtensionList(defGetString(def),
&(fpinfo->extensions));
+ }
foreach(lc, fpinfo->server->options)
{
DefElem *def = (DefElem *) lfirst(lc);
@@ -416,6 +393,8 @@ postgresGetForeignRelSize(PlannerInfo *root,
fpinfo->fdw_startup_cost = strtod(defGetString(def),
NULL);
else if (strcmp(def->defname, "fdw_tuple_cost") == 0)
fpinfo->fdw_tuple_cost = strtod(defGetString(def),
NULL);
+ else if (strcmp(def->defname, "extensions") == 0)
+ extractExtensionList(defGetString(def),
&(fpinfo->extensions));
}
foreach(lc, fpinfo->table->options)
{
diff --git a/contrib/postgres_fdw/postgres_fdw.h
b/contrib/postgres_fdw/postgres_fdw.h
index 3835ddb..87e0bc5 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -20,6 +20,44 @@
#include "libpq-fe.h"
+/*
+ * FDW-specific planner information kept in RelOptInfo.fdw_private for a
+ * foreign table. This information is collected by postgresGetForeignRelSize.
+ */
+typedef struct PgFdwRelationInfo
+{
+ /* baserestrictinfo clauses, broken down into safe and unsafe subsets.
*/
+ List *remote_conds;
+ List *local_conds;
+
+ /* Bitmap of attr numbers we need to fetch from the remote server. */
+ Bitmapset *attrs_used;
+
+ /* Cost and selectivity of local_conds. */
+ QualCost local_conds_cost;
+ Selectivity local_conds_sel;
+
+ /* Estimated size and cost for a scan with baserestrictinfo quals. */
+ double rows;
+ int width;
+ Cost startup_cost;
+ Cost total_cost;
+
+ /* Options extracted from catalogs. */
+ bool use_remote_estimate;
+ Cost fdw_startup_cost;
+ Cost fdw_tuple_cost;
+
+ /* Optional extensions to support (list of oid) */
+ List *extensions;
+
+ /* Cached catalog information. */
+ ForeignDataWrapper *wrapper;
+ ForeignTable *table;
+ ForeignServer *server;
+ UserMapping *user; /* only set in
use_remote_estimate mode */
+} PgFdwRelationInfo;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -37,6 +75,8 @@ extern void pgfdw_report_error(int elevel, PGresult *res,
PGconn *conn,
extern int ExtractConnectionOptions(List *defelems,
const char **keywords,
const char **values);
+extern bool extractExtensionList(char *extensionString,
+ List **extensionOids);
/* in deparse.c */
extern void classifyConditions(PlannerInfo *root,
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 14b12e3..7b3d8c7 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -373,6 +373,37 @@
foreign tables, see <xref linkend="sql-createforeigntable">.
</para>
</sect3>
+
+ <sect3>
+ <title>Extension Options</title>
+
+ <para>
+ By default only built-in operators and functions will be sent from the
+ local to the foreign server. This may be overridden using the following
option:
+ </para>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>extensions</literal></term>
+ <listitem>
+ <para>
+ This option allows you to declare what extensions you expect are
+ installed on the foreign server, using a comma-separated list of
+ extension names. The extensions are also expected to be installed
+ on the local server too.
+ </para>
+<programlisting>
+CREATE SERVER foreign_server
+ FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (host '127.0.0.1', port '5432', dbname 'my_db', extensions
'cube, seg');
+</programlisting>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect3>
+
</sect2>
<sect2>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
