Hi all,
Attached is a patch that implements the extension support discussed at
PgCon this year during the FDW unconference sesssion. Highlights:
* Pass extension operators and functions to the foreign server
* Only send ops/funcs if the foreign server is declared to support the
relevant extension, for example:
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5432', dbname 'my_db',
extensions 'cube, seg');
Github branch is here:
https://github.com/pramsey/postgres/tree/fdw-extension-suppport
Synthetic pull request for easy browsing/commenting is here:
https://github.com/pramsey/postgres/pull/1
Thanks!
Paul
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 81cb2b4..bbe3c9d 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);
/*
@@ -167,6 +174,7 @@ classifyConditions(PlannerInfo *root,
}
}
+
/*
* Returns true if given expr is safe to evaluate on the foreign server.
*/
@@ -177,7 +185,7 @@ is_foreign_expr(PlannerInfo *root,
{
foreign_glob_cxt glob_cxt;
foreign_loc_cxt loc_cxt;
-
+
/*
* Check that the expression consists of nodes that are safe to execute
* remotely.
@@ -207,6 +215,8 @@ is_foreign_expr(PlannerInfo *root,
return true;
}
+
+
/*
* Check if expression is safe to execute remotely, and return true if so.
*
@@ -229,6 +239,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 +374,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 +420,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 +458,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 +604,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;
/*
@@ -643,6 +656,8 @@ foreign_expr_walker(Node *node,
return true;
}
+
+
/*
* Return true if given object is one of PostgreSQL's built-in objects.
*
@@ -669,6 +684,67 @@ 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 +1480,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..29a3731 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -19,6 +19,8 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/extension.h"
+#include "utils/builtins.h"
/*
@@ -105,7 +107,7 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
* Validate option value, when we can do so without any context.
*/
if (strcmp(def->defname, "use_remote_estimate") == 0 ||
- strcmp(def->defname, "updatable") == 0)
+ strcmp(def->defname, "updatable") == 0 )
{
/* these accept only boolean values */
(void) defGetBoolean(def);
@@ -124,6 +126,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 +159,8 @@ InitPgFdwOptions(void)
/* updatable is available on both server and table */
{"updatable", ForeignServerRelationId, false},
{"updatable", ForeignTableRelationId, false},
+ /* use_remote_estimate is available on both server and table */
+ {"extensions", ForeignServerRelationId, false},
{NULL, InvalidOid, false}
};
@@ -293,3 +301,46 @@ ExtractConnectionOptions(List *defelems, const char
**keywords,
}
return i;
}
+
+
+bool
+extractExtensionList(char *extensionString, List **extensionOids)
+{
+ List *extlist;
+ ListCell *l;
+
+ if ( ! SplitIdentifierString(extensionString, ',', &extlist) )
+ {
+ list_free(extlist);
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unable to parse extension list '%s'",
+ extensionString)));
+ }
+
+ if ( extensionOids )
+ *extensionOids = NIL;
+
+ foreach(l, extlist)
+ {
+ char *extension_name = (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 )
+ {
+ *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..2b1c240 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.
@@ -416,16 +384,15 @@ 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)
{
DefElem *def = (DefElem *) lfirst(lc);
if (strcmp(def->defname, "use_remote_estimate") == 0)
- {
fpinfo->use_remote_estimate = defGetBoolean(def);
- break; /* only need the one
value */
- }
}
/*
@@ -2994,3 +2961,5 @@ conversion_error_callback(void *arg)
NameStr(tupdesc->attrs[errpos->cur_attno -
1]->attname),
RelationGetRelationName(errpos->rel));
}
+
+
diff --git a/contrib/postgres_fdw/postgres_fdw.h
b/contrib/postgres_fdw/postgres_fdw.h
index 3835ddb..d27ed37 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -20,6 +20,46 @@
#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;
+
+ /* PostGIS metadata */
+ List *extensions;
+ bool use_postgis;
+ Oid postgis_oid;
+
+ /* Cached catalog information. */
+ 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 +77,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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
