[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] [PATCH] postgres_fdw extension support
From: Paul Ramsey <pramsey () cleverelephant ! ca>
Date: 2015-09-30 22:57:58
Message-ID: etPan.560c68f8.8edbdab.14269 () Crane ! local
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
On September 30, 2015 at 3:32:21 PM, Michael Paquier (michael.paquier@gmail.com) wrote:
OK. Once you can get a new patch done with a reworked
extractExtensionList, I'll get a new look at it in a timely fashion
and then let's move it to a committer's hands.
Done and thanks!
P
--
http://postgis.net
http://cleverelephant.ca
[Attachment #5 (text/html)]
<html><head><style>body{font-family:Helvetica,Arial;font-size:13px}</style></head><body \
style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: \
after-white-space;"><div id="bloop_customfont" \
style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: \
0px; line-height: auto;"> On September 30, 2015 at 3:32:21 PM, Michael Paquier \
(<a href="mailto:michael.paquier@gmail.com">michael.paquier@gmail.com</a>) \
wrote:</div> <blockquote type="cite" class="clean_bq"><span><div><div></div><div>OK. \
Once you can get a new patch done with a reworked <br>extractExtensionList, I'll get \
a new look at it in a timely fashion <br>and then let's move it to a committer's \
hands. <br></div></div></span></blockquote><div><br></div>Done and \
thanks!<div>P<br><div id="bloop_sign_1443653847609857024" \
class="bloop_sign"><div><br></div><span \
style="font-family:helvetica,arial;font-size:13px"></span>-- <br><span>http://pos \
tgis.net</span><div><span>http://cleverelephant.ca</span></div><div><br></div></div></div></body></html>
["fdw-extension-support8.diff" (application/octet-stream)]
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index d2b98e1..f78fc64 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,9 @@ SHLIB_LINK = $(libpq)
EXTENSION = postgres_fdw
DATA = postgres_fdw--1.0.sql
-REGRESS = postgres_fdw
+# Note: shippable tests depend on postgres_fdw tests setup
+REGRESS = postgres_fdw shippable
+EXTRA_INSTALL = contrib/cube
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..54749a3
--- /dev/null
+++ b/contrib/postgres_fdw/expected/shippable.out
@@ -0,0 +1,138 @@
+-- ===================================================================
+-- create FDW objects
+-- ===================================================================
+-- Error, extension isn't installed yet
+ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
+ERROR: the "cube" extension must be installed locally before it can be used on a \
remote server +-- Try again
+CREATE EXTENSION cube;
+ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
+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 cube,
+ c4 timestamptz
+);
+INSERT INTO "SH 1"."TBL 1"
+ SELECT id,
+ 2 * id,
+ cube(id,2*id),
+ '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 cube,
+ 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 && cube(1.5,2.5);
+ 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)'::cube)
+ Remote SQL: SELECT c2, c3 FROM "SH 1"."TBL 1"
+(4 rows)
+
+SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
+ c2
+----
+ 2
+ 4
+(2 rows)
+
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+ 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)'::cube)
+ Remote SQL: SELECT c2, c3 FROM "SH 1"."TBL 1"
+(4 rows)
+
+-- with operator shipping
+ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
+ 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.cube)) +(3 rows)
+
+SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
+ c2
+----
+ 2
+ 4
+(2 rows)
+
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+ 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.cube)) +(3 rows)
+
+EXPLAIN VERBOSE SELECT cube_dim(c3) FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+ QUERY PLAN \
+---------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.shft1 (cost=100.00..128.43 rows=7 width=32)
+ Output: cube_dim(c3)
+ Remote SQL: SELECT c3 FROM "SH 1"."TBL 1" WHERE ((c3 OPERATOR(public.&&) \
'(1.5),(2.5)'::public.cube)) +(3 rows)
+
+SELECT cube_dim(c3) FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+ cube_dim
+----------
+ 1
+ 1
+(2 rows)
+
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE cube_dim(c3) = 1 LIMIT 2;
+ QUERY PLAN \
+-------------------------------------------------------------------------------------
+ Limit (cost=100.00..107.22 rows=2 width=4)
+ Output: c2
+ -> Foreign Scan on public.shft1 (cost=100.00..154.18 rows=15 width=4)
+ Output: c2
+ Remote SQL: SELECT c2 FROM "SH 1"."TBL 1" WHERE ((public.cube_dim(c3) = 1))
+(5 rows)
+
+SELECT c2 FROM shft1 WHERE cube_dim(c3) = 1 LIMIT 2;
+ c2
+----
+ 2
+ 4
+(2 rows)
+
+-- ===================================================================
+-- clean up
+-- ===================================================================
+DROP FOREIGN TABLE shft1;
+DROP TABLE "SH 1"."TBL 1";
+DROP SCHEMA "SH 1";
+DROP EXTENSION cube;
+ALTER SERVER loopback OPTIONS (DROP extensions);
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 7547ec2..beb61ba 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), false);
+ }
}
PG_RETURN_VOID();
@@ -153,6 +158,8 @@ InitPgFdwOptions(void)
/* updatable is available on both server and table */
{"updatable", ForeignServerRelationId, false},
{"updatable", ForeignTableRelationId, false},
+ /* extensions is available on server */
+ {"extensions", ForeignServerRelationId, false},
{NULL, InvalidOid, false}
};
diff --git a/contrib/postgres_fdw/postgres_fdw.c \
b/contrib/postgres_fdw/postgres_fdw.c index e4d799c..eb5eb67 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,6 +373,7 @@ 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->server->options)
{
@@ -416,6 +385,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)
+ fpinfo->extensions = extractExtensionList(defGetString(def), true);
}
foreach(lc, fpinfo->table->options)
{
diff --git a/contrib/postgres_fdw/postgres_fdw.h \
b/contrib/postgres_fdw/postgres_fdw.h index 3835ddb..98d9fea 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,10 @@ extern int ExtractConnectionOptions(List *defelems,
const char **keywords,
const char **values);
+/* in shippable.c */
+extern List* extractExtensionList(char *extensionString, bool populateList);
+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..0e2aa77
--- /dev/null
+++ b/contrib/postgres_fdw/shippable.c
@@ -0,0 +1,263 @@
+/*-------------------------------------------------------------------------
+ *
+ * shippable.c
+ * Facility to track database objects shippable to a foreign server.
+ *
+ * Determine if functions and operators for non-built-in types/functions/ops
+ * are shippable to the remote 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 return a List
+ * of 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.
+ */
+List *
+extractExtensionList(char *extensionString, bool populateList)
+{
+ List *extlist;
+ List *extensionOids = NIL;
+ 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 (populateList)
+ {
+ /*
+ * 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 extensionOids;
+}
diff --git a/contrib/postgres_fdw/sql/shippable.sql \
b/contrib/postgres_fdw/sql/shippable.sql new file mode 100644
index 0000000..9ea6fc0
--- /dev/null
+++ b/contrib/postgres_fdw/sql/shippable.sql
@@ -0,0 +1,77 @@
+-- ===================================================================
+-- create FDW objects
+-- ===================================================================
+
+-- Error, extension isn't installed yet
+ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
+
+-- Try again
+CREATE EXTENSION cube;
+ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
+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 cube,
+ c4 timestamptz
+);
+
+INSERT INTO "SH 1"."TBL 1"
+ SELECT id,
+ 2 * id,
+ cube(id,2*id),
+ '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 cube,
+ 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 && cube(1.5,2.5);
+SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+
+-- with operator shipping
+ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
+SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+EXPLAIN VERBOSE SELECT cube_dim(c3) FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+SELECT cube_dim(c3) FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
+
+EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE cube_dim(c3) = 1 LIMIT 2;
+SELECT c2 FROM shft1 WHERE cube_dim(c3) = 1 LIMIT 2;
+
+-- ===================================================================
+-- clean up
+-- ===================================================================
+
+DROP FOREIGN TABLE shft1;
+DROP TABLE "SH 1"."TBL 1";
+DROP SCHEMA "SH 1";
+DROP EXTENSION cube;
+ALTER SERVER loopback OPTIONS (DROP extensions);
+
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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic