Back from summer and conferencing, and finally responding, sorry for
the delay...
On Thu, Aug 20, 2015 at 6:01 PM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
>
>
> if (needlabel)
> appendStringInfo(buf, "::%s",
> -
> format_type_with_typemod(node->consttype,
> -
> node->consttypmod));
> +
> format_type_be_qualified(node->consttype));
> Pondering more about this one, I think that we are going to need a new
> routine in format_type.c to be able to call format_type_internal as
> format_type_internal(type_oid, typemod, true/false, false, true). If typemod
> is -1, then typemod_given (the third argument) is false, otherwise
> typemod_given is true. That's close to what the C function format_type at
> the SQL level can do except that we want it to be qualified. Regression
> tests will need an update as well.
I ended up switching on whether the type being formatted was an
extension type or not. Extension types need to be fully qualified or
they won't get found by the remote. Conversely if you fully qualify
the built-in types, the regression test for postgres_fdw isn't happy.
This still isn't quite the best/final solution, perhaps something as
simple as this would work, but I'm not sure:
src/backend/utils/adt/format_type.c
+/*
+ * This version allows a nondefault typemod to be specified and fully
qualified.
+ */
+char *
+format_type_with_typemod_qualified(Oid type_oid, int32 typemod)
+{
+ return format_type_internal(type_oid, typemod, true, false, true);
+}
> Comment format is incorrect, this should be written like that:
Comments fixed.
> + if (!extension_list)
> + return false;
> I would rather mark that as == NIL instead, NIL is what an empty list is.
Done
> +extern bool is_shippable(Oid procnumber, PgFdwRelationInfo *fpinfo);
> There is no need to pass PgFdwRelationInfo to is_shippable. Only the list of
> extension OIDs is fine.
Done
> That's nitpicking, but normally we avoid more than 80 characters per line of
> code.
Done.
> When attempting to create a server when an extension is not installed we get
> an appropriate error:
> =# CREATE SERVER postgres_server
> FOREIGN DATA WRAPPER postgres_fdw
> OPTIONS (host 'localhost', port '5432', dbname 'postgres', extensions
> 'seg');
> ERROR: 42601: the "seg" extension must be installed locally before it can
> be used on a remote server
> LOCATION: extractExtensionList, shippable.c:245
> However, it is possible to drop an extension listed in a postgres_fdw
> server. Shouldn't we invalidate cache as well when pg_extension is updated?
> Thoughts?
For extensions with types, it's pretty hard to pull the extension out
from under the FDW, because the tables using the type will depend on
it. For simpler function-only extensions, it's possible, but as soon
as you pull the extension out and run a query, your FDW will notice
the extension is missing and complain. And then you'll have to update
the foreign server or foreign table entries and the cache will get
flushed. So there's no way to get a stale cache.
> + list_free(extlist);
> It does not matter much to call list_free here. The list is going to be
> freed in the error context with ERROR.
Done.
> + foreach(ext, extension_list)
> + {
> + Oid extension_oid = (Oid) lfirst(ext);
> + if (foundDep->refobjid == extension_oid)
> + {
> + nresults++;
> + }
> + }
> You could just use list_member_oid here. And why not just breaking the loop
> once there is a match? This is doing unnecessary work visibly
Done.
> + 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:
> Missing a mention to "data types" here?
Actually extension data types traverse the postgres_fdw without
trouble without this patch, as long as both sides have the extension
installed. So not strictly needed to mention data types.
> It would be good to get some regression tests for this feature, which is
> something doable with the flag EXTRA_INSTALL and some tests with EXPLAIN
> VERBOSE. Note that you will need to use CREATE EXTENSION to make the
> extension available in the new test, which should be I imagine a new file
> like shippable.sql.
I've put a very very small regression file in that tests the shippable
feature, which can be fleshed out further as needed.
Thanks so much!
P.
> Regards,
> --
> Michael
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index d2b98e1..63576c4 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -1,7 +1,7 @@
# contrib/postgres_fdw/Makefile
MODULE_big = postgres_fdw
-OBJS = postgres_fdw.o option.o deparse.o connection.o $(WIN32RES)
+OBJS = postgres_fdw.o option.o deparse.o connection.o shippable.o $(WIN32RES)
PGFILEDESC = "postgres_fdw - foreign data wrapper for PostgreSQL"
PG_CPPFLAGS = -I$(libpq_srcdir)
@@ -10,7 +10,8 @@ SHLIB_LINK = $(libpq)
EXTENSION = postgres_fdw
DATA = postgres_fdw--1.0.sql
-REGRESS = postgres_fdw
+REGRESS = postgres_fdw shippable
+EXTRA_INSTALL = contrib/seg
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 697de60..897ecdb 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -233,6 +233,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;
@@ -378,7 +381,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_shippable(fe->funcid, fpinfo->extensions))
return false;
/*
@@ -426,7 +429,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_shippable(oe->opno, fpinfo->extensions))
return false;
/*
@@ -466,7 +469,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_shippable(oe->opno, fpinfo->extensions))
return false;
/*
@@ -616,7 +619,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_shippable(exprType(node), fpinfo->extensions))
return false;
/*
@@ -1351,6 +1354,9 @@ deparseConst(Const *node, deparse_expr_cxt *context)
bool isfloat = false;
bool needlabel;
+ /* Access extension metadata from fpinfo on baserel */
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo
*)(context->foreignrel->fdw_private);
+
if (node->constisnull)
{
appendStringInfoString(buf, "NULL");
@@ -1428,9 +1434,16 @@ deparseConst(Const *node, deparse_expr_cxt *context)
break;
}
if (needlabel)
+ {
+ /*
+ * References to extension types need to be fully qualified,
+ * but references to built-in types shouldn't be.
+ */
appendStringInfo(buf, "::%s",
-
format_type_with_typemod(node->consttype,
-
node->consttypmod));
+ is_shippable(node->consttype, fpinfo->extensions) ?
+ format_type_be_qualified(node->consttype) :
+ format_type_with_typemod(node->consttype,
node->consttypmod));
+ }
}
/*
diff --git a/contrib/postgres_fdw/expected/shippable.out
b/contrib/postgres_fdw/expected/shippable.out
new file mode 100644
index 0000000..1787830
--- /dev/null
+++ b/contrib/postgres_fdw/expected/shippable.out
@@ -0,0 +1,84 @@
+-- ===================================================================
+-- create FDW objects
+-- ===================================================================
+-- Error, extension isn't installed yet
+ALTER SERVER loopback OPTIONS (ADD extensions 'seg');
+ERROR: the "seg" extension must be installed locally before it can be used on
a remote server
+-- Try again
+CREATE EXTENSION seg;
+ALTER SERVER loopback OPTIONS (ADD extensions 'seg');
+ALTER SERVER loopback OPTIONS (DROP extensions);
+-- ===================================================================
+-- create objects used through FDW loopback server
+-- ===================================================================
+CREATE SCHEMA "SH 1";
+CREATE TABLE "SH 1"."TBL 1" (
+ "C 1" int NOT NULL,
+ c2 int NOT NULL,
+ c3 seg,
+ c4 timestamptz
+);
+INSERT INTO "SH 1"."TBL 1"
+ SELECT id,
+ 2 * id,
+ (id || ' .. ' || 2*id)::seg,
+ '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval
+ FROM generate_series(1, 1000) id;
+ANALYZE "SH 1"."TBL 1";
+-- ===================================================================
+-- create foreign table
+-- ===================================================================
+CREATE FOREIGN TABLE shft1 (
+ "C 1" int NOT NULL,
+ c2 int NOT NULL,
+ c3 seg,
+ c4 timestamptz
+) SERVER loopback
+OPTIONS (schema_name 'SH 1', table_name 'TBL 1');
+-- ===================================================================
+-- simple queries
+-- ===================================================================
+-- without operator shipping
+EXPLAIN (COSTS false) SELECT * FROM shft1 LIMIT 1;
+ QUERY PLAN
+-----------------------------
+ Limit
+ -> Foreign Scan on shft1
+(2 rows)
+
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Foreign Scan on public.shft1 (cost=100.00..205.06 rows=15 width=4)
+ Output: c2
+ Filter: (shft1.c3 && '1.5 .. 2.5'::seg)
+ Remote SQL: SELECT c2, c3 FROM "SH 1"."TBL 1"
+(4 rows)
+
+SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+ c2
+----
+ 2
+ 4
+(2 rows)
+
+-- with operator shipping
+ALTER SERVER loopback OPTIONS (ADD extensions 'seg');
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.shft1 (cost=100.00..146.86 rows=15 width=4)
+ Output: c2
+ Remote SQL: SELECT c2 FROM "SH 1"."TBL 1" WHERE ((c3 OPERATOR(public.&&)
'1.5 .. 2.5'::public.seg))
+(3 rows)
+
+SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+ c2
+----
+ 2
+ 4
+(2 rows)
+
+-- ===================================================================
+-- clean up
+-- ===================================================================
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 7547ec2..9aeaf1a 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -15,6 +15,7 @@
#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"
@@ -124,6 +125,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 +158,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}
};
diff --git a/contrib/postgres_fdw/postgres_fdw.c
b/contrib/postgres_fdw/postgres_fdw.c
index e4d799c..42d7e25 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -47,39 +47,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 +364,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 +373,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 +392,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..a264e49 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);
@@ -38,6 +76,11 @@ extern int ExtractConnectionOptions(List *defelems,
const char **keywords,
const char **values);
+/* in shippable.c */
+extern bool extractExtensionList(char *extensionString,
+ List **extensionOids);
+extern bool is_shippable(Oid procnumber, List *extension_list);
+
/* in deparse.c */
extern void classifyConditions(PlannerInfo *root,
RelOptInfo *baserel,
diff --git a/contrib/postgres_fdw/shippable.c b/contrib/postgres_fdw/shippable.c
new file mode 100644
index 0000000..65533f7
--- /dev/null
+++ b/contrib/postgres_fdw/shippable.c
@@ -0,0 +1,263 @@
+/*-------------------------------------------------------------------------
+ *
+ * shippable.c
+ * Non-built-in objects cache management and utilities.
+ *
+ * Is a non-built-in shippable to the remote server? Only if
+ * the object is in an extension declared by the user in the
+ * OPTIONS of the wrapper or the server.
+ *
+ * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/postgres_fdw/shippable.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "postgres_fdw.h"
+
+#include "access/genam.h"
+#include "access/heapam.h"
+#include "access/htup_details.h"
+#include "catalog/dependency.h"
+#include "catalog/indexing.h"
+#include "catalog/pg_depend.h"
+#include "commands/extension.h"
+#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/hsearch.h"
+#include "utils/inval.h"
+#include "utils/rel.h"
+#include "utils/snapmgr.h"
+#include "utils/syscache.h"
+
+/* Hash table for informations about remote objects we'll call */
+static HTAB *ShippableCacheHash = NULL;
+
+/* objid is the lookup key, must appear first */
+typedef struct
+{
+ Oid objid;
+} ShippableCacheKey;
+
+typedef struct
+{
+ /* lookup key - must be first */
+ ShippableCacheKey key;
+ /* extension the object appears within, or InvalidOid if none */
+ bool shippable;
+} ShippableCacheEntry;
+
+/*
+ * InvalidateShippableCacheCallback
+ * Flush all cache entries when pg_foreign_data_wrapper
+ * or pg_foreign_server is updated.
+ */
+static void
+InvalidateShippableCacheCallback(Datum arg, int cacheid, uint32 hashvalue)
+{
+ HASH_SEQ_STATUS status;
+ ShippableCacheEntry *entry;
+
+ hash_seq_init(&status, ShippableCacheHash);
+ while ((entry = (ShippableCacheEntry *) hash_seq_search(&status)) !=
NULL)
+ {
+ if (hash_search(ShippableCacheHash,
+ (void *) &entry->key,
+ HASH_REMOVE,
+ NULL) == NULL)
+ elog(ERROR, "hash table corrupted");
+ }
+}
+
+/*
+ * InitializeShippableCache
+ * Initialize the cache of functions we can ship to remote server.
+ */
+static void
+InitializeShippableCache(void)
+{
+ HASHCTL ctl;
+
+ /* Initialize the hash table. */
+ MemSet(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(ShippableCacheKey);
+ ctl.entrysize = sizeof(ShippableCacheEntry);
+ ShippableCacheHash =
+ hash_create("Shippable cache", 256, &ctl, HASH_ELEM);
+
+ /* Watch for invalidation events. */
+ CacheRegisterSyscacheCallback(FOREIGNDATAWRAPPEROID,
+
InvalidateShippableCacheCallback,
+ (Datum) 0);
+
+ CacheRegisterSyscacheCallback(FOREIGNSERVEROID,
+
InvalidateShippableCacheCallback,
+ (Datum) 0);
+}
+
+/*
+ * Returns true if given operator/function is part of an extension declared in
+ * the server options.
+ */
+static bool
+lookup_shippable(Oid objnumber, List *extension_list)
+{
+ static int nkeys = 1;
+ ScanKeyData key[nkeys];
+ HeapTuple tup;
+ Relation depRel;
+ SysScanDesc scan;
+ bool is_shippable = false;
+
+ /* Always return false if we don't have any declared extensions */
+ if (extension_list == NIL)
+ return false;
+
+ /* We need this relation to scan */
+ depRel = heap_open(DependRelationId, RowExclusiveLock);
+
+ /*
+ * Scan the system dependency table for all entries this object
+ * depends on, then iterate through and see if one of them
+ * is an extension declared by the user in the options
+ */
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(objnumber));
+
+ 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_member_oid(extension_list, foundDep->refobjid))
+ {
+ is_shippable = true;
+ break;
+ }
+ }
+
+ systable_endscan(scan);
+ relation_close(depRel, RowExclusiveLock);
+
+ return is_shippable;
+}
+
+/*
+ * is_shippable
+ * Is this object (proc/op/type) shippable to foreign server?
+ * Check cache first, then look-up whether (proc/op/type) is
+ * part of a declared extension if it is not cached.
+ */
+bool
+is_shippable(Oid objnumber, List *extension_list)
+{
+ ShippableCacheKey key;
+ ShippableCacheEntry *entry;
+
+ /* Always return false if we don't have any declared extensions */
+ if (extension_list == NIL)
+ return false;
+
+ /* Find existing cache, if any. */
+ if (!ShippableCacheHash)
+ InitializeShippableCache();
+
+ /* Zero out the key */
+ memset(&key, 0, sizeof(key));
+
+ key.objid = objnumber;
+
+ entry = (ShippableCacheEntry *)
+ hash_search(ShippableCacheHash,
+ (void *) &key,
+ HASH_FIND,
+ NULL);
+
+ /* Not found in ShippableCacheHash cache. Construct new entry. */
+ if (!entry)
+ {
+ /*
+ * Right now "shippability" is exclusively a function of whether
+ * the obj (proc/op/type) is in an extension declared by the
user.
+ * In the future we could additionally have a whitelist of
functions
+ * declared one at a time.
+ */
+ bool shippable = lookup_shippable(objnumber, extension_list);
+
+ entry = (ShippableCacheEntry *)
+ hash_search(ShippableCacheHash,
+ (void *) &key,
+ HASH_ENTER,
+ NULL);
+
+ entry->shippable = shippable;
+ }
+
+ if (!entry)
+ return false;
+ else
+ return entry->shippable;
+}
+
+/*
+ * extractExtensionList
+ * Parse a comma-separated string and fill out the list
+ * argument with the Oids of the extensions in the string.
+ * If an extenstion provided cannot be looked up in the
+ * catalog (it hasn't been installed or doesn't exist)
+ * then throw an error.
+ */
+bool
+extractExtensionList(char *extensionString, List **extensionOids)
+{
+ List *extlist;
+ ListCell *l;
+
+ if (!SplitIdentifierString(extensionString, ',', &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)));
+ }
+ /*
+ * Option validation calls this function with NULL in the
+ * extensionOids parameter, to just do existence/syntax
+ * checking of the option
+ */
+ else if (extensionOids)
+ {
+ /*
+ * Only add this extension Oid to the list
+ * if we don't already have it in the list
+ */
+ if (!list_member_oid(*extensionOids, extension_oid))
+ *extensionOids = lappend_oid(*extensionOids,
extension_oid);
+ }
+ }
+
+ list_free(extlist);
+ return true;
+}
diff --git a/contrib/postgres_fdw/sql/shippable.sql
b/contrib/postgres_fdw/sql/shippable.sql
new file mode 100644
index 0000000..3d24d97
--- /dev/null
+++ b/contrib/postgres_fdw/sql/shippable.sql
@@ -0,0 +1,67 @@
+-- ===================================================================
+-- create FDW objects
+-- ===================================================================
+
+-- Error, extension isn't installed yet
+ALTER SERVER loopback OPTIONS (ADD extensions 'seg');
+
+-- Try again
+CREATE EXTENSION seg;
+ALTER SERVER loopback OPTIONS (ADD extensions 'seg');
+ALTER SERVER loopback OPTIONS (DROP extensions);
+
+
+-- ===================================================================
+-- create objects used through FDW loopback server
+-- ===================================================================
+
+CREATE SCHEMA "SH 1";
+CREATE TABLE "SH 1"."TBL 1" (
+ "C 1" int NOT NULL,
+ c2 int NOT NULL,
+ c3 seg,
+ c4 timestamptz
+);
+
+INSERT INTO "SH 1"."TBL 1"
+ SELECT id,
+ 2 * id,
+ (id || ' .. ' || 2*id)::seg,
+ '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval
+ FROM generate_series(1, 1000) id;
+
+ANALYZE "SH 1"."TBL 1";
+
+-- ===================================================================
+-- create foreign table
+-- ===================================================================
+
+CREATE FOREIGN TABLE shft1 (
+ "C 1" int NOT NULL,
+ c2 int NOT NULL,
+ c3 seg,
+ c4 timestamptz
+) SERVER loopback
+OPTIONS (schema_name 'SH 1', table_name 'TBL 1');
+
+-- ===================================================================
+-- simple queries
+-- ===================================================================
+
+-- without operator shipping
+EXPLAIN (COSTS false) SELECT * FROM shft1 LIMIT 1;
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+
+-- with operator shipping
+ALTER SERVER loopback OPTIONS (ADD extensions 'seg');
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+SELECT c2 FROM shft1 WHERE c3 && '1.5 .. 2.5'::seg;
+
+
+-- ===================================================================
+-- clean up
+-- ===================================================================
+
+
+
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 7c92282..6e7fcf7 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -373,6 +373,40 @@
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. The option is available on the wrapper and
+ on servers.
+ </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');
+
+ALTER FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( SET extensions '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
