pgsql_fdw_v16.patch
text/plain
Filename: pgsql_fdw_v16.patch
Type: text/plain
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
Series: patch v16
| File | + | − |
|---|---|---|
| contrib/Makefile | 0 | 0 |
| contrib/pgsql_fdw/connection.c | 0 | 0 |
| contrib/pgsql_fdw/connection.h | 0 | 0 |
| contrib/pgsql_fdw/deparse.c | 0 | 0 |
| contrib/pgsql_fdw/expected/pgsql_fdw.out | 0 | 0 |
| contrib/pgsql_fdw/.gitignore | 0 | 0 |
| contrib/pgsql_fdw/Makefile | 0 | 0 |
| contrib/pgsql_fdw/option.c | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw--1.0.sql | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw.c | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw.control | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw.h | 0 | 0 |
| contrib/pgsql_fdw/sql/pgsql_fdw.sql | 0 | 0 |
| contrib/README | 0 | 0 |
| doc/src/sgml/contrib.sgml | 0 | 0 |
| doc/src/sgml/filelist.sgml | 0 | 0 |
| doc/src/sgml/pgsql-fdw.sgml | 0 | 0 |
diff --git a/contrib/Makefile b/contrib/Makefile
index ac0a80a..14aa433 100644
*** a/contrib/Makefile
--- b/contrib/Makefile
*************** SUBDIRS = \
*** 41,46 ****
--- 41,47 ----
pgbench \
pgcrypto \
pgrowlocks \
+ pgsql_fdw \
pgstattuple \
seg \
spi \
diff --git a/contrib/README b/contrib/README
index a1d42a1..d3fa211 100644
*** a/contrib/README
--- b/contrib/README
*************** pgrowlocks -
*** 158,163 ****
--- 158,167 ----
A function to return row locking information
by Tatsuo Ishii <ishii@sraoss.co.jp>
+ pgsql_fdw -
+ Foreign-data wrapper for external PostgreSQL servers.
+ by Shigeru Hanada <shigeru.hanada@gmail.com>
+
pgstattuple -
Functions to return statistics about "dead" tuples and free
space within a table
diff --git a/contrib/pgsql_fdw/.gitignore b/contrib/pgsql_fdw/.gitignore
index ...0854728 .
*** a/contrib/pgsql_fdw/.gitignore
--- b/contrib/pgsql_fdw/.gitignore
***************
*** 0 ****
--- 1,4 ----
+ # Generated subdirectories
+ /results/
+ *.o
+ *.so
diff --git a/contrib/pgsql_fdw/Makefile b/contrib/pgsql_fdw/Makefile
index ...6381365 .
*** a/contrib/pgsql_fdw/Makefile
--- b/contrib/pgsql_fdw/Makefile
***************
*** 0 ****
--- 1,22 ----
+ # contrib/pgsql_fdw/Makefile
+
+ MODULE_big = pgsql_fdw
+ OBJS = pgsql_fdw.o option.o deparse.o connection.o
+ PG_CPPFLAGS = -I$(libpq_srcdir)
+ SHLIB_LINK = $(libpq)
+
+ EXTENSION = pgsql_fdw
+ DATA = pgsql_fdw--1.0.sql
+
+ REGRESS = pgsql_fdw
+
+ ifdef USE_PGXS
+ PG_CONFIG = pg_config
+ PGXS := $(shell $(PG_CONFIG) --pgxs)
+ include $(PGXS)
+ else
+ subdir = contrib/pgsql_fdw
+ top_builddir = ../..
+ include $(top_builddir)/src/Makefile.global
+ include $(top_srcdir)/contrib/contrib-global.mk
+ endif
diff --git a/contrib/pgsql_fdw/connection.c b/contrib/pgsql_fdw/connection.c
index ...c971bd7 .
*** a/contrib/pgsql_fdw/connection.c
--- b/contrib/pgsql_fdw/connection.c
***************
*** 0 ****
--- 1,555 ----
+ /*-------------------------------------------------------------------------
+ *
+ * connection.c
+ * Connection management for pgsql_fdw
+ *
+ * Portions Copyright (c) 2012, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pgsql_fdw/connection.c
+ *
+ *-------------------------------------------------------------------------
+ */
+ #include "postgres.h"
+
+ #include "access/xact.h"
+ #include "catalog/pg_type.h"
+ #include "foreign/foreign.h"
+ #include "funcapi.h"
+ #include "libpq-fe.h"
+ #include "mb/pg_wchar.h"
+ #include "miscadmin.h"
+ #include "utils/array.h"
+ #include "utils/builtins.h"
+ #include "utils/hsearch.h"
+ #include "utils/memutils.h"
+ #include "utils/resowner.h"
+ #include "utils/tuplestore.h"
+
+ #include "pgsql_fdw.h"
+ #include "connection.h"
+
+ /* ============================================================================
+ * Connection management functions
+ * ==========================================================================*/
+
+ /*
+ * Connection cache entry managed with hash table.
+ */
+ typedef struct ConnCacheEntry
+ {
+ /* hash key must be first */
+ Oid serverid; /* oid of foreign server */
+ Oid userid; /* oid of local user */
+
+ bool use_tx; /* true when using remote transaction */
+ int refs; /* reference counter */
+ PGconn *conn; /* foreign server connection */
+ } ConnCacheEntry;
+
+ /*
+ * Hash table which is used to cache connection to PostgreSQL servers, will be
+ * initialized before first attempt to connect PostgreSQL server by the backend.
+ */
+ static HTAB *FSConnectionHash;
+
+ /* ----------------------------------------------------------------------------
+ * prototype of private functions
+ * --------------------------------------------------------------------------*/
+ static void
+ cleanup_connection(ResourceReleasePhase phase,
+ bool isCommit,
+ bool isTopLevel,
+ void *arg);
+ static PGconn *connect_pg_server(ForeignServer *server, UserMapping *user);
+ static void begin_remote_tx(PGconn *conn);
+ static void abort_remote_tx(PGconn *conn);
+
+ /*
+ * Get a PGconn which can be used to execute foreign query on the remote
+ * PostgreSQL server with the user's authorization. If this was the first
+ * request for the server, new connection is established.
+ *
+ * When use_tx is true, remote transaction is started if caller is the only
+ * user of the connection. Isolation level of the remote transaction is same
+ * as local transaction, and remote transaction will be aborted when last
+ * user release.
+ *
+ * TODO: Note that caching connections requires a mechanism to detect change of
+ * FDW object to invalidate already established connections.
+ */
+ PGconn *
+ GetConnection(ForeignServer *server, UserMapping *user, bool use_tx)
+ {
+ bool found;
+ ConnCacheEntry *entry;
+ ConnCacheEntry key;
+
+ /* initialize connection cache if it isn't */
+ if (FSConnectionHash == NULL)
+ {
+ HASHCTL ctl;
+
+ /* hash key is a pair of oids: serverid and userid */
+ MemSet(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(Oid) + sizeof(Oid);
+ ctl.entrysize = sizeof(ConnCacheEntry);
+ ctl.hash = tag_hash;
+ ctl.match = memcmp;
+ ctl.keycopy = memcpy;
+ /* allocate FSConnectionHash in the cache context */
+ ctl.hcxt = CacheMemoryContext;
+ FSConnectionHash = hash_create("Foreign Connections", 32,
+ &ctl,
+ HASH_ELEM | HASH_CONTEXT |
+ HASH_FUNCTION | HASH_COMPARE |
+ HASH_KEYCOPY);
+ }
+
+ /* Create key value for the entry. */
+ MemSet(&key, 0, sizeof(key));
+ key.serverid = server->serverid;
+ key.userid = GetOuterUserId();
+
+ /*
+ * Find cached entry for requested connection. If we couldn't find,
+ * callback function of ResourceOwner should be registered to clean the
+ * connection up on error including user interrupt.
+ */
+ entry = hash_search(FSConnectionHash, &key, HASH_ENTER, &found);
+ if (!found)
+ {
+ entry->refs = 0;
+ entry->use_tx = false;
+ entry->conn = NULL;
+ RegisterResourceReleaseCallback(cleanup_connection, entry);
+ }
+
+ /*
+ * We don't check the health of cached connection here, because it would
+ * require some overhead. Broken connection and its cache entry will be
+ * cleaned up when the connection is actually used.
+ */
+
+ /*
+ * If cache entry doesn't have connection, we have to establish new
+ * connection.
+ */
+ if (entry->conn == NULL)
+ {
+ /*
+ * Use PG_TRY block to ensure closing connection on error.
+ */
+ PG_TRY();
+ {
+ /*
+ * Connect to the foreign PostgreSQL server, and store it in cache
+ * entry to keep new connection.
+ * Note: key items of entry has already been initialized in
+ * hash_search(HASH_ENTER).
+ */
+ entry->conn = connect_pg_server(server, user);
+ }
+ PG_CATCH();
+ {
+ /* Clear connection cache entry on error case. */
+ PQfinish(entry->conn);
+ entry->refs = 0;
+ entry->use_tx = false;
+ entry->conn = NULL;
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+ }
+
+ /* Increase connection reference counter. */
+ entry->refs++;
+
+ /*
+ * If requester is the only referrer of this connection, start transaction
+ * with the same isolation level as the local transaction we are in.
+ * We need to remember whether this connection uses remote transaction to
+ * abort it when this connection is released completely.
+ */
+ if (use_tx && entry->refs == 1)
+ begin_remote_tx(entry->conn);
+ entry->use_tx = use_tx;
+
+
+ return entry->conn;
+ }
+
+ /*
+ * For non-superusers, insist that the connstr specify a password. This
+ * prevents a password from being picked up from .pgpass, a service file,
+ * the environment, etc. We don't want the postgres user's passwords
+ * to be accessible to non-superusers.
+ */
+ static void
+ check_conn_params(const char **keywords, const char **values)
+ {
+ int i;
+
+ /* no check required if superuser */
+ if (superuser())
+ return;
+
+ /* ok if params contain a non-empty password */
+ for (i = 0; keywords[i] != NULL; i++)
+ {
+ if (strcmp(keywords[i], "password") == 0 && values[i][0] != '\0')
+ return;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
+ errmsg("password is required"),
+ errdetail("Non-superusers must provide a password in the connection string.")));
+ }
+
+ static PGconn *
+ connect_pg_server(ForeignServer *server, UserMapping *user)
+ {
+ const char *conname = server->servername;
+ PGconn *conn;
+ const char **all_keywords;
+ const char **all_values;
+ const char **keywords;
+ const char **values;
+ int n;
+ int i, j;
+
+ /*
+ * Construct connection params from generic options of ForeignServer and
+ * UserMapping. Those two object hold only libpq options.
+ * Extra 3 items are for:
+ * *) fallback_application_name
+ * *) client_encoding
+ * *) NULL termination (end marker)
+ *
+ * Note: We don't omit any parameters even target database might be older
+ * than local, because unexpected parameters are just ignored.
+ */
+ n = list_length(server->options) + list_length(user->options) + 3;
+ all_keywords = (const char **) palloc(sizeof(char *) * n);
+ all_values = (const char **) palloc(sizeof(char *) * n);
+ keywords = (const char **) palloc(sizeof(char *) * n);
+ values = (const char **) palloc(sizeof(char *) * n);
+ n = 0;
+ n += ExtractConnectionOptions(server->options,
+ all_keywords + n, all_values + n);
+ n += ExtractConnectionOptions(user->options,
+ all_keywords + n, all_values + n);
+ all_keywords[n] = all_values[n] = NULL;
+
+ for (i = 0, j = 0; all_keywords[i]; i++)
+ {
+ keywords[j] = all_keywords[i];
+ values[j] = all_values[i];
+ j++;
+ }
+
+ /* Use "pgsql_fdw" as fallback_application_name. */
+ keywords[j] = "fallback_application_name";
+ values[j++] = "pgsql_fdw";
+
+ /* Set client_encoding so that libpq can convert encoding properly. */
+ keywords[j] = "client_encoding";
+ values[j++] = GetDatabaseEncodingName();
+
+ keywords[j] = values[j] = NULL;
+ pfree(all_keywords);
+ pfree(all_values);
+
+ /* verify connection parameters and do connect */
+ check_conn_params(keywords, values);
+ conn = PQconnectdbParams(keywords, values, 0);
+ if (!conn || PQstatus(conn) != CONNECTION_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION),
+ errmsg("could not connect to server \"%s\"", conname),
+ errdetail("%s", PQerrorMessage(conn))));
+ pfree(keywords);
+ pfree(values);
+
+ /*
+ * Check that non-superuser has used password to establish connection.
+ * This check logic is based on dblink_security_check() in contrib/dblink.
+ *
+ * XXX Should we check this even if we don't provide unsafe version like
+ * dblink_connect_u()?
+ */
+ if (!superuser() && !PQconnectionUsedPassword(conn))
+ {
+ PQfinish(conn);
+ ereport(ERROR,
+ (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
+ errmsg("password is required"),
+ errdetail("Non-superuser cannot connect if the server does not request a password."),
+ errhint("Target server's authentication method must be changed.")));
+ }
+
+ return conn;
+ }
+
+ /*
+ * Start transaction to use cursor to retrieve data separately.
+ */
+ static void
+ begin_remote_tx(PGconn *conn)
+ {
+ const char *sql = NULL; /* keep compiler quiet. */
+ PGresult *res;
+
+ switch (XactIsoLevel)
+ {
+ case XACT_READ_UNCOMMITTED:
+ case XACT_READ_COMMITTED:
+ case XACT_REPEATABLE_READ:
+ sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
+ break;
+ case XACT_SERIALIZABLE:
+ sql = "START TRANSACTION ISOLATION LEVEL SERIALIZABLE";
+ break;
+ default:
+ elog(ERROR, "unexpected isolation level: %d", XactIsoLevel);
+ break;
+ }
+
+ elog(DEBUG3, "starting remote transaction with \"%s\"", sql);
+
+ res = PQexec(conn, sql);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ PQclear(res);
+ elog(ERROR, "could not start transaction: %s", PQerrorMessage(conn));
+ }
+ PQclear(res);
+ }
+
+ static void
+ abort_remote_tx(PGconn *conn)
+ {
+ PGresult *res;
+
+ elog(DEBUG3, "aborting remote transaction");
+
+ res = PQexec(conn, "ABORT TRANSACTION");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ PQclear(res);
+ elog(ERROR, "could not abort transaction: %s", PQerrorMessage(conn));
+ }
+ PQclear(res);
+ }
+
+ /*
+ * Mark the connection as "unused", and close it if the caller was the last
+ * user of the connection.
+ */
+ void
+ ReleaseConnection(PGconn *conn)
+ {
+ HASH_SEQ_STATUS scan;
+ ConnCacheEntry *entry;
+
+ if (conn == NULL)
+ return;
+
+ /*
+ * We need to scan sequentially since we use the address to find
+ * appropriate PGconn from the hash table.
+ */
+ hash_seq_init(&scan, FSConnectionHash);
+ while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
+ {
+ if (entry->conn == conn)
+ {
+ hash_seq_term(&scan);
+ break;
+ }
+ }
+
+ /* If the released connection was an orphan, just close it. */
+ if (entry == NULL)
+ {
+ PQfinish(conn);
+ return;
+ }
+
+ /*
+ * If this connection uses remote transaction and caller is the last user,
+ * abort remote transaction and forget about it.
+ */
+ if (entry->use_tx && entry->refs == 1)
+ {
+ abort_remote_tx(conn);
+ entry->use_tx = false;
+ }
+
+ /*
+ * Decrease reference counter of this connection. Even if the caller was
+ * the last referrer, we don't unregister it from cache.
+ */
+ entry->refs--;
+ }
+
+ /*
+ * Clean the connection up via ResourceOwner.
+ */
+ static void
+ cleanup_connection(ResourceReleasePhase phase,
+ bool isCommit,
+ bool isTopLevel,
+ void *arg)
+ {
+ ConnCacheEntry *entry = (ConnCacheEntry *) arg;
+
+ /* If the transaction was committed, don't close connections. */
+ if (isCommit)
+ return;
+
+ /*
+ * We clean the connection up on post-lock because foreign connections are
+ * backend-internal resource.
+ */
+ if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
+ return;
+
+ /*
+ * We ignore cleanup for ResourceOwners other than transaction. At this
+ * point, such a ResourceOwner is only Portal.
+ */
+ if (CurrentResourceOwner != CurTransactionResourceOwner)
+ return;
+
+ /*
+ * We don't need to clean up at end of subtransactions, because they might
+ * be recovered to consistent state with savepoints.
+ */
+ if (!isTopLevel)
+ return;
+
+ /*
+ * Here, it must be after abort of top level transaction. Disconnect and
+ * forget every referrer.
+ */
+ if (entry->conn != NULL)
+ {
+ PQfinish(entry->conn);
+ entry->refs = 0;
+ entry->conn = NULL;
+ }
+ }
+
+ /*
+ * Get list of connections currently active.
+ */
+ Datum pgsql_fdw_get_connections(PG_FUNCTION_ARGS);
+ PG_FUNCTION_INFO_V1(pgsql_fdw_get_connections);
+ Datum
+ pgsql_fdw_get_connections(PG_FUNCTION_ARGS)
+ {
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HASH_SEQ_STATUS scan;
+ ConnCacheEntry *entry;
+ MemoryContext oldcontext = CurrentMemoryContext;
+ Tuplestorestate *tuplestore;
+ TupleDesc tupdesc;
+
+ /* We return list of connection with storing them in a Tuplestore. */
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = NULL;
+ rsinfo->setDesc = NULL;
+
+ /* Create tuplestore and copy of TupleDesc in per-query context. */
+ MemoryContextSwitchTo(rsinfo->econtext->ecxt_per_query_memory);
+
+ tupdesc = CreateTemplateTupleDesc(2, false);
+ TupleDescInitEntry(tupdesc, 1, "srvid", OIDOID, -1, 0);
+ TupleDescInitEntry(tupdesc, 2, "usesysid", OIDOID, -1, 0);
+ rsinfo->setDesc = tupdesc;
+
+ tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ rsinfo->setResult = tuplestore;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ /*
+ * We need to scan sequentially since we use the address to find
+ * appropriate PGconn from the hash table.
+ */
+ if (FSConnectionHash != NULL)
+ {
+ hash_seq_init(&scan, FSConnectionHash);
+ while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
+ {
+ Datum values[2];
+ bool nulls[2];
+ HeapTuple tuple;
+
+ /* Ignore inactive connections */
+ if (PQstatus(entry->conn) != CONNECTION_OK)
+ continue;
+
+ /*
+ * Ignore other users' connections if current user isn't a
+ * superuser.
+ */
+ if (!superuser() && entry->userid != GetUserId())
+ continue;
+
+ values[0] = ObjectIdGetDatum(entry->serverid);
+ values[1] = ObjectIdGetDatum(entry->userid);
+ nulls[0] = false;
+ nulls[1] = false;
+
+ tuple = heap_formtuple(tupdesc, values, nulls);
+ tuplestore_puttuple(tuplestore, tuple);
+ }
+ }
+ tuplestore_donestoring(tuplestore);
+
+ PG_RETURN_VOID();
+ }
+
+ /*
+ * Discard persistent connection designated by given connection name.
+ */
+ Datum pgsql_fdw_disconnect(PG_FUNCTION_ARGS);
+ PG_FUNCTION_INFO_V1(pgsql_fdw_disconnect);
+ Datum
+ pgsql_fdw_disconnect(PG_FUNCTION_ARGS)
+ {
+ Oid serverid = PG_GETARG_OID(0);
+ Oid userid = PG_GETARG_OID(1);
+ ConnCacheEntry key;
+ ConnCacheEntry *entry = NULL;
+ bool found;
+
+ /* Non-superuser can't discard other users' connection. */
+ if (!superuser() && userid != GetOuterUserId())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_RESOURCES),
+ errmsg("only superuser can discard other user's connection")));
+
+ /*
+ * If no connection has been established, or no such connections, just
+ * return "NG" to indicate nothing has done.
+ */
+ if (FSConnectionHash == NULL)
+ PG_RETURN_TEXT_P(cstring_to_text("NG"));
+
+ key.serverid = serverid;
+ key.userid = userid;
+ entry = hash_search(FSConnectionHash, &key, HASH_FIND, &found);
+ if (!found)
+ PG_RETURN_TEXT_P(cstring_to_text("NG"));
+
+ /* Discard cached connection, and clear reference counter. */
+ PQfinish(entry->conn);
+ entry->refs = 0;
+ entry->conn = NULL;
+
+ PG_RETURN_TEXT_P(cstring_to_text("OK"));
+ }
diff --git a/contrib/pgsql_fdw/connection.h b/contrib/pgsql_fdw/connection.h
index ...4427f56 .
*** a/contrib/pgsql_fdw/connection.h
--- b/contrib/pgsql_fdw/connection.h
***************
*** 0 ****
--- 1,25 ----
+ /*-------------------------------------------------------------------------
+ *
+ * connection.h
+ * Connection management for pgsql_fdw
+ *
+ * Portions Copyright (c) 2012, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pgsql_fdw/connection.h
+ *
+ *-------------------------------------------------------------------------
+ */
+ #ifndef CONNECTION_H
+ #define CONNECTION_H
+
+ #include "foreign/foreign.h"
+ #include "libpq-fe.h"
+
+ /*
+ * Connection management
+ */
+ PGconn *GetConnection(ForeignServer *server, UserMapping *user, bool use_tx);
+ void ReleaseConnection(PGconn *conn);
+
+ #endif /* CONNECTION_H */
diff --git a/contrib/pgsql_fdw/deparse.c b/contrib/pgsql_fdw/deparse.c
index ...beb62fe .
*** a/contrib/pgsql_fdw/deparse.c
--- b/contrib/pgsql_fdw/deparse.c
***************
*** 0 ****
--- 1,290 ----
+ /*-------------------------------------------------------------------------
+ *
+ * deparse.c
+ * query deparser for PostgreSQL
+ *
+ * Copyright (c) 2012, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pgsql_fdw/deparse.c
+ *
+ *-------------------------------------------------------------------------
+ */
+ #include "postgres.h"
+
+ #include "access/transam.h"
+ #include "catalog/pg_class.h"
+ #include "commands/defrem.h"
+ #include "foreign/foreign.h"
+ #include "lib/stringinfo.h"
+ #include "nodes/nodeFuncs.h"
+ #include "nodes/nodes.h"
+ #include "nodes/makefuncs.h"
+ #include "optimizer/clauses.h"
+ #include "optimizer/var.h"
+ #include "parser/parsetree.h"
+ #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
+
+ #include "pgsql_fdw.h"
+
+ /*
+ * Context for walk-through the expression tree.
+ */
+ typedef struct foreign_executable_cxt
+ {
+ PlannerInfo *root;
+ RelOptInfo *foreignrel;
+ } foreign_executable_cxt;
+
+ /*
+ * Get string representation which can be used in SQL statement from a node.
+ */
+ static void deparseRelation(StringInfo buf, Oid relid, PlannerInfo *root,
+ bool need_prefix);
+ static void deparseVar(StringInfo buf, Var *node, PlannerInfo *root,
+ RelOptInfo *baserel, bool need_prefix);
+
+ /*
+ * Deparse query representation into SQL statement which suits for remote
+ * PostgreSQL server. This function just creates simple query string which
+ * consists of only SELECT and FROM clause.
+ */
+ char *
+ deparseSimpleSql(Oid relid,
+ PlannerInfo *root,
+ RelOptInfo *baserel,
+ ForeignTable *table)
+ {
+ StringInfoData foreign_relname;
+ StringInfoData sql; /* builder for SQL statement */
+ bool first;
+ AttrNumber attr;
+ List *attr_used = NIL; /* List of AttNumber used in the query */
+
+ initStringInfo(&sql);
+ initStringInfo(&foreign_relname);
+
+ /*
+ * First of all, determine which column should be retrieved for this scan.
+ *
+ * We do this before deparsing SELECT clause because attributes which are
+ * not used in neither reltargetlist nor baserel->baserestrictinfo, quals
+ * evaluated on local, can be replaced with literal "NULL" in the SELECT
+ * clause to reduce overhead of tuple handling tuple and data transfer.
+ */
+ if (baserel->baserestrictinfo != NIL)
+ {
+ ListCell *lc;
+
+ foreach (lc, baserel->baserestrictinfo)
+ {
+ RestrictInfo *ri = (RestrictInfo *) lfirst(lc);
+ List *attrs;
+
+ /*
+ * We need to know which attributes are used in qual evaluated
+ * on the local server, because they should be listed in the
+ * SELECT clause of remote query. We can ignore attributes
+ * which are referenced only in ORDER BY/GROUP BY clause because
+ * such attributes has already been kept in reltargetlist.
+ */
+ attrs = pull_var_clause((Node *) ri->clause,
+ PVC_RECURSE_AGGREGATES,
+ PVC_RECURSE_PLACEHOLDERS);
+ attr_used = list_union(attr_used, attrs);
+ }
+ }
+
+ /*
+ * deparse SELECT clause
+ *
+ * List attributes which are in either target list or local restriction.
+ * Unused attributes are replaced with a literal "NULL" for optimization.
+ *
+ * Note that nothing is added for dropped columns, though tuple constructor
+ * function requires entries for dropped columns. Such entries must be
+ * initialized with NULL before calling tuple constructor.
+ */
+ appendStringInfo(&sql, "SELECT ");
+ attr_used = list_union(attr_used, baserel->reltargetlist);
+ first = true;
+ for (attr = 1; attr <= baserel->max_attr; attr++)
+ {
+ RangeTblEntry *rte = root->simple_rte_array[baserel->relid];
+ Var *var = NULL;
+ ListCell *lc;
+
+ /* Ignore dropped attributes. */
+ if (get_rte_attribute_is_dropped(rte, attr))
+ continue;
+
+ if (!first)
+ appendStringInfo(&sql, ", ");
+ first = false;
+
+ /*
+ * We use linear search here, but it wouldn't be problem since
+ * attr_used seems to not become so large.
+ */
+ foreach (lc, attr_used)
+ {
+ var = lfirst(lc);
+ if (var->varattno == attr)
+ break;
+ var = NULL;
+ }
+ if (var != NULL)
+ deparseVar(&sql, var, root, baserel, false);
+ else
+ appendStringInfo(&sql, "NULL");
+ }
+ appendStringInfoChar(&sql, ' ');
+
+ /*
+ * deparse FROM clause, including alias if any
+ */
+ appendStringInfo(&sql, "FROM ");
+ deparseRelation(&sql, table->relid, root, true);
+
+ elog(DEBUG3, "Remote SQL: %s", sql.data);
+ return sql.data;
+ }
+
+ /*
+ * Deparse node into buf, with relation qualifier if need_prefix was true. If
+ * node is a column of a foreign table, use value of colname FDW option (if any)
+ * instead of attribute name.
+ */
+ static void
+ deparseVar(StringInfo buf,
+ Var *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel,
+ bool need_prefix)
+ {
+ RangeTblEntry *rte;
+ char *colname = NULL;
+ const char *q_colname = NULL;
+ List *options;
+ ListCell *lc;
+
+ /* node must not be any of OUTER_VAR,INNER_VAR and INDEX_VAR. */
+ Assert(node->varno >= 1 && node->varno <= root->simple_rel_array_size);
+
+ /* Get RangeTblEntry from array in PlannerInfo. */
+ rte = root->simple_rte_array[node->varno];
+
+ /*
+ * If the node is a column of a foreign table, and it has colname FDW
+ * option, use its value.
+ */
+ if (rte->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ options = GetForeignColumnOptions(rte->relid, node->varattno);
+ foreach(lc, options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "colname") == 0)
+ {
+ colname = defGetString(def);
+ break;
+ }
+ }
+ }
+
+ /*
+ * If the node refers a column of a regular table or it doesn't have colname
+ * FDW option, use attribute name.
+ */
+ if (colname == NULL)
+ colname = get_attname(rte->relid, node->varattno);
+
+ if (need_prefix)
+ {
+ char *aliasname;
+ const char *q_aliasname;
+
+ if (rte->eref != NULL && rte->eref->aliasname != NULL)
+ aliasname = rte->eref->aliasname;
+ else if (rte->alias != NULL && rte->alias->aliasname != NULL)
+ aliasname = rte->alias->aliasname;
+
+ q_aliasname = quote_identifier(aliasname);
+ appendStringInfo(buf, "%s.", q_aliasname);
+ }
+
+ q_colname = quote_identifier(colname);
+ appendStringInfo(buf, "%s", q_colname);
+ }
+
+ /*
+ * Deparse table which has relid as oid into buf, with schema qualifier if
+ * need_prefix was true. If relid points a foreign table, use value of relname
+ * FDW option (if any) instead of relation's name. Similarly, nspname FDW
+ * option overrides schema name.
+ */
+ static void
+ deparseRelation(StringInfo buf,
+ Oid relid,
+ PlannerInfo *root,
+ bool need_prefix)
+ {
+ int i;
+ RangeTblEntry *rte = NULL;
+ ListCell *lc;
+ const char *nspname = NULL; /* plain namespace name */
+ const char *relname = NULL; /* plain relation name */
+ const char *q_nspname; /* quoted namespace name */
+ const char *q_relname; /* quoted relation name */
+
+ /* Find RangeTblEntry for the relation from PlannerInfo. */
+ for (i = 1; i < root->simple_rel_array_size; i++)
+ {
+ if (root->simple_rte_array[i]->relid == relid)
+ {
+ rte = root->simple_rte_array[i];
+ break;
+ }
+ }
+ if (rte == NULL)
+ elog(ERROR, "relation with OID %u is not used in the query", relid);
+
+ /* If target is a foreign table, obtain additional catalog information. */
+ if (rte->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ ForeignTable *table = GetForeignTable(rte->relid);
+
+ /*
+ * Use value of FDW options if any, instead of the name of object
+ * itself.
+ */
+ foreach(lc, table->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (need_prefix && strcmp(def->defname, "nspname") == 0)
+ nspname = defGetString(def);
+ else if (strcmp(def->defname, "relname") == 0)
+ relname = defGetString(def);
+ }
+ }
+
+ /* Quote each identifier, if necessary. */
+ if (need_prefix)
+ {
+ if (nspname == NULL)
+ nspname = get_namespace_name(get_rel_namespace(relid));
+ q_nspname = quote_identifier(nspname);
+ }
+
+ if (relname == NULL)
+ relname = get_rel_name(relid);
+ q_relname = quote_identifier(relname);
+
+ /* Construct relation reference into the buffer. */
+ if (need_prefix)
+ appendStringInfo(buf, "%s.", q_nspname);
+ appendStringInfo(buf, "%s", q_relname);
+ }
diff --git a/contrib/pgsql_fdw/expected/pgsql_fdw.out b/contrib/pgsql_fdw/expected/pgsql_fdw.out
index ...5bac241 .
*** a/contrib/pgsql_fdw/expected/pgsql_fdw.out
--- b/contrib/pgsql_fdw/expected/pgsql_fdw.out
***************
*** 0 ****
--- 1,592 ----
+ -- ===================================================================
+ -- create FDW objects
+ -- ===================================================================
+ -- Clean up in case a prior regression run failed
+ -- Suppress NOTICE messages when roles don't exist
+ SET client_min_messages TO 'error';
+ DROP ROLE IF EXISTS pgsql_fdw_user;
+ RESET client_min_messages;
+ CREATE ROLE pgsql_fdw_user LOGIN SUPERUSER;
+ SET SESSION AUTHORIZATION 'pgsql_fdw_user';
+ CREATE EXTENSION pgsql_fdw;
+ CREATE SERVER loopback1 FOREIGN DATA WRAPPER pgsql_fdw;
+ CREATE SERVER loopback2 FOREIGN DATA WRAPPER pgsql_fdw
+ OPTIONS (dbname 'contrib_regression');
+ CREATE USER MAPPING FOR public SERVER loopback1
+ OPTIONS (user 'value', password 'value');
+ CREATE USER MAPPING FOR pgsql_fdw_user SERVER loopback2;
+ CREATE FOREIGN TABLE ft1 (
+ c0 int,
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 timestamptz,
+ c5 timestamp,
+ c6 varchar(10),
+ c7 char(10)
+ ) SERVER loopback2;
+ ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
+ CREATE FOREIGN TABLE ft2 (
+ c0 int,
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 timestamptz,
+ c5 timestamp,
+ c6 varchar(10),
+ c7 char(10)
+ ) SERVER loopback2;
+ ALTER FOREIGN TABLE ft2 DROP COLUMN c0;
+ -- ===================================================================
+ -- create objects used through FDW
+ -- ===================================================================
+ CREATE SCHEMA "S 1";
+ CREATE TABLE "S 1"."T 1" (
+ "C 1" int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 timestamptz,
+ c5 timestamp,
+ c6 varchar(10),
+ c7 char(10),
+ CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
+ );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "T 1"
+ CREATE TABLE "S 1"."T 2" (
+ c1 int NOT NULL,
+ c2 text,
+ CONSTRAINT t2_pkey PRIMARY KEY (c1)
+ );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "T 2"
+ BEGIN;
+ TRUNCATE "S 1"."T 1";
+ INSERT INTO "S 1"."T 1"
+ SELECT id,
+ id % 10,
+ to_char(id, 'FM00000'),
+ '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
+ '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
+ id % 10,
+ id % 10
+ FROM generate_series(1, 1000) id;
+ TRUNCATE "S 1"."T 2";
+ INSERT INTO "S 1"."T 2"
+ SELECT id,
+ 'AAA' || to_char(id, 'FM000')
+ FROM generate_series(1, 100) id;
+ COMMIT;
+ -- ===================================================================
+ -- tests for pgsql_fdw_validator
+ -- ===================================================================
+ ALTER FOREIGN DATA WRAPPER pgsql_fdw OPTIONS (host 'value'); -- ERROR
+ ERROR: invalid option "host"
+ HINT: Valid options in this context are:
+ -- requiressl, krbsrvname and gsslib are omitted because they depend on
+ -- configure option
+ ALTER SERVER loopback1 OPTIONS (
+ authtype 'value',
+ service 'value',
+ connect_timeout 'value',
+ dbname 'value',
+ host 'value',
+ hostaddr 'value',
+ port 'value',
+ --client_encoding 'value',
+ tty 'value',
+ options 'value',
+ application_name 'value',
+ --fallback_application_name 'value',
+ keepalives 'value',
+ keepalives_idle 'value',
+ keepalives_interval 'value',
+ -- requiressl 'value',
+ sslcompression 'value',
+ sslmode 'value',
+ sslcert 'value',
+ sslkey 'value',
+ sslrootcert 'value',
+ sslcrl 'value'
+ --requirepeer 'value',
+ -- krbsrvname 'value',
+ -- gsslib 'value',
+ --replication 'value'
+ );
+ ALTER SERVER loopback1 OPTIONS (user 'value'); -- ERROR
+ ERROR: invalid option "user"
+ HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, requiressl, sslcompression, sslmode, sslcert, sslkey, sslrootcert, sslcrl, requirepeer, krbsrvname, gsslib, fetch_count
+ ALTER SERVER loopback2 OPTIONS (ADD fetch_count '2');
+ ALTER USER MAPPING FOR public SERVER loopback1
+ OPTIONS (DROP user, DROP password);
+ ALTER USER MAPPING FOR public SERVER loopback1
+ OPTIONS (host 'value'); -- ERROR
+ ERROR: invalid option "host"
+ HINT: Valid options in this context are: user, password
+ ALTER FOREIGN TABLE ft1 OPTIONS (nspname 'S 1', relname 'T 1');
+ ALTER FOREIGN TABLE ft2 OPTIONS (nspname 'S 1', relname 'T 1', fetch_count '100');
+ ALTER FOREIGN TABLE ft1 OPTIONS (invalid 'value'); -- ERROR
+ ERROR: invalid option "invalid"
+ HINT: Valid options in this context are: nspname, relname, fetch_count
+ ALTER FOREIGN TABLE ft1 OPTIONS (fetch_count 'a'); -- ERROR
+ ERROR: invalid value for fetch_count: "a"
+ ALTER FOREIGN TABLE ft1 OPTIONS (fetch_count '0'); -- ERROR
+ ERROR: invalid value for fetch_count: "0"
+ ALTER FOREIGN TABLE ft1 OPTIONS (fetch_count '-1'); -- ERROR
+ ERROR: invalid value for fetch_count: "-1"
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (invalid 'value'); -- ERROR
+ ERROR: invalid option "invalid"
+ HINT: Valid options in this context are: colname
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (colname 'C 1');
+ ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (colname 'C 1');
+ \dew+
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
+ -----------+----------------+-------------------+---------------------+-------------------+-------------+-------------
+ pgsql_fdw | pgsql_fdw_user | pgsql_fdw_handler | pgsql_fdw_validator | | |
+ (1 row)
+
+ \des+
+ List of foreign servers
+ Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
+ -----------+----------------+----------------------+-------------------+------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
+ loopback1 | pgsql_fdw_user | pgsql_fdw | | | | (authtype 'value', service 'value', connect_timeout 'value', dbname 'value', host 'value', hostaddr 'value', port 'value', tty 'value', options 'value', application_name 'value', keepalives 'value', keepalives_idle 'value', keepalives_interval 'value', sslcompression 'value', sslmode 'value', sslcert 'value', sslkey 'value', sslrootcert 'value', sslcrl 'value') |
+ loopback2 | pgsql_fdw_user | pgsql_fdw | | | | (dbname 'contrib_regression', fetch_count '2') |
+ (2 rows)
+
+ \deu+
+ List of user mappings
+ Server | User name | FDW Options
+ -----------+----------------+-------------
+ loopback1 | public |
+ loopback2 | pgsql_fdw_user |
+ (2 rows)
+
+ \det+
+ List of foreign tables
+ Schema | Table | Server | FDW Options | Description
+ --------+-------+-----------+---------------------------------------------------+-------------
+ public | ft1 | loopback2 | (nspname 'S 1', relname 'T 1') |
+ public | ft2 | loopback2 | (nspname 'S 1', relname 'T 1', fetch_count '100') |
+ (2 rows)
+
+ -- ===================================================================
+ -- simple queries
+ -- ===================================================================
+ -- single table, with/without alias
+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+ ------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: c1, c2, c3, c4, c5, c6, c7
+ -> Sort
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Sort Key: ft1.c3, ft1.c1
+ -> Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Remote SQL: DECLARE pgsql_fdw_cursor_0 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (8 rows)
+
+ SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ -----+----+-------+------------------------------+--------------------------+----+------------
+ 101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1
+ 102 | 2 | 00102 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2
+ 103 | 3 | 00103 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3
+ 104 | 4 | 00104 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4
+ 105 | 5 | 00105 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5
+ 106 | 6 | 00106 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6
+ 107 | 7 | 00107 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7
+ 108 | 8 | 00108 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8
+ 109 | 9 | 00109 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9
+ 110 | 0 | 00110 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0
+ (10 rows)
+
+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+ ------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: c1, c2, c3, c4, c5, c6, c7
+ -> Sort
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Remote SQL: DECLARE pgsql_fdw_cursor_2 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (8 rows)
+
+ SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ -----+----+-------+------------------------------+--------------------------+----+------------
+ 101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1
+ 102 | 2 | 00102 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2
+ 103 | 3 | 00103 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3
+ 104 | 4 | 00104 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4
+ 105 | 5 | 00105 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5
+ 106 | 6 | 00106 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6
+ 107 | 7 | 00107 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7
+ 108 | 8 | 00108 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8
+ 109 | 9 | 00109 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9
+ 110 | 0 | 00110 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0
+ (10 rows)
+
+ -- with WHERE clause
+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
+ QUERY PLAN
+ ------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7
+ Filter: ((t1.c1 = 101) AND ((t1.c6)::text = '1'::text) AND (t1.c7 = '1'::bpchar))
+ Remote SQL: DECLARE pgsql_fdw_cursor_4 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (4 rows)
+
+ SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ -----+----+-------+------------------------------+--------------------------+----+------------
+ 101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1
+ (1 row)
+
+ -- aggregate
+ SELECT COUNT(*) FROM ft1 t1;
+ count
+ -------
+ 1000
+ (1 row)
+
+ -- join two tables
+ SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ c1
+ -----
+ 101
+ 102
+ 103
+ 104
+ 105
+ 106
+ 107
+ 108
+ 109
+ 110
+ (10 rows)
+
+ -- subquery
+ SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ ----+----+-------+------------------------------+--------------------------+----+------------
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1
+ 2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2
+ 3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3
+ 4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4
+ 5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5
+ 6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6
+ 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7
+ 8 | 8 | 00008 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8
+ 9 | 9 | 00009 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9
+ 10 | 0 | 00010 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0
+ (10 rows)
+
+ -- subquery+MAX
+ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ ------+----+-------+------------------------------+--------------------------+----+------------
+ 1000 | 0 | 01000 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0
+ (1 row)
+
+ -- used in CTE
+ WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
+ c1 | c2 | c3 | c4
+ ----+----+-------+------------------------------
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST
+ 2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST
+ 3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST
+ 4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST
+ 5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST
+ 6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST
+ 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST
+ 8 | 8 | 00008 | Fri Jan 09 00:00:00 1970 PST
+ 9 | 9 | 00009 | Sat Jan 10 00:00:00 1970 PST
+ 10 | 0 | 00010 | Sun Jan 11 00:00:00 1970 PST
+ (10 rows)
+
+ -- fixed values
+ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+ ?column? | ?column?
+ ----------+----------
+ fixed |
+ (1 row)
+
+ -- user-defined operator/function
+ CREATE FUNCTION pgsql_fdw_abs(int) RETURNS int AS $$
+ BEGIN
+ RETURN abs($1);
+ END
+ $$ LANGUAGE plpgsql IMMUTABLE;
+ CREATE OPERATOR === (
+ LEFTARG = int,
+ RIGHTARG = int,
+ PROCEDURE = int4eq,
+ COMMUTATOR = ===,
+ NEGATOR = !==
+ );
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = pgsql_fdw_abs(t1.c2);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = pgsql_fdw_abs(c2))
+ Remote SQL: DECLARE pgsql_fdw_cursor_16 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 === c2)
+ Remote SQL: DECLARE pgsql_fdw_cursor_17 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = abs(c2))
+ Remote SQL: DECLARE pgsql_fdw_cursor_18 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = c2)
+ Remote SQL: DECLARE pgsql_fdw_cursor_19 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ -- ===================================================================
+ -- parameterized queries
+ -- ===================================================================
+ -- simple join
+ PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
+ EXPLAIN (COSTS false) EXECUTE st1(1, 2);
+ QUERY PLAN
+ -----------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ -> Foreign Scan on ft1 t1
+ Filter: (c1 = 1)
+ Remote SQL: DECLARE pgsql_fdw_cursor_20 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1"
+ -> Materialize
+ -> Foreign Scan on ft2 t2
+ Filter: (c1 = 2)
+ Remote SQL: DECLARE pgsql_fdw_cursor_21 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1"
+ (8 rows)
+
+ EXECUTE st1(1, 1);
+ c3 | c3
+ -------+-------
+ 00001 | 00001
+ (1 row)
+
+ EXECUTE st1(101, 101);
+ c3 | c3
+ -------+-------
+ 00101 | 00101
+ (1 row)
+
+ -- subquery using stable function (can't be pushed down)
+ PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) = 6) ORDER BY c1;
+ EXPLAIN (COSTS false) EXECUTE st2(10, 20);
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.c1
+ -> Hash Join
+ Hash Cond: (t1.c3 = t2.c3)
+ -> Foreign Scan on ft1 t1
+ Filter: (c1 < 20)
+ Remote SQL: DECLARE pgsql_fdw_cursor_26 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ -> Hash
+ -> HashAggregate
+ -> Foreign Scan on ft2 t2
+ Filter: ((c1 > 10) AND (date_part('dow'::text, c4) = 6::double precision))
+ Remote SQL: DECLARE pgsql_fdw_cursor_27 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, c4, NULL, NULL, NULL FROM "S 1"."T 1"
+ (12 rows)
+
+ EXECUTE st2(10, 20);
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ ----+----+-------+------------------------------+--------------------------+----+------------
+ 16 | 6 | 00016 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6 | 6
+ (1 row)
+
+ EXECUTE st1(101, 101);
+ c3 | c3
+ -------+-------
+ 00101 | 00101
+ (1 row)
+
+ -- subquery using immutable function (can be pushed down)
+ PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c5) = 6) ORDER BY c1;
+ EXPLAIN (COSTS false) EXECUTE st3(10, 20);
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.c1
+ -> Hash Join
+ Hash Cond: (t1.c3 = t2.c3)
+ -> Foreign Scan on ft1 t1
+ Filter: (c1 < 20)
+ Remote SQL: DECLARE pgsql_fdw_cursor_32 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ -> Hash
+ -> HashAggregate
+ -> Foreign Scan on ft2 t2
+ Filter: ((c1 > 10) AND (date_part('dow'::text, c5) = 6::double precision))
+ Remote SQL: DECLARE pgsql_fdw_cursor_33 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, c5, NULL, NULL FROM "S 1"."T 1"
+ (12 rows)
+
+ EXECUTE st3(10, 20);
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ ----+----+-------+------------------------------+--------------------------+----+------------
+ 16 | 6 | 00016 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6 | 6
+ (1 row)
+
+ EXECUTE st3(20, 30);
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ ----+----+-------+------------------------------+--------------------------+----+------------
+ 23 | 3 | 00023 | Sat Jan 24 00:00:00 1970 PST | Sat Jan 24 00:00:00 1970 | 3 | 3
+ (1 row)
+
+ -- custom plan should be chosen
+ PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = 1)
+ Remote SQL: DECLARE pgsql_fdw_cursor_38 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = 1)
+ Remote SQL: DECLARE pgsql_fdw_cursor_39 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = 1)
+ Remote SQL: DECLARE pgsql_fdw_cursor_40 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = 1)
+ Remote SQL: DECLARE pgsql_fdw_cursor_41 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = 1)
+ Remote SQL: DECLARE pgsql_fdw_cursor_42 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on ft1 t1
+ Filter: (c1 = $1)
+ Remote SQL: DECLARE pgsql_fdw_cursor_43 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
+ (3 rows)
+
+ -- cleanup
+ DEALLOCATE st1;
+ DEALLOCATE st2;
+ DEALLOCATE st3;
+ DEALLOCATE st4;
+ -- ===================================================================
+ -- connection management
+ -- ===================================================================
+ SELECT srvname, usename FROM pgsql_fdw_connections;
+ srvname | usename
+ -----------+----------------
+ loopback2 | pgsql_fdw_user
+ (1 row)
+
+ SELECT pgsql_fdw_disconnect(srvid, usesysid) FROM pgsql_fdw_get_connections();
+ pgsql_fdw_disconnect
+ ----------------------
+ OK
+ (1 row)
+
+ SELECT srvname, usename FROM pgsql_fdw_connections;
+ srvname | usename
+ ---------+---------
+ (0 rows)
+
+ -- ===================================================================
+ -- subtransaction
+ -- ===================================================================
+ BEGIN;
+ DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+ FETCH c;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ ----+----+-------+------------------------------+--------------------------+----+------------
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1
+ (1 row)
+
+ SAVEPOINT s;
+ ERROR OUT; -- ERROR
+ ERROR: syntax error at or near "ERROR"
+ LINE 1: ERROR OUT;
+ ^
+ ROLLBACK TO s;
+ SELECT srvname FROM pgsql_fdw_connections;
+ srvname
+ -----------
+ loopback2
+ (1 row)
+
+ FETCH c;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7
+ ----+----+-------+------------------------------+--------------------------+----+------------
+ 2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2
+ (1 row)
+
+ COMMIT;
+ SELECT srvname FROM pgsql_fdw_connections;
+ srvname
+ -----------
+ loopback2
+ (1 row)
+
+ ERROR OUT; -- ERROR
+ ERROR: syntax error at or near "ERROR"
+ LINE 1: ERROR OUT;
+ ^
+ SELECT srvname FROM pgsql_fdw_connections;
+ srvname
+ ---------
+ (0 rows)
+
+ -- ===================================================================
+ -- cleanup
+ -- ===================================================================
+ DROP OPERATOR === (int, int) CASCADE;
+ DROP OPERATOR !== (int, int) CASCADE;
+ DROP FUNCTION pgsql_fdw_abs(int);
+ DROP SCHEMA "S 1" CASCADE;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to table "S 1"."T 1"
+ drop cascades to table "S 1"."T 2"
+ DROP EXTENSION pgsql_fdw CASCADE;
+ NOTICE: drop cascades to 6 other objects
+ DETAIL: drop cascades to server loopback1
+ drop cascades to user mapping for public
+ drop cascades to server loopback2
+ drop cascades to user mapping for pgsql_fdw_user
+ drop cascades to foreign table ft1
+ drop cascades to foreign table ft2
+ \c
+ DROP ROLE pgsql_fdw_user;
diff --git a/contrib/pgsql_fdw/option.c b/contrib/pgsql_fdw/option.c
index ...a2a8927 .
*** a/contrib/pgsql_fdw/option.c
--- b/contrib/pgsql_fdw/option.c
***************
*** 0 ****
--- 1,242 ----
+ /*-------------------------------------------------------------------------
+ *
+ * option.c
+ * FDW option handling
+ *
+ * Copyright (c) 2012, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pgsql_fdw/option.c
+ *
+ *-------------------------------------------------------------------------
+ */
+ #include "postgres.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 "fmgr.h"
+ #include "foreign/foreign.h"
+ #include "lib/stringinfo.h"
+ #include "miscadmin.h"
+
+ #include "pgsql_fdw.h"
+
+ /*
+ * SQL functions
+ */
+ extern Datum pgsql_fdw_validator(PG_FUNCTION_ARGS);
+ PG_FUNCTION_INFO_V1(pgsql_fdw_validator);
+
+ /*
+ * Describes the valid options for objects that use this wrapper.
+ */
+ typedef struct PgsqlFdwOption
+ {
+ const char *optname;
+ Oid optcontext; /* Oid of catalog in which options may appear */
+ bool is_libpq_opt; /* true if it's used in libpq */
+ } PgsqlFdwOption;
+
+ /*
+ * Valid options for pgsql_fdw.
+ */
+ static PgsqlFdwOption valid_options[] = {
+
+ /*
+ * Options for libpq connection.
+ * Note: This list should be updated along with PQconninfoOptions in
+ * interfaces/libpq/fe-connect.c, so the order is kept as is.
+ *
+ * Some useless libpq connection options are not accepted by pgsql_fdw:
+ * client_encoding: set to local database encoding automatically
+ * fallback_application_name: fixed to "pgsql_fdw"
+ * replication: pgsql_fdw never be replication client
+ */
+ {"authtype", ForeignServerRelationId, true},
+ {"service", ForeignServerRelationId, true},
+ {"user", UserMappingRelationId, true},
+ {"password", UserMappingRelationId, true},
+ {"connect_timeout", ForeignServerRelationId, true},
+ {"dbname", ForeignServerRelationId, true},
+ {"host", ForeignServerRelationId, true},
+ {"hostaddr", ForeignServerRelationId, true},
+ {"port", ForeignServerRelationId, true},
+ #ifdef NOT_USED
+ {"client_encoding", ForeignServerRelationId, true},
+ #endif
+ {"tty", ForeignServerRelationId, true},
+ {"options", ForeignServerRelationId, true},
+ {"application_name", ForeignServerRelationId, true},
+ #ifdef NOT_USED
+ {"fallback_application_name", ForeignServerRelationId, true},
+ #endif
+ {"keepalives", ForeignServerRelationId, true},
+ {"keepalives_idle", ForeignServerRelationId, true},
+ {"keepalives_interval", ForeignServerRelationId, true},
+ {"keepalives_count", ForeignServerRelationId, true},
+ {"requiressl", ForeignServerRelationId, true},
+ {"sslcompression", ForeignServerRelationId, true},
+ {"sslmode", ForeignServerRelationId, true},
+ {"sslcert", ForeignServerRelationId, true},
+ {"sslkey", ForeignServerRelationId, true},
+ {"sslrootcert", ForeignServerRelationId, true},
+ {"sslcrl", ForeignServerRelationId, true},
+ {"requirepeer", ForeignServerRelationId, true},
+ {"krbsrvname", ForeignServerRelationId, true},
+ {"gsslib", ForeignServerRelationId, true},
+ #ifdef NOT_USED
+ {"replication", ForeignServerRelationId, true},
+ #endif
+
+ /*
+ * Options for translation of object names.
+ */
+ {"nspname", ForeignTableRelationId, false},
+ {"relname", ForeignTableRelationId, false},
+ {"colname", AttributeRelationId, false},
+
+ /*
+ * Options for cursor behavior.
+ * These options can be overridden by finer-grained objects.
+ */
+ {"fetch_count", ForeignTableRelationId, false},
+ {"fetch_count", ForeignServerRelationId, false},
+
+ /* Terminating entry --- MUST BE LAST */
+ {NULL, InvalidOid, false}
+ };
+
+ /*
+ * Helper functions
+ */
+ static bool is_valid_option(const char *optname, Oid context);
+
+ /*
+ * Validate the generic options given to a FOREIGN DATA WRAPPER, SERVER,
+ * USER MAPPING or FOREIGN TABLE that uses pgsql_fdw.
+ *
+ * Raise an ERROR if the option or its value is considered invalid.
+ */
+ Datum
+ pgsql_fdw_validator(PG_FUNCTION_ARGS)
+ {
+ List *options_list = untransformRelOptions(PG_GETARG_DATUM(0));
+ Oid catalog = PG_GETARG_OID(1);
+ ListCell *cell;
+
+ /*
+ * Check that only options supported by pgsql_fdw, and allowed for the
+ * current object type, are given.
+ */
+ foreach(cell, options_list)
+ {
+ DefElem *def = (DefElem *) lfirst(cell);
+
+ if (!is_valid_option(def->defname, catalog))
+ {
+ PgsqlFdwOption *opt;
+ StringInfoData buf;
+
+ /*
+ * Unknown option specified, complain about it. Provide a hint
+ * with list of valid options for the object.
+ */
+ initStringInfo(&buf);
+ for (opt = valid_options; opt->optname; opt++)
+ {
+ if (catalog == opt->optcontext)
+ appendStringInfo(&buf, "%s%s", (buf.len > 0) ? ", " : "",
+ opt->optname);
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FDW_INVALID_OPTION_NAME),
+ errmsg("invalid option \"%s\"", def->defname),
+ errhint("Valid options in this context are: %s",
+ buf.data)));
+ }
+
+ /* fetch_count be positive digit number. */
+ if (strcmp(def->defname, "fetch_count") == 0)
+ {
+ long value;
+ char *p = NULL;
+
+ value = strtol(defGetString(def), &p, 10);
+ if (*p != '\0' || value < 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FDW_INVALID_ATTRIBUTE_VALUE),
+ errmsg("invalid value for %s: \"%s\"",
+ def->defname, defGetString(def))));
+ }
+ }
+
+ /*
+ * We don't care option-specific limitation here; they will be validated at
+ * the execution time.
+ */
+
+ PG_RETURN_VOID();
+ }
+
+ /*
+ * Check whether the given option is one of the valid pgsql_fdw options.
+ * context is the Oid of the catalog holding the object the option is for.
+ */
+ static bool
+ is_valid_option(const char *optname, Oid context)
+ {
+ PgsqlFdwOption *opt;
+
+ for (opt = valid_options; opt->optname; opt++)
+ {
+ if (context == opt->optcontext && strcmp(opt->optname, optname) == 0)
+ return true;
+ }
+ return false;
+ }
+
+ /*
+ * Check whether the given option is one of the valid libpq options.
+ * context is the Oid of the catalog holding the object the option is for.
+ */
+ static bool
+ is_libpq_option(const char *optname)
+ {
+ PgsqlFdwOption *opt;
+
+ for (opt = valid_options; opt->optname; opt++)
+ {
+ if (strcmp(opt->optname, optname) == 0 && opt->is_libpq_opt)
+ return true;
+ }
+ return false;
+ }
+
+ /*
+ * Generate key-value arrays which includes only libpq options from the list
+ * which contains any kind of options.
+ */
+ int
+ ExtractConnectionOptions(List *defelems, const char **keywords, const char **values)
+ {
+ ListCell *lc;
+ int i;
+
+ i = 0;
+ foreach(lc, defelems)
+ {
+ DefElem *d = (DefElem *) lfirst(lc);
+ if (is_libpq_option(d->defname))
+ {
+ keywords[i] = d->defname;
+ values[i] = defGetString(d);
+ i++;
+ }
+ }
+ return i;
+ }
diff --git a/contrib/pgsql_fdw/pgsql_fdw--1.0.sql b/contrib/pgsql_fdw/pgsql_fdw--1.0.sql
index ...b0ea2b2 .
*** a/contrib/pgsql_fdw/pgsql_fdw--1.0.sql
--- b/contrib/pgsql_fdw/pgsql_fdw--1.0.sql
***************
*** 0 ****
--- 1,39 ----
+ /* contrib/pgsql_fdw/pgsql_fdw--1.0.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "CREATE EXTENSION pgsql_fdw" to load this file. \quit
+
+ CREATE FUNCTION pgsql_fdw_handler()
+ RETURNS fdw_handler
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pgsql_fdw_validator(text[], oid)
+ RETURNS void
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT;
+
+ CREATE FOREIGN DATA WRAPPER pgsql_fdw
+ HANDLER pgsql_fdw_handler
+ VALIDATOR pgsql_fdw_validator;
+
+ /* connection management functions and view */
+ CREATE FUNCTION pgsql_fdw_get_connections(out srvid oid, out usesysid oid)
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pgsql_fdw_disconnect(oid, oid)
+ RETURNS text
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT;
+
+ CREATE VIEW pgsql_fdw_connections AS
+ SELECT c.srvid srvid,
+ s.srvname srvname,
+ c.usesysid usesysid,
+ pg_get_userbyid(c.usesysid) usename
+ FROM pgsql_fdw_get_connections() c
+ JOIN pg_catalog.pg_foreign_server s ON (s.oid = c.srvid);
+ GRANT SELECT ON pgsql_fdw_connections TO public;
+
diff --git a/contrib/pgsql_fdw/pgsql_fdw.c b/contrib/pgsql_fdw/pgsql_fdw.c
index ...370d240 .
*** a/contrib/pgsql_fdw/pgsql_fdw.c
--- b/contrib/pgsql_fdw/pgsql_fdw.c
***************
*** 0 ****
--- 1,1014 ----
+ /*-------------------------------------------------------------------------
+ *
+ * pgsql_fdw.c
+ * foreign-data wrapper for remote PostgreSQL servers.
+ *
+ * Copyright (c) 2012, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pgsql_fdw/pgsql_fdw.c
+ *
+ *-------------------------------------------------------------------------
+ */
+ #include "postgres.h"
+ #include "fmgr.h"
+
+ #include "catalog/pg_foreign_server.h"
+ #include "catalog/pg_foreign_table.h"
+ #include "commands/defrem.h"
+ #include "commands/explain.h"
+ #include "foreign/fdwapi.h"
+ #include "funcapi.h"
+ #include "miscadmin.h"
+ #include "nodes/nodeFuncs.h"
+ #include "optimizer/cost.h"
+ #include "optimizer/pathnode.h"
+ #include "optimizer/planmain.h"
+ #include "optimizer/restrictinfo.h"
+ #include "utils/lsyscache.h"
+ #include "utils/memutils.h"
+ #include "utils/rel.h"
+
+ #include "pgsql_fdw.h"
+ #include "connection.h"
+
+ PG_MODULE_MAGIC;
+
+ /*
+ * Default fetch count for cursor. This can be overridden by fetch_count FDW
+ * option.
+ */
+ #define DEFAULT_FETCH_COUNT 10000
+
+ /*
+ * Cost to establish a connection.
+ * XXX: should be configurable per server?
+ */
+ #define CONNECTION_COSTS 100.0
+
+ /*
+ * Cost to transfer 1 byte from remote server.
+ * XXX: should be configurable per server?
+ */
+ #define TRANSFER_COSTS_PER_BYTE 0.001
+
+ /*
+ * Cursors which are used together in a local query require different name, so
+ * we use simple incremental name for that purpose. We don't care wrap around
+ * of cursor_id because it's hard to imagine that 2^32 cursors are used in a
+ * query.
+ */
+ #define CURSOR_NAME_FORMAT "pgsql_fdw_cursor_%u"
+ static uint32 cursor_id = 0;
+
+ /*
+ * FDW-specific information for RelOptInfo.fdw_private. This is used to pass
+ * information from pgsqlGetForeignRelSize to pgsqlGetForeignPaths.
+ */
+ typedef struct PgsqlFdwPlanState {
+ /*
+ * These are generated in GetForeignRelSize, and also used in subsequent
+ * GetForeignPaths.
+ * XXX If we can have enough statistics of remote data, we can defer
+ * estimating cost to GetForeignPaths, so then these would be unnecessary.
+ */
+ char *sql;
+ Cost startup_cost;
+ Cost total_cost;
+ } PgsqlFdwPlanState;
+
+ /*
+ * Index of FDW-private information, such as remote SQL statements, which are
+ * stored in fdw_private list.
+ */
+ enum FdwPrivateIndex {
+ FdwPrivateSelectSql,
+ FdwPrivateDeclareSql,
+ FdwPrivateFetchSql,
+ FdwPrivateResetSql,
+ FdwPrivateCloseSql,
+
+ /* # of elements stored in the list fdw_private */
+ FdwPrivateNum,
+ };
+
+ /*
+ * Describes an execution state of a foreign scan against a foreign table
+ * using pgsql_fdw.
+ */
+ typedef struct PgsqlFdwExecutionState
+ {
+ MemoryContext scan_cxt; /* context for per-scan lifespan data */
+
+ List *fdw_private; /* FDW-private information */
+ PGconn *conn; /* connection for the scan */
+
+ Oid *param_types; /* type array of external parameter */
+ const char **param_values; /* value array of external parameter */
+
+ int attnum; /* # of non-dropped attribute */
+ char **col_values; /* column value buffer */
+ AttInMetadata *attinmeta; /* attribute metadata */
+
+ Tuplestorestate *tuples; /* result of the scan */
+ } PgsqlFdwExecutionState;
+
+ /*
+ * SQL functions
+ */
+ extern Datum pgsql_fdw_handler(PG_FUNCTION_ARGS);
+ PG_FUNCTION_INFO_V1(pgsql_fdw_handler);
+
+ /*
+ * FDW callback routines
+ */
+ static void pgsqlGetForeignRelSize(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid);
+ static void pgsqlGetForeignPaths(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid);
+ static ForeignScan *pgsqlGetForeignPlan(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid,
+ ForeignPath *best_path,
+ List *tlist,
+ List *scan_clauses);
+ static void pgsqlExplainForeignScan(ForeignScanState *node, ExplainState *es);
+ static void pgsqlBeginForeignScan(ForeignScanState *node, int eflags);
+ static TupleTableSlot *pgsqlIterateForeignScan(ForeignScanState *node);
+ static void pgsqlReScanForeignScan(ForeignScanState *node);
+ static void pgsqlEndForeignScan(ForeignScanState *node);
+
+ /*
+ * Helper functions
+ */
+ static void get_remote_estimate(const char *sql,
+ PGconn *conn,
+ double *rows,
+ int *width,
+ Cost *startup_cost,
+ Cost *total_cost);
+ static void adjust_costs(double rows, int width,
+ Cost *startup_cost, Cost *total_cost);
+ static void execute_query(ForeignScanState *node);
+ static PGresult *fetch_result(ForeignScanState *node);
+ static void store_result(ForeignScanState *node, PGresult *res);
+
+ /* Exported functions, but not written in pgsql_fdw.h. */
+ void _PG_init(void);
+ void _PG_fini(void);
+
+ /*
+ * Module-specific initialization.
+ */
+ void
+ _PG_init(void)
+ {
+ }
+
+ /*
+ * Module-specific clean up.
+ */
+ void
+ _PG_fini(void)
+ {
+ }
+
+ /*
+ * The content of FdwRoutine of pgsql_fdw is never changed, so we use one
+ * static object for all scan.
+ */
+ static FdwRoutine fdwroutine = {
+
+ /* Node type */
+ T_FdwRoutine,
+
+ /* Required handler functions. */
+ pgsqlGetForeignRelSize,
+ pgsqlGetForeignPaths,
+ pgsqlGetForeignPlan,
+ pgsqlExplainForeignScan,
+ pgsqlBeginForeignScan,
+ pgsqlIterateForeignScan,
+ pgsqlReScanForeignScan,
+ pgsqlEndForeignScan,
+
+ /* Optional handler functions. */
+ };
+
+ /*
+ * Foreign-data wrapper handler function: return a struct with pointers
+ * to my callback routines.
+ */
+ Datum
+ pgsql_fdw_handler(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_POINTER(&fdwroutine);
+ }
+
+ /*
+ * pgsqlGetForeignRelSize
+ * Estimate # of rows and width of the result of the scan
+ *
+ * If we have had enough statistics about foreign data, we can get good
+ * estimate of rows and width of this scan by calling
+ * set_baserel_size_estimates(), therefore we can avoid executing expensive
+ * EXPLAIN on remote side. To achieve this, we need ANALYZE support for
+ * foreign tables by backend, and FDW-specific implementation of updating
+ * statistics. But we don't have them at this moment, so estimate # of result
+ * rows on the basis of remote estimate for unconditional query and selectivity
+ * of the clauses.
+ */
+ static void
+ pgsqlGetForeignRelSize(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid)
+ {
+ char *sql;
+ ForeignTable *table;
+ ForeignServer *server;
+ UserMapping *user;
+ PGconn *conn;
+ double rows;
+ int width;
+ Cost startup_cost;
+ Cost total_cost;
+ PgsqlFdwPlanState *planstate;
+
+ /* Retrieve catalog objects which are necessary to estimate rows. */
+ table = GetForeignTable(foreigntableid);
+ server = GetForeignServer(table->serverid);
+ user = GetUserMapping(GetOuterUserId(), server->serverid);
+
+ /*
+ * Create plain SELECT statement with no WHERE clause for this scan to
+ * obtain meaningful rows estimation by executing EXPLAIN on remote server.
+ */
+ sql = deparseSimpleSql(foreigntableid, root, baserel, table);
+ conn = GetConnection(server, user, false);
+ get_remote_estimate(sql, conn, &rows, &width, &startup_cost, &total_cost);
+ ReleaseConnection(conn);
+
+ /*
+ * Estimate # of rows by calling set_baserel_size_estimates() with setting
+ * baserel->tuples to the rows estimation of remote EXPLAIN. We override
+ * width estimation because set_baserel_size_estimates estimates it on the
+ * basis of statistics which we don't actually have.
+ */
+ baserel->tuples = rows;
+ set_baserel_size_estimates(root, baserel);
+ baserel->width = width;
+
+ /*
+ * Pack obtained information into a object and store it in FDW-private area
+ * of RelOptInfo to pass them to subsequent functions.
+ */
+ planstate = palloc(sizeof(PgsqlFdwExecutionState));
+ planstate->sql = sql;
+ planstate->startup_cost = startup_cost;
+ planstate->total_cost = total_cost;
+ baserel->fdw_private = (void *) planstate;
+ }
+
+ /*
+ * pgsqlGetForeignPaths
+ * Create possible scan paths for a scan on the foreign table
+ */
+ static void
+ pgsqlGetForeignPaths(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid)
+ {
+ PgsqlFdwPlanState *planstate = (PgsqlFdwPlanState *) baserel->fdw_private;
+ List *fdw_private = NIL;
+ ForeignPath *path;
+
+ /*
+ * Cost estimation should be modified to respect cost of establishing
+ * connection and transferring data.
+ */
+ adjust_costs(baserel->rows,
+ baserel->width,
+ &planstate->startup_cost,
+ &planstate->total_cost);
+
+ /*
+ * Create simplest ForeignScan path node, counterpart of SeqScan for
+ * regular tables, for this scan, and add it to baserel.
+ */
+ fdw_private = list_make1(makeString(planstate->sql));
+ path = create_foreignscan_path(root, baserel,
+ baserel->rows,
+ planstate->startup_cost,
+ planstate->total_cost,
+ NIL, /* no pathkeys */
+ NULL, /* no outer rel either */
+ NIL, /* no param clause */
+ fdw_private); /* pass only simple SQL */
+ add_path(baserel, (Path *) path);
+
+ /*
+ * XXX We can consider sorted path here if we know that foreign table is
+ * indexed on remote end. For this purpose, should we support FOREIGN
+ * INDEX to represent possible sets of sort keys which are relatively
+ * efficient?
+ */
+ }
+
+ /*
+ * pgsqlGetForeignPlan
+ * Create ForeignScan plan node which implements selected best path
+ */
+ static ForeignScan *
+ pgsqlGetForeignPlan(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid,
+ ForeignPath *best_path,
+ List *tlist,
+ List *scan_clauses)
+ {
+ Index scan_relid = baserel->relid;
+ List *fdw_private = best_path->fdw_private;
+ ForeignTable *table;
+ ForeignServer *server;
+ ListCell *lc;
+ char name[128]; /* must be larger than format + 10 */
+ StringInfoData cursor;
+ DefElem *def;
+ int fetch_count = DEFAULT_FETCH_COUNT;
+ char *sql;
+
+ /*
+ * We have no native ability to evaluate restriction clauses, so we just
+ * put all the scan_clauses into the plan node's qual list for the
+ * executor to check. So all we have to do here is strip RestrictInfo
+ * nodes from the clauses and ignore pseudoconstants (which will be
+ * handled elsewhere).
+ */
+ scan_clauses = extract_actual_clauses(scan_clauses, false);
+
+ /*
+ * Use specified fetch_count instead of default value, if any. Foreign
+ * table option overrides server option.
+ */
+ table = GetForeignTable(foreigntableid);
+ foreach(lc, table->options)
+ {
+ def = (DefElem *) lfirst(lc);
+ if (strcmp(def->defname, "fetch_count") == 0)
+ break;
+
+ }
+ if (lc == NULL)
+ {
+ server = GetForeignServer(table->serverid);
+ foreach(lc, server->options)
+ {
+ def = (DefElem *) lfirst(lc);
+ if (strcmp(def->defname, "fetch_count") == 0)
+ break;
+
+ }
+ }
+ if (lc != NULL)
+ fetch_count = strtol(defGetString(def), NULL, 10);
+ elog(DEBUG1, "relid=%u fetch_count=%d", foreigntableid, fetch_count);
+
+ /*
+ * We store some more information in FdwPlan to pass them beyond the
+ * boundary between planner and executor. Finally FdwPlan using cursor
+ * would hold items below:
+ *
+ * 1) plain SELECT statement (already added above)
+ * 2) SQL statement used to declare cursor
+ * 3) SQL statement used to fetch rows from cursor
+ * 4) SQL statement used to reset cursor
+ * 5) SQL statement used to close cursor
+ *
+ * These items are indexed with the enum FdwPrivateIndex, so an item
+ * can be accessed directly via list_nth(). For example of FETCH
+ * statement:
+ * list_nth(fdw_private, FdwPrivateFetchSql)
+ */
+ sql = strVal(list_nth(fdw_private, FdwPrivateSelectSql));
+
+ /* Construct cursor name from sequential value */
+ sprintf(name, CURSOR_NAME_FORMAT, cursor_id++);
+
+ /* Construct statement to declare cursor */
+ initStringInfo(&cursor);
+ appendStringInfo(&cursor, "DECLARE %s SCROLL CURSOR FOR %s", name, sql);
+ fdw_private = lappend(fdw_private, makeString(cursor.data));
+
+ /* Construct statement to fetch rows from cursor */
+ initStringInfo(&cursor);
+ appendStringInfo(&cursor, "FETCH %d FROM %s", fetch_count, name);
+ fdw_private = lappend(fdw_private, makeString(cursor.data));
+
+ /* Construct statement to reset cursor */
+ initStringInfo(&cursor);
+ appendStringInfo(&cursor, "MOVE ABSOLUTE 0 FROM %s", name);
+ fdw_private = lappend(fdw_private, makeString(cursor.data));
+
+ /* Construct statement to close cursor */
+ initStringInfo(&cursor);
+ appendStringInfo(&cursor, "CLOSE %s", name);
+ fdw_private = lappend(fdw_private, makeString(cursor.data));
+
+ /* Create the ForeignScan node with fdw_private of selected path. */
+ return make_foreignscan(tlist,
+ scan_clauses,
+ scan_relid,
+ NIL,
+ fdw_private);
+ }
+
+ /*
+ * pgsqlExplainForeignScan
+ * Produce extra output for EXPLAIN
+ */
+ static void
+ pgsqlExplainForeignScan(ForeignScanState *node, ExplainState *es)
+ {
+ List *fdw_private;
+ char *sql;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwPrivateDeclareSql));
+ ExplainPropertyText("Remote SQL", sql, es);
+ }
+
+ /*
+ * pgsqlBeginForeignScan
+ * Initiate access to a foreign PostgreSQL table.
+ */
+ static void
+ pgsqlBeginForeignScan(ForeignScanState *node, int eflags)
+ {
+ PgsqlFdwExecutionState *festate;
+ PGconn *conn;
+ Oid relid;
+ ForeignTable *table;
+ ForeignServer *server;
+ UserMapping *user;
+ TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
+
+ /*
+ * Do nothing in EXPLAIN (no ANALYZE) case. node->fdw_state stays NULL.
+ */
+ if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
+ return;
+
+ /*
+ * Save state in node->fdw_state.
+ */
+ festate = (PgsqlFdwExecutionState *) palloc(sizeof(PgsqlFdwExecutionState));
+ festate->fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+
+ /*
+ * Create context for per-scan tuplestore under per-query context.
+ */
+ festate->scan_cxt = AllocSetContextCreate(node->ss.ps.state->es_query_cxt,
+ "pgsql_fdw",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ /*
+ * Get connection to the foreign server. Connection manager would
+ * establish new connection if necessary.
+ */
+ relid = RelationGetRelid(node->ss.ss_currentRelation);
+ table = GetForeignTable(relid);
+ server = GetForeignServer(table->serverid);
+ user = GetUserMapping(GetOuterUserId(), server->serverid);
+ conn = GetConnection(server, user, true);
+ festate->conn = conn;
+
+ /* Result will be filled in first Iterate call. */
+ festate->tuples = NULL;
+
+ /* Allocate buffers for column values. */
+ {
+ TupleDesc tupdesc = slot->tts_tupleDescriptor;
+ festate->col_values = palloc(sizeof(char *) * tupdesc->natts);
+ festate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+ }
+
+ /* Allocate buffers for query parameters. */
+ {
+ ParamListInfo params = node->ss.ps.state->es_param_list_info;
+ int numParams = params ? params->numParams : 0;
+
+ if (numParams > 0)
+ {
+ festate->param_types = palloc0(sizeof(Oid) * numParams);
+ festate->param_values = palloc0(sizeof(char *) * numParams);
+ }
+ else
+ {
+ festate->param_types = NULL;
+ festate->param_values = NULL;
+ }
+ }
+
+ /* Store FDW-specific state into ForeignScanState */
+ node->fdw_state = (void *) festate;
+
+ return;
+ }
+
+ /*
+ * pgsqlIterateForeignScan
+ * Retrieve next row from the result set, or clear tuple slot to indicate
+ * EOF.
+ *
+ * Note that using per-query context when retrieving tuples from
+ * tuplestore to ensure that returned tuples can survive until next
+ * iteration because the tuple is released implicitly via ExecClearTuple.
+ * Retrieving a tuple from tuplestore in CurrentMemoryContext (it's a
+ * per-tuple context), ExecClearTuple will free dangling pointer.
+ */
+ static TupleTableSlot *
+ pgsqlIterateForeignScan(ForeignScanState *node)
+ {
+ PgsqlFdwExecutionState *festate;
+ TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
+ PGresult *res = NULL;
+ MemoryContext oldcontext = CurrentMemoryContext;
+
+ festate = (PgsqlFdwExecutionState *) node->fdw_state;
+
+ /*
+ * If this is the first call after Begin, we need to execute remote query.
+ * If the query needs cursor, we declare a cursor at first call and fetch
+ * from it in later calls.
+ */
+ if (festate->tuples == NULL)
+ execute_query(node);
+
+ /*
+ * If enough tuples are left in tuplestore, just return next tuple from it.
+ *
+ * It is necessary to switch to per-scan context to make returned tuple
+ * valid until next IterateForeignScan call, because it will be released
+ * with ExecClearTuple then. Otherwise, picked tuple is allocated in
+ * per-tuple context, and double-free of that tuple might happen.
+ */
+ MemoryContextSwitchTo(festate->scan_cxt);
+ if (tuplestore_gettupleslot(festate->tuples, true, false, slot))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ return slot;
+ }
+ MemoryContextSwitchTo(oldcontext);
+
+ /*
+ * Here we need to clear partial result and fetch next bunch of tuples from
+ * from the cursor for the scan. If the fetch returns no tuple, the scan
+ * has reached the end.
+ *
+ * PGresult must be released before leaving this function.
+ */
+ PG_TRY();
+ {
+ res = fetch_result(node);
+ store_result(node, res);
+ PQclear(res);
+ res = NULL;
+ }
+ PG_CATCH();
+ {
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ /*
+ * If we got more tuples from the server cursor, return next tuple from
+ * tuplestore.
+ */
+ MemoryContextSwitchTo(festate->scan_cxt);
+ if (tuplestore_gettupleslot(festate->tuples, true, false, slot))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ return slot;
+ }
+ MemoryContextSwitchTo(oldcontext);
+
+ /* We don't have any result even in remote server cursor. */
+ ExecClearTuple(slot);
+ return slot;
+ }
+
+ /*
+ * pgsqlReScanForeignScan
+ * - Restart this scan by resetting fetch location.
+ */
+ static void
+ pgsqlReScanForeignScan(ForeignScanState *node)
+ {
+ List *fdw_private;
+ char *sql;
+ PGconn *conn;
+ PGresult *res = NULL;
+ PgsqlFdwExecutionState *festate;
+
+ festate = (PgsqlFdwExecutionState *) node->fdw_state;
+
+ /* If we have not opened cursor yet, nothing to do. */
+ if (festate->tuples == NULL)
+ return;
+
+ /* Discard fetch results if any. */
+ tuplestore_clear(festate->tuples);
+
+ /* PGresult must be released before leaving this function. */
+ PG_TRY();
+ {
+ /* Reset cursor */
+ fdw_private = festate->fdw_private;
+ conn = festate->conn;
+ sql = strVal(list_nth(fdw_private, FdwPrivateResetSql));
+ res = PQexec(conn, sql);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ ereport(ERROR,
+ (errmsg("could not rewind cursor"),
+ errdetail("%s", PQerrorMessage(conn)),
+ errhint("%s", sql)));
+ }
+ PQclear(res);
+ res = NULL;
+ }
+ PG_CATCH();
+ {
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+ }
+
+ /*
+ * pgsqlEndForeignScan
+ * Finish scanning foreign table and dispose objects used for this scan
+ */
+ static void
+ pgsqlEndForeignScan(ForeignScanState *node)
+ {
+ List *fdw_private;
+ char *sql;
+ PGconn *conn;
+ PGresult *res = NULL;
+ PgsqlFdwExecutionState *festate;
+
+ festate = (PgsqlFdwExecutionState *) node->fdw_state;
+
+ /* if festate is NULL, we are in EXPLAIN; nothing to do */
+ if (festate == NULL)
+ return;
+
+ /* If we have not opened cursor yet, nothing to do. */
+ if (festate->tuples == NULL)
+ return;
+
+ /* Discard fetch results */
+ tuplestore_end(festate->tuples);
+ festate->tuples = NULL;
+
+ /* PGresult must be released before leaving this function. */
+ PG_TRY();
+ {
+ /* Close cursor */
+ fdw_private = festate->fdw_private;
+ conn = festate->conn;
+ sql = strVal(list_nth(fdw_private, FdwPrivateCloseSql));
+ res = PQexec(conn, sql);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ ereport(ERROR,
+ (errmsg("could not close cursor"),
+ errdetail("%s", PQerrorMessage(conn)),
+ errhint("%s", sql)));
+ }
+ PQclear(res);
+ res = NULL;
+ }
+ PG_CATCH();
+ {
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ ReleaseConnection(festate->conn);
+
+ MemoryContextDelete(festate->scan_cxt);
+ festate->scan_cxt = NULL;
+ }
+
+ /*
+ * Estimate costs of executing given SQL statement.
+ */
+ static void
+ get_remote_estimate(const char *sql, PGconn *conn,
+ double *rows, int *width,
+ Cost *startup_cost, Cost *total_cost)
+ {
+ PGresult *res = NULL;
+ StringInfoData buf;
+ char *plan;
+ char *p;
+ int n;
+
+ /*
+ * Construct EXPLAIN statement with given SQL statement.
+ */
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "EXPLAIN %s", sql);
+
+ /* PGresult must be released before leaving this function. */
+ PG_TRY();
+ {
+ res = PQexec(conn, buf.data);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) == 0)
+ {
+ char *msg;
+
+ msg = pstrdup(PQerrorMessage(conn));
+ ereport(ERROR,
+ (errmsg("could not execute EXPLAIN for cost estimation"),
+ errdetail("%s", msg),
+ errhint("%s", sql)));
+ }
+
+ /*
+ * Find estimation portion from top plan node. Here we search opening
+ * parentheses from the end of the line to avoid finding unexpected
+ * parentheses.
+ */
+ plan = PQgetvalue(res, 0, 0);
+ p = strrchr(plan, '(');
+ if (p == NULL)
+ elog(ERROR, "wrong EXPLAIN output: %s", plan);
+ n = sscanf(p,
+ "(cost=%lf..%lf rows=%lf width=%d)",
+ startup_cost, total_cost, rows, width);
+ if (n != 4)
+ elog(ERROR, "could not get estimation from EXPLAIN output");
+
+ PQclear(res);
+ res = NULL;
+ }
+ PG_CATCH();
+ {
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+ }
+
+ /*
+ * Adjust costs estimated on remote end with some overheads such as connection
+ * and data transfer.
+ */
+ static void
+ adjust_costs(double rows, int width, Cost *startup_cost, Cost *total_cost)
+ {
+ /*
+ * TODO Selectivity of quals which are NOT pushed down should be also
+ * considered.
+ */
+
+ /* add cost to establish connection. */
+ *startup_cost += CONNECTION_COSTS;
+ *total_cost += CONNECTION_COSTS;
+
+ /* add cost to transfer result. */
+ *total_cost += TRANSFER_COSTS_PER_BYTE * width * rows;
+ *total_cost += cpu_tuple_cost * rows;
+ }
+
+ /*
+ * Execute remote query with current parameters.
+ */
+ static void
+ execute_query(ForeignScanState *node)
+ {
+ List *fdw_private;
+ PgsqlFdwExecutionState *festate;
+ ParamListInfo params = node->ss.ps.state->es_param_list_info;
+ int numParams = params ? params->numParams : 0;
+ Oid *types = NULL;
+ const char **values = NULL;
+ char *sql;
+ PGconn *conn;
+ PGresult *res = NULL;
+
+ festate = (PgsqlFdwExecutionState *) node->fdw_state;
+ types = festate->param_types;
+ values = festate->param_values;
+
+ /*
+ * Construct parameter array in text format. We don't release memory for
+ * the arrays explicitly, because the memory usage would not be very large,
+ * and anyway they will be released in context cleanup.
+ */
+ if (numParams > 0)
+ {
+ int i;
+
+ for (i = 0; i < numParams; i++)
+ {
+ types[i] = params->params[i].ptype;
+ if (params->params[i].isnull)
+ values[i] = NULL;
+ else
+ {
+ Oid out_func_oid;
+ bool isvarlena;
+ FmgrInfo func;
+
+ getTypeOutputInfo(types[i], &out_func_oid, &isvarlena);
+ fmgr_info(out_func_oid, &func);
+ values[i] = OutputFunctionCall(&func, params->params[i].value);
+ }
+ }
+ }
+
+ /* PGresult must be released before leaving this function. */
+ PG_TRY();
+ {
+ /*
+ * Execute remote query with parameters.
+ */
+ conn = festate->conn;
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwPrivateDeclareSql));
+ res = PQexecParams(conn, sql, numParams, types, values, NULL, NULL, 0);
+
+ /*
+ * If the query has failed, reporting details is enough here.
+ * Connection(s) which are used by this query (at least used by
+ * pgsql_fdw) will be cleaned up by the foreign connection manager.
+ */
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ ereport(ERROR,
+ (errmsg("could not declare cursor"),
+ errdetail("%s", PQerrorMessage(conn)),
+ errhint("%s", sql)));
+ }
+
+ /* Discard result of DECLARE statement. */
+ PQclear(res);
+ res = NULL;
+
+ /* Fetch first bunch of the result and store them into tuplestore. */
+ res = fetch_result(node);
+ store_result(node, res);
+ PQclear(res);
+ res = NULL;
+ }
+ PG_CATCH();
+ {
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+ }
+
+ /*
+ * Fetch next partial result from remote server.
+ *
+ * Once this function has returned result records as PGresult, caller is
+ * responsible to release it, so caller should put codes which might throw
+ * exception in PG_TRY block. When an exception has been caught, release
+ * PGresult and re-throw the exception in PG_CATCH block.
+ */
+ static PGresult *
+ fetch_result(ForeignScanState *node)
+ {
+ PgsqlFdwExecutionState *festate;
+ List *fdw_private;
+ char *sql;
+ PGconn *conn;
+ PGresult *res;
+
+ festate = (PgsqlFdwExecutionState *) node->fdw_state;
+
+ /* Retrieve information for fetching result. */
+ fdw_private = festate->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwPrivateFetchSql));
+ conn = festate->conn;
+
+ /*
+ * Fetch result from remote server. In error case, we must release
+ * PGresult in this function to avoid memory leak because caller can't
+ * get the reference.
+ */
+ res = PQexec(conn, sql);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ PQclear(res);
+ ereport(ERROR,
+ (errmsg("could not fetch rows from foreign server"),
+ errdetail("%s", PQerrorMessage(conn)),
+ errhint("%s", sql)));
+ }
+
+ return res;
+ }
+
+ /*
+ * Create tuples from PGresult and store them into tuplestore.
+ *
+ * Caller must use PG_TRY block to catch exception and release PGresult
+ * surely.
+ */
+ static void
+ store_result(ForeignScanState *node, PGresult *res)
+ {
+ int rows;
+ int row;
+ int i;
+ int nfields;
+ int attnum; /* number of non-dropped columns */
+ Form_pg_attribute *attrs;
+ TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
+ TupleDesc tupdesc = slot->tts_tupleDescriptor;
+ PgsqlFdwExecutionState *festate;
+
+ festate = (PgsqlFdwExecutionState *) node->fdw_state;
+ rows = PQntuples(res);
+ nfields = PQnfields(res);
+ attrs = tupdesc->attrs;
+
+ /* First, ensure that the tuplestore is empty. */
+ if (festate->tuples == NULL)
+ {
+ MemoryContext oldcontext = CurrentMemoryContext;
+
+ /*
+ * Create tuplestore to store result of the query in per-query context.
+ * Note that we use this memory context to avoid memory leak in error
+ * cases.
+ */
+ MemoryContextSwitchTo(festate->scan_cxt);
+ festate->tuples = tuplestore_begin_heap(false, false, work_mem);
+ MemoryContextSwitchTo(oldcontext);
+ }
+ else
+ {
+ /* We already have tuplestore, just need to clear contents of it. */
+ tuplestore_clear(festate->tuples);
+ }
+
+ /* count non-dropped columns */
+ for (attnum = 0, i = 0; i < tupdesc->natts; i++)
+ if (!attrs[i]->attisdropped)
+ attnum++;
+
+ /* check result and tuple descriptor have the same number of columns */
+ if (attnum > 0 && attnum != nfields)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("remote query result rowtype does not match "
+ "the specified FROM clause rowtype"),
+ errdetail("expected %d, actual %d", attnum, nfields)));
+
+ /* put a tuples into the slot */
+ for (row = 0; row < rows; row++)
+ {
+ int j;
+ HeapTuple tuple;
+
+ for (i = 0, j = 0; i < tupdesc->natts; i++)
+ {
+ /* skip dropped columns. */
+ if (attrs[i]->attisdropped)
+ {
+ festate->col_values[i] = NULL;
+ continue;
+ }
+
+ if (PQgetisnull(res, row, j))
+ festate->col_values[i] = NULL;
+ else
+ festate->col_values[i] = PQgetvalue(res, row, j);
+ j++;
+ }
+
+ /*
+ * Build the tuple and put it into the slot.
+ * We don't have to free the tuple explicitly because it's been
+ * allocated in the per-tuple context.
+ */
+ tuple = BuildTupleFromCStrings(festate->attinmeta, festate->col_values);
+ tuplestore_puttuple(festate->tuples, tuple);
+ }
+
+ tuplestore_donestoring(festate->tuples);
+ }
+
diff --git a/contrib/pgsql_fdw/pgsql_fdw.control b/contrib/pgsql_fdw/pgsql_fdw.control
index ...0a9c8f4 .
*** a/contrib/pgsql_fdw/pgsql_fdw.control
--- b/contrib/pgsql_fdw/pgsql_fdw.control
***************
*** 0 ****
--- 1,5 ----
+ # pgsql_fdw extension
+ comment = 'foreign-data wrapper for remote PostgreSQL servers'
+ default_version = '1.0'
+ module_pathname = '$libdir/pgsql_fdw'
+ relocatable = true
diff --git a/contrib/pgsql_fdw/pgsql_fdw.h b/contrib/pgsql_fdw/pgsql_fdw.h
index ...6eb99d5 .
*** a/contrib/pgsql_fdw/pgsql_fdw.h
--- b/contrib/pgsql_fdw/pgsql_fdw.h
***************
*** 0 ****
--- 1,32 ----
+ /*-------------------------------------------------------------------------
+ *
+ * pgsql_fdw.h
+ * foreign-data wrapper for remote PostgreSQL servers.
+ *
+ * Copyright (c) 2012, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pgsql_fdw/pgsql_fdw.h
+ *
+ *-------------------------------------------------------------------------
+ */
+
+ #ifndef PGSQL_FDW_H
+ #define PGSQL_FDW_H
+
+ #include "postgres.h"
+ #include "foreign/foreign.h"
+ #include "nodes/relation.h"
+
+ /* in option.c */
+ int ExtractConnectionOptions(List *defelems,
+ const char **keywords,
+ const char **values);
+
+ /* in deparse.c */
+ char *deparseSimpleSql(Oid relid,
+ PlannerInfo *root,
+ RelOptInfo *baserel,
+ ForeignTable *table);
+
+ #endif /* PGSQL_FDW_H */
diff --git a/contrib/pgsql_fdw/sql/pgsql_fdw.sql b/contrib/pgsql_fdw/sql/pgsql_fdw.sql
index ...b3fac96 .
*** a/contrib/pgsql_fdw/sql/pgsql_fdw.sql
--- b/contrib/pgsql_fdw/sql/pgsql_fdw.sql
***************
*** 0 ****
--- 1,248 ----
+ -- ===================================================================
+ -- create FDW objects
+ -- ===================================================================
+
+ -- Clean up in case a prior regression run failed
+
+ -- Suppress NOTICE messages when roles don't exist
+ SET client_min_messages TO 'error';
+
+ DROP ROLE IF EXISTS pgsql_fdw_user;
+
+ RESET client_min_messages;
+
+ CREATE ROLE pgsql_fdw_user LOGIN SUPERUSER;
+ SET SESSION AUTHORIZATION 'pgsql_fdw_user';
+
+ CREATE EXTENSION pgsql_fdw;
+
+ CREATE SERVER loopback1 FOREIGN DATA WRAPPER pgsql_fdw;
+ CREATE SERVER loopback2 FOREIGN DATA WRAPPER pgsql_fdw
+ OPTIONS (dbname 'contrib_regression');
+
+ CREATE USER MAPPING FOR public SERVER loopback1
+ OPTIONS (user 'value', password 'value');
+ CREATE USER MAPPING FOR pgsql_fdw_user SERVER loopback2;
+
+ CREATE FOREIGN TABLE ft1 (
+ c0 int,
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 timestamptz,
+ c5 timestamp,
+ c6 varchar(10),
+ c7 char(10)
+ ) SERVER loopback2;
+ ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
+
+ CREATE FOREIGN TABLE ft2 (
+ c0 int,
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 timestamptz,
+ c5 timestamp,
+ c6 varchar(10),
+ c7 char(10)
+ ) SERVER loopback2;
+ ALTER FOREIGN TABLE ft2 DROP COLUMN c0;
+
+ -- ===================================================================
+ -- create objects used through FDW
+ -- ===================================================================
+ CREATE SCHEMA "S 1";
+ CREATE TABLE "S 1"."T 1" (
+ "C 1" int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ c4 timestamptz,
+ c5 timestamp,
+ c6 varchar(10),
+ c7 char(10),
+ CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
+ );
+ CREATE TABLE "S 1"."T 2" (
+ c1 int NOT NULL,
+ c2 text,
+ CONSTRAINT t2_pkey PRIMARY KEY (c1)
+ );
+
+ BEGIN;
+ TRUNCATE "S 1"."T 1";
+ INSERT INTO "S 1"."T 1"
+ SELECT id,
+ id % 10,
+ to_char(id, 'FM00000'),
+ '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
+ '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
+ id % 10,
+ id % 10
+ FROM generate_series(1, 1000) id;
+ TRUNCATE "S 1"."T 2";
+ INSERT INTO "S 1"."T 2"
+ SELECT id,
+ 'AAA' || to_char(id, 'FM000')
+ FROM generate_series(1, 100) id;
+ COMMIT;
+
+ -- ===================================================================
+ -- tests for pgsql_fdw_validator
+ -- ===================================================================
+ ALTER FOREIGN DATA WRAPPER pgsql_fdw OPTIONS (host 'value'); -- ERROR
+ -- requiressl, krbsrvname and gsslib are omitted because they depend on
+ -- configure option
+ ALTER SERVER loopback1 OPTIONS (
+ authtype 'value',
+ service 'value',
+ connect_timeout 'value',
+ dbname 'value',
+ host 'value',
+ hostaddr 'value',
+ port 'value',
+ --client_encoding 'value',
+ tty 'value',
+ options 'value',
+ application_name 'value',
+ --fallback_application_name 'value',
+ keepalives 'value',
+ keepalives_idle 'value',
+ keepalives_interval 'value',
+ -- requiressl 'value',
+ sslcompression 'value',
+ sslmode 'value',
+ sslcert 'value',
+ sslkey 'value',
+ sslrootcert 'value',
+ sslcrl 'value'
+ --requirepeer 'value',
+ -- krbsrvname 'value',
+ -- gsslib 'value',
+ --replication 'value'
+ );
+ ALTER SERVER loopback1 OPTIONS (user 'value'); -- ERROR
+ ALTER SERVER loopback2 OPTIONS (ADD fetch_count '2');
+ ALTER USER MAPPING FOR public SERVER loopback1
+ OPTIONS (DROP user, DROP password);
+ ALTER USER MAPPING FOR public SERVER loopback1
+ OPTIONS (host 'value'); -- ERROR
+ ALTER FOREIGN TABLE ft1 OPTIONS (nspname 'S 1', relname 'T 1');
+ ALTER FOREIGN TABLE ft2 OPTIONS (nspname 'S 1', relname 'T 1', fetch_count '100');
+ ALTER FOREIGN TABLE ft1 OPTIONS (invalid 'value'); -- ERROR
+ ALTER FOREIGN TABLE ft1 OPTIONS (fetch_count 'a'); -- ERROR
+ ALTER FOREIGN TABLE ft1 OPTIONS (fetch_count '0'); -- ERROR
+ ALTER FOREIGN TABLE ft1 OPTIONS (fetch_count '-1'); -- ERROR
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (invalid 'value'); -- ERROR
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (colname 'C 1');
+ ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (colname 'C 1');
+ \dew+
+ \des+
+ \deu+
+ \det+
+
+ -- ===================================================================
+ -- simple queries
+ -- ===================================================================
+ -- single table, with/without alias
+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
+ SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ -- with WHERE clause
+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
+ SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
+ -- aggregate
+ SELECT COUNT(*) FROM ft1 t1;
+ -- join two tables
+ SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ -- subquery
+ SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
+ -- subquery+MAX
+ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
+ -- used in CTE
+ WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
+ -- fixed values
+ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+ -- user-defined operator/function
+ CREATE FUNCTION pgsql_fdw_abs(int) RETURNS int AS $$
+ BEGIN
+ RETURN abs($1);
+ END
+ $$ LANGUAGE plpgsql IMMUTABLE;
+ CREATE OPERATOR === (
+ LEFTARG = int,
+ RIGHTARG = int,
+ PROCEDURE = int4eq,
+ COMMUTATOR = ===,
+ NEGATOR = !==
+ );
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = pgsql_fdw_abs(t1.c2);
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
+
+ -- ===================================================================
+ -- parameterized queries
+ -- ===================================================================
+ -- simple join
+ PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
+ EXPLAIN (COSTS false) EXECUTE st1(1, 2);
+ EXECUTE st1(1, 1);
+ EXECUTE st1(101, 101);
+ -- subquery using stable function (can't be pushed down)
+ PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) = 6) ORDER BY c1;
+ EXPLAIN (COSTS false) EXECUTE st2(10, 20);
+ EXECUTE st2(10, 20);
+ EXECUTE st1(101, 101);
+ -- subquery using immutable function (can be pushed down)
+ PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c5) = 6) ORDER BY c1;
+ EXPLAIN (COSTS false) EXECUTE st3(10, 20);
+ EXECUTE st3(10, 20);
+ EXECUTE st3(20, 30);
+ -- custom plan should be chosen
+ PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ EXPLAIN (COSTS false) EXECUTE st4(1);
+ -- cleanup
+ DEALLOCATE st1;
+ DEALLOCATE st2;
+ DEALLOCATE st3;
+ DEALLOCATE st4;
+
+ -- ===================================================================
+ -- connection management
+ -- ===================================================================
+ SELECT srvname, usename FROM pgsql_fdw_connections;
+ SELECT pgsql_fdw_disconnect(srvid, usesysid) FROM pgsql_fdw_get_connections();
+ SELECT srvname, usename FROM pgsql_fdw_connections;
+
+ -- ===================================================================
+ -- subtransaction
+ -- ===================================================================
+ BEGIN;
+ DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
+ FETCH c;
+ SAVEPOINT s;
+ ERROR OUT; -- ERROR
+ ROLLBACK TO s;
+ SELECT srvname FROM pgsql_fdw_connections;
+ FETCH c;
+ COMMIT;
+ SELECT srvname FROM pgsql_fdw_connections;
+ ERROR OUT; -- ERROR
+ SELECT srvname FROM pgsql_fdw_connections;
+
+ -- ===================================================================
+ -- cleanup
+ -- ===================================================================
+ DROP OPERATOR === (int, int) CASCADE;
+ DROP OPERATOR !== (int, int) CASCADE;
+ DROP FUNCTION pgsql_fdw_abs(int);
+ DROP SCHEMA "S 1" CASCADE;
+ DROP EXTENSION pgsql_fdw CASCADE;
+ \c
+ DROP ROLE pgsql_fdw_user;
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index d4da4ee..32056d1 100644
*** a/doc/src/sgml/contrib.sgml
--- b/doc/src/sgml/contrib.sgml
*************** CREATE EXTENSION <replaceable>module_nam
*** 117,122 ****
--- 117,123 ----
&pgcrypto;
&pgfreespacemap;
&pgrowlocks;
+ &pgsql-fdw;
&pgstandby;
&pgstatstatements;
&pgstattuple;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index b5d3c6d..de686ee 100644
*** a/doc/src/sgml/filelist.sgml
--- b/doc/src/sgml/filelist.sgml
***************
*** 125,130 ****
--- 125,131 ----
<!ENTITY pgcrypto SYSTEM "pgcrypto.sgml">
<!ENTITY pgfreespacemap SYSTEM "pgfreespacemap.sgml">
<!ENTITY pgrowlocks SYSTEM "pgrowlocks.sgml">
+ <!ENTITY pgsql-fdw SYSTEM "pgsql-fdw.sgml">
<!ENTITY pgstandby SYSTEM "pgstandby.sgml">
<!ENTITY pgstatstatements SYSTEM "pgstatstatements.sgml">
<!ENTITY pgstattuple SYSTEM "pgstattuple.sgml">
diff --git a/doc/src/sgml/pgsql-fdw.sgml b/doc/src/sgml/pgsql-fdw.sgml
index ...30fa7f5 .
*** a/doc/src/sgml/pgsql-fdw.sgml
--- b/doc/src/sgml/pgsql-fdw.sgml
***************
*** 0 ****
--- 1,261 ----
+ <!-- doc/src/sgml/pgsql-fdw.sgml -->
+
+ <sect1 id="pgsql-fdw" xreflabel="pgsql_fdw">
+ <title>pgsql_fdw</title>
+
+ <indexterm zone="pgsql-fdw">
+ <primary>pgsql_fdw</primary>
+ </indexterm>
+
+ <para>
+ The <filename>pgsql_fdw</filename> module provides a foreign-data wrapper for
+ external <productname>PostgreSQL</productname> servers.
+ With this module, users can access data stored in external
+ <productname>PostgreSQL</productname> via plain SQL statements.
+ </para>
+
+ <para>
+ Note that default wrapper <literal>pgsql_fdw</literal> is created
+ automatically during <command>CREATE EXTENSION</command> command for
+ <application>pgsql_fdw</application>.
+ </para>
+
+ <sect2>
+ <title>FDW Options of pgsql_fdw</title>
+
+ <sect3>
+ <title>Connection Options</title>
+ <para>
+ A foreign server and user mapping created using this wrapper can have
+ <application>libpq</> connection options, expect below:
+
+ <itemizedlist>
+ <listitem><para>client_encoding</para></listitem>
+ <listitem><para>fallback_application_name</para></listitem>
+ <listitem><para>replication</para></listitem>
+ </itemizedlist>
+
+ For details of <application>libpq</> connection options, see
+ <xref linkend="libpq-connect">.
+ </para>
+
+ <para>
+ <literal>user</literal> and <literal>password</literal> can be
+ specified on user mappings, and others can be specified on foreign servers.
+ </para>
+ </sect3>
+
+ <sect3>
+ <title>Object Name Options</title>
+ <para>
+ Foreign tables which were created using this wrapper, or its columns can
+ have object name options. These options can be used to specify the names
+ used in SQL statement sent to remote <productname>PostgreSQL</productname>
+ server. These options are useful when a remote object has different name
+ from corresponding local one.
+ </para>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>nspname</literal></term>
+ <listitem>
+ <para>
+ This option, which can be specified on a foreign table, is used as a
+ namespace (schema) reference in the SQL statement. If this options is
+ omitted, <literal>pg_class.nspname</literal> of the foreign table is
+ used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>relname</literal></term>
+ <listitem>
+ <para>
+ This option, which can be specified on a foreign table, is used as a
+ relation (table) reference in the SQL statement. If this options is
+ omitted, <literal>pg_class.relname</literal> of the foreign table is
+ used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>colname</literal></term>
+ <listitem>
+ <para>
+ This option, which can be specified on a column of a foreign table, is
+ used as a column (attribute) reference in the SQL statement. If this
+ option is omitted, <literal>pg_attribute.attname</literal> of the column
+ of the foreign table is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
+ </sect3>
+
+ <sect3>
+ <title>Cursor Options</title>
+ <para>
+ The <application>pgsql_fdw</application> always uses cursor to retrieve the
+ result from external server. Users can control the behavior of cursor by
+ setting cursor options to foreign table or foreign server. If an option
+ is set to both objects, finer-grained setting is used. In other words,
+ foreign table's setting overrides foreign server's setting.
+ </para>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>fetch_count</literal></term>
+ <listitem>
+ <para>
+ This option specifies the number of rows to be fetched at a time.
+ This option accepts only integer value larger than zero. The default
+ setting is 10000.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
+ </sect3>
+
+ </sect2>
+
+ <sect2>
+ <title>Connection Management</title>
+
+ <para>
+ The <application>pgsql_fdw</application> establishes a connection to a
+ foreign server in the beginning of the first query which uses a foreign
+ table associated to the foreign server, and reuses the connection following
+ queries and even in following foreign scans in same query.
+
+ You can see the list of active connections via
+ <structname>pgsql_fdw_connections</structname> view. It shows pair of oid
+ and name of server and local role for each active connections established by
+ <application>pgsql_fdw</application>. For security reason, only superuser
+ can see other role's connections.
+ </para>
+
+ <para>
+ Established connections are kept alive until local role changes or the
+ current transaction aborts or user requests so.
+ </para>
+
+ <para>
+ If role has been changed, active connections established as old local role
+ is kept alive but never be reused until local role has restored to original
+ role. This kind of situation happens with <command>SET ROLE</command> and
+ <command>SET SESSION AUTHORIZATION</command>.
+ </para>
+
+ <para>
+ If current transaction aborts by error or user request, all active
+ connections are disconnected automatically. This behavior avoids possible
+ connection leaks on error.
+ </para>
+
+ <para>
+ You can discard persistent connection at arbitrary timing with
+ <function>pgsql_fdw_disconnect()</function>. It takes server oid and
+ user oid as arguments. This function can handle only connections
+ established in current session; connections established by other backends
+ are not reachable.
+ </para>
+
+ <para>
+ You can discard all active and visible connections in current session with
+ using <structname>pgsql_fdw_connections</structname> and
+ <function>pgsql_fdw_disconnect()</function> together:
+ <synopsis>
+ postgres=# SELECT pgsql_fdw_disconnect(srvid, usesysid) FROM pgsql_fdw_connections;
+ pgsql_fdw_disconnect
+ ----------------------
+ OK
+ OK
+ (2 rows)
+ </synopsis>
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Transaction Management</title>
+ <para>
+ The <application>pgsql_fdw</application> begins remote transaction at the
+ beginning of a local query, and terminates it with <command>ABORT</command>
+ at the end of the local query. This means that all foreign scans on a
+ foreign server in a local query are executed in one transaction.
+ If isolation level of local transaction is <literal>SERIALIZABLE</literal>,
+ <literal>SERIALIZABLE</literal> is used for remote transaction. Otherwise,
+ if isolation level of local transaction is one of
+ <literal>READ UNCOMMITTED</literal>, <literal>READ COMMITTED</literal> or
+ <literal>REPEATABLE READ</literal>, then <literal>REPEATABLE READ</literal>
+ is used for remote transaction.
+ <literal>READ UNCOMMITTED</literal> and <literal>READ COMMITTED</literal>
+ are never used for remote transaction, because even
+ <literal>READ COMMITTED</literal> transaction might produce inconsistent
+ results, if remote data have been updated between two remote queries.
+ </para>
+ <para>
+ Note that even if the isolation level of local transaction was
+ <literal>SERIALIZABLE</literal> or <literal>REPEATABLE READ</literal>,
+ series of one query might produce different result, because foreign scans
+ in different local queries are executed in different remote transactions.
+ For instance, when client started a local transaction
+ explicitly with isolation level <literal>SERIALIZABLE</literal>, and
+ executed same local query which contains a foreign table which references
+ foreign data which is updated frequently, latter result would be different
+ from former result.
+ </para>
+ <para>
+ This restriction might be relaxed in future release.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Estimation of Costs and Rows</title>
+ <para>
+ The <application>pgsql_fdw</application> estimates the costs of a foreign
+ scan by adding up some basic costs: connection costs, remote query costs
+ and data transfer costs.
+ To get remote query costs, <application>pgsql_fdw</application> executes
+ <command>EXPLAIN</command> command on remote server for each foreign scan.
+ </para>
+ <para>
+ On the other hand, estimated rows which was returned by
+ <command>EXPLAIN</command> is used for local estimation as-is.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>EXPLAIN Output</title>
+ <para>
+ For a foreign table using <literal>pgsql_fdw</>, <command>EXPLAIN</> shows
+ a remote SQL statement which is sent to remote
+ <productname>PostgreSQL</productname> server for a ForeignScan plan node.
+ For example:
+ </para>
+ <synopsis>
+ postgres=# EXPLAIN SELECT aid FROM pgbench_accounts WHERE abalance < 0;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on pgbench_accounts (cost=100.00..8105.13 rows=302613 width=8)
+ Filter: (abalance < 0)
+ Remote SQL: DECLARE pgsql_fdw_cursor_3 SCROLL CURSOR FOR SELECT aid, NULL, abalance, NULL FROM public.pgbench_accounts
+ (3 rows)
+ </synopsis>
+ </sect2>
+
+ <sect2>
+ <title>Author</title>
+ <para>
+ Shigeru Hanada <email>shigeru.hanada@gmail.com</email>
+ </para>
+ </sect2>
+
+ </sect1>