diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index c8232f2..d925ced 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -37,22 +37,25 @@
#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
+#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/plannodes.h"
#include "optimizer/clauses.h"
+#include "optimizer/prep.h"
#include "optimizer/var.h"
#include "parser/parsetree.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/syscache.h"
/*
* Global context for foreign_expr_walker's search of an expression tree.
@@ -85,20 +88,22 @@ typedef struct foreign_loc_cxt
/*
* Context for deparseExpr
*/
typedef struct deparse_expr_cxt
{
PlannerInfo *root; /* global planner state */
RelOptInfo *foreignrel; /* the foreign relation we are planning for */
StringInfo buf; /* output buffer to append to */
List **params_list; /* exprs that will become remote Params */
+ List *outertlist; /* outer child's target list */
+ List *innertlist; /* inner child's target list */
} deparse_expr_cxt;
/*
* Functions to determine whether an expression can be evaluated safely on
* remote server.
*/
static bool foreign_expr_walker(Node *node,
foreign_glob_cxt *glob_cxt,
foreign_loc_cxt *outer_cxt);
static char *deparse_type_name(Oid type_oid, int32 typemod);
@@ -112,40 +117,41 @@ static void deparseTargetList(StringInfo buf,
Relation rel,
Bitmapset *attrs_used,
List **retrieved_attrs);
static void deparseReturningList(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
bool trig_after_row,
List *returningList,
List **retrieved_attrs);
static void deparseColumnRef(StringInfo buf, int varno, int varattno,
PlannerInfo *root);
-static void deparseRelation(StringInfo buf, Relation rel);
+static void deparseRelation(StringInfo buf, Relation rel, bool qualified);
static void deparseExpr(Expr *expr, deparse_expr_cxt *context);
static void deparseVar(Var *node, deparse_expr_cxt *context);
static void deparseConst(Const *node, deparse_expr_cxt *context);
static void deparseParam(Param *node, deparse_expr_cxt *context);
static void deparseArrayRef(ArrayRef *node, deparse_expr_cxt *context);
static void deparseFuncExpr(FuncExpr *node, deparse_expr_cxt *context);
static void deparseOpExpr(OpExpr *node, deparse_expr_cxt *context);
static void deparseOperatorName(StringInfo buf, Form_pg_operator opform);
static void deparseDistinctExpr(DistinctExpr *node, deparse_expr_cxt *context);
static void deparseScalarArrayOpExpr(ScalarArrayOpExpr *node,
deparse_expr_cxt *context);
static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
static void deparseBoolExpr(BoolExpr *node, deparse_expr_cxt *context);
static void deparseNullTest(NullTest *node, deparse_expr_cxt *context);
static void deparseArrayExpr(ArrayExpr *node, deparse_expr_cxt *context);
static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
+static const char *get_jointype_name(JoinType jointype);
/*
* Examine each qual clause in input_conds, and classify them into two groups,
* which are returned as two lists:
* - remote_conds contains expressions that can be evaluated remotely
* - local_conds contains expressions that can't be evaluated remotely
*/
void
classifyConditions(PlannerInfo *root,
@@ -254,21 +260,21 @@ foreign_expr_walker(Node *node,
{
Var *var = (Var *) node;
/*
* If the Var is from the foreign table, we consider its
* collation (if any) safe to use. If it is from another
* table, we treat its collation the same way as we would a
* Param's collation, ie it's not safe for it to have a
* non-default collation.
*/
- if (var->varno == glob_cxt->foreignrel->relid &&
+ if (bms_is_member(var->varno, glob_cxt->foreignrel->relids) &&
var->varlevelsup == 0)
{
/* Var belongs to foreign table */
/*
* System columns other than ctid should not be sent to
* the remote, since we don't make any effort to ensure
* that local and remote values match (tableoid, in
* particular, almost certainly doesn't match).
*/
@@ -696,51 +702,238 @@ deparse_type_name(Oid type_oid, int32 typemod)
}
/*
* Construct a simple SELECT statement that retrieves desired columns
* of the specified foreign table, and append it to "buf". The output
* contains just "SELECT ... FROM tablename".
*
* We also create an integer List of the columns being retrieved, which is
* returned to *retrieved_attrs.
+ *
+ * The relations is a string buffer for "Relations" portion of EXPLAIN output,
+ * or NULL if caller doesn't need it. Note that it should have been
+ * initialized by caller.
+ *
+ * If use_reltargetlist is true, targetlist from the given relation is used. If
+ * false, the attrs_used is used. For deparsing queries for a pushed down join,
+ * use_reltargetlist is set to true and for single relations it is set to false.
+ * For single relations using attrs_used to build the targetlist of the remote
+ * SQL, avoids duplicate columns e.g. whole row references. For join there is
+ * no such reference from which to derive from. Hence use targetlist from the
+ * RelOptInfo in case of joins.
*/
void
-deparseSelectSql(StringInfo buf,
+deparseSelectSqlForRel(StringInfo buf,
PlannerInfo *root,
- RelOptInfo *baserel,
+ RelOptInfo *foreign_rel,
Bitmapset *attrs_used,
- List **retrieved_attrs)
+ List *remote_conds,
+ List **params_list,
+ List **fdw_scan_tlist,
+ List **retrieved_attrs,
+ StringInfo relations,
+ List *pathkeys,
+ bool use_reltargetlist)
{
- RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root);
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreign_rel->fdw_private;
+ /* TODO: a join relation may not have RangeTblEntry */
Relation rel;
/*
- * Core code already has some lock on each rel being planned, so we can
- * use NoLock here.
+ * If given relation was a join relation, recursively construct statement
+ * by putting each outer and inner relations in FROM clause as a subquery
+ * with aliasing.
*/
- rel = heap_open(rte->relid, NoLock);
+ if (foreign_rel->reloptkind == RELOPT_JOINREL)
+ {
+ RelOptInfo *rel_o = fpinfo->outerrel;
+ RelOptInfo *rel_i = fpinfo->innerrel;
+ PgFdwRelationInfo *fpinfo_o = (PgFdwRelationInfo *) rel_o->fdw_private;
+ PgFdwRelationInfo *fpinfo_i = (PgFdwRelationInfo *) rel_i->fdw_private;
+ StringInfoData sql_o;
+ StringInfoData sql_i;
+ List *ret_attrs_tmp; /* not used */
+ StringInfoData relations_o;
+ StringInfoData relations_i;
+ const char *jointype_str;
+
+ /*
+ * Deparse query for outer and inner relation, and combine them into
+ * a query.
+ *
+ * Here we don't pass fdw_scan_tlist because targets of underlying
+ * relations are already put in joinrel->reltargetlist, and
+ * deparseJoinRel() takes all care about it.
+ */
+ initStringInfo(&sql_o);
+ initStringInfo(&relations_o);
+ deparseSelectSqlForRel(&sql_o, root, rel_o, fpinfo_o->attrs_used,
+ fpinfo_o->remote_conds, params_list,
+ NULL, &ret_attrs_tmp, &relations_o, NULL, true);
+ initStringInfo(&sql_i);
+ initStringInfo(&relations_i);
+ deparseSelectSqlForRel(&sql_i, root, rel_i, fpinfo_i->attrs_used,
+ fpinfo_i->remote_conds, params_list,
+ NULL, &ret_attrs_tmp, &relations_i, NULL, true);
+
+ /* For EXPLAIN output */
+ jointype_str = get_jointype_name(fpinfo->jointype);
+ if (relations)
+ appendStringInfo(relations, "(%s) %s JOIN (%s)",
+ relations_o.data, jointype_str, relations_i.data);
+
+ deparseJoinSql(buf, root, foreign_rel,
+ rel_o,
+ rel_i,
+ sql_o.data,
+ sql_i.data,
+ fpinfo->jointype,
+ fpinfo->joinclauses,
+ fdw_scan_tlist,
+ retrieved_attrs);
+
+ remote_conds = fpinfo->otherclauses;
+ }
+ else
+ {
+ RangeTblEntry *rte = planner_rt_fetch(foreign_rel->relid, root);
+
+ /*
+ * Core code already has some lock on each rel being planned, so we can
+ * use NoLock here.
+ */
+ rel = heap_open(rte->relid, NoLock);
+
+ /*
+ * Construct SELECT list
+ */
+ appendStringInfoString(buf, "SELECT ");
+ if (use_reltargetlist)
+ {
+ deparse_expr_cxt context;
+
+ context.root = root;
+ context.foreignrel = foreign_rel;
+ context.buf = buf;
+ context.params_list = NIL;
+
+ deparseTargetListFromRel(foreign_rel, &context, retrieved_attrs, NULL);
+ }
+ else
+ deparseTargetList(buf, root, foreign_rel->relid, rel, attrs_used,
+ retrieved_attrs);
+
+ /*
+ * Construct FROM clause
+ */
+ appendStringInfoString(buf, " FROM ");
+ deparseRelation(buf, rel, true);
+
+ /*
+ * Return local relation name for EXPLAIN output.
+ * We can't know VERBOSE option is specified or not, so always add shcema
+ * name.
+ */
+ if (relations)
+ {
+ const char *namespace;
+ const char *relname;
+ const char *refname;
+
+ namespace = get_namespace_name(get_rel_namespace(rte->relid));
+ relname = get_rel_name(rte->relid);
+ refname = rte->eref->aliasname;
+ appendStringInfo(relations, "%s.%s",
+ quote_identifier(namespace),
+ quote_identifier(relname));
+ if (*refname && strcmp(refname, relname) != 0)
+ appendStringInfo(relations, " %s",
+ quote_identifier(rte->eref->aliasname));
+ }
+ heap_close(rel, NoLock);
+ }
/*
- * Construct SELECT list
+ * Construct WHERE clause
*/
- appendStringInfoString(buf, "SELECT ");
- deparseTargetList(buf, root, baserel->relid, rel, attrs_used,
- retrieved_attrs);
+ if (remote_conds)
+ appendConditions(buf, root, foreign_rel, remote_conds,
+ " WHERE ", params_list);
+ if (pathkeys)
+ appendOrderByClause(buf, root, foreign_rel, pathkeys);
/*
- * Construct FROM clause
+ * Add FOR UPDATE/SHARE if appropriate. We apply locking during the
+ * initial row fetch, rather than later on as is done for local tables.
+ * The extra roundtrips involved in trying to duplicate the local
+ * semantics exactly don't seem worthwhile (see also comments for
+ * RowMarkType).
+ *
+ * XXX
+ * Since the query is being built in recursive manner in bottom up manner,
+ * the FOR UPDATE/SHARE clause referring the base relations can not be added
+ * at the top level. They need to be added to the subqueries corresponding
+ * to the base relations. This has an undesirable effect of locking more
+ * rows than specified by user, as it locks even those rows from base
+ * relations which are not part of the final join result. To avoid this
+ * undesirable effect, we need to build the join query without the
+ * subqueries, which for now, seems hard.
+ *
+ * Note: because we actually run the query as a cursor, this assumes
+ * that DECLARE CURSOR ... FOR UPDATE is supported, which it isn't
+ * before 8.3.
*/
- appendStringInfoString(buf, " FROM ");
- deparseRelation(buf, rel);
- heap_close(rel, NoLock);
+ if (foreign_rel->reloptkind == RELOPT_JOINREL)
+ return;
+
+ if (foreign_rel->relid == root->parse->resultRelation &&
+ (root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE))
+ {
+ /* Relation is UPDATE/DELETE target, so use FOR UPDATE */
+ appendStringInfoString(buf, " FOR UPDATE");
+ }
+ else
+ {
+ PlanRowMark *rc = get_plan_rowmark(root->rowMarks, foreign_rel->relid);
+
+ if (rc)
+ {
+ /*
+ * Relation is specified as a FOR UPDATE/SHARE target, so handle
+ * that. (But we could also see LCS_NONE, meaning this isn't a
+ * target relation after all.)
+ *
+ * For now, just ignore any [NO] KEY specification, since (a)
+ * it's not clear what that means for a remote table that we
+ * don't have complete information about, and (b) it wouldn't
+ * work anyway on older remote servers. Likewise, we don't
+ * worry about NOWAIT.
+ */
+ switch (rc->strength)
+ {
+ case LCS_NONE:
+ /* No locking needed */
+ break;
+ case LCS_FORKEYSHARE:
+ case LCS_FORSHARE:
+ appendStringInfoString(buf, " FOR SHARE");
+ break;
+ case LCS_FORNOKEYUPDATE:
+ case LCS_FORUPDATE:
+ appendStringInfoString(buf, " FOR UPDATE");
+ break;
+ }
+ }
+ }
}
/*
* Emit a target list that retrieves the columns specified in attrs_used.
* This is used for both SELECT and RETURNING targetlists.
*
* The tlist text is appended to buf, and we also create an integer List
* of the columns being retrieved, which is returned to *retrieved_attrs.
*/
static void
@@ -801,96 +994,304 @@ deparseTargetList(StringInfo buf,
*retrieved_attrs = lappend_int(*retrieved_attrs,
SelfItemPointerAttributeNumber);
}
/* Don't generate bad syntax if no undropped columns */
if (first)
appendStringInfoString(buf, "NULL");
}
/*
- * Deparse WHERE clauses in given list of RestrictInfos and append them to buf.
+ * Deparse conditions, such as WHERE clause and ON clause of JOIN, in the given
+ * list, consist of RestrictInfo or Expr, and append string representation of
+ * them to buf.
*
* baserel is the foreign table we're planning for.
*
- * If no WHERE clause already exists in the buffer, is_first should be true.
+ * prefix is placed before the conditions, if any.
*
* If params is not NULL, it receives a list of Params and other-relation Vars
* used in the clauses; these values must be transmitted to the remote server
* as parameter values.
*
* If params is NULL, we're generating the query for EXPLAIN purposes,
* so Params and other-relation Vars should be replaced by dummy values.
*/
void
-appendWhereClause(StringInfo buf,
- PlannerInfo *root,
- RelOptInfo *baserel,
- List *exprs,
- bool is_first,
- List **params)
+appendConditions(StringInfo buf,
+ PlannerInfo *root,
+ RelOptInfo *foreign_rel,
+ List *exprs,
+ const char *prefix,
+ List **params)
{
deparse_expr_cxt context;
int nestlevel;
ListCell *lc;
+ bool is_first = prefix == NULL ? false : true;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreign_rel->fdw_private;
if (params)
*params = NIL; /* initialize result list to empty */
/* Set up context struct for recursion */
context.root = root;
- context.foreignrel = baserel;
+ context.foreignrel = foreign_rel;
context.buf = buf;
context.params_list = params;
+ if (foreign_rel->reloptkind == RELOPT_JOINREL)
+ {
+ context.outertlist = fpinfo->outerrel->reltargetlist;
+ context.innertlist = fpinfo->innerrel->reltargetlist;
+ }
+ else
+ {
+ context.outertlist = NIL;
+ context.innertlist = NIL;
+ }
+
/* Make sure any constants in the exprs are printed portably */
nestlevel = set_transmission_modes();
foreach(lc, exprs)
{
+ Node *node = (Node *) lfirst(lc);
RestrictInfo *ri = (RestrictInfo *) lfirst(lc);
+ Expr *expr = (Expr *) lfirst(lc);
+
+ if (IsA(node, RestrictInfo))
+ {
+ expr = ri->clause;
+ }
+ else
+ {
+ expr = ri->clause;
+ expr = (Expr *) node;
+ }
/* Connect expressions with "AND" and parenthesize each condition. */
if (is_first)
- appendStringInfoString(buf, " WHERE ");
+ appendStringInfoString(buf, prefix);
else
appendStringInfoString(buf, " AND ");
appendStringInfoChar(buf, '(');
- deparseExpr(ri->clause, &context);
+ deparseExpr(expr, &context);
appendStringInfoChar(buf, ')');
is_first = false;
}
reset_transmission_modes(nestlevel);
}
/*
+ * Returns position index (start with 1) of given var in given target list, or
+ * 0 when not found.
+ */
+static int
+find_var_pos(Var *node, List *tlist)
+{
+ int pos = 1;
+ ListCell *lc;
+
+ foreach(lc, tlist)
+ {
+ Var *var = (Var *) lfirst(lc);
+
+ if (equal(var, node))
+ {
+ return pos;
+ }
+ pos++;
+ }
+
+ return 0;
+}
+
+/*
+ * Deparse given Var into buf.
+ */
+static void
+deparseJoinVar(Var *node, deparse_expr_cxt *context)
+{
+ char side;
+ int pos;
+
+ pos = find_var_pos(node, context->outertlist);
+ if (pos > 0)
+ side = 'l';
+ else
+ {
+ side = 'r';
+ pos = find_var_pos(node, context->innertlist);
+ }
+
+ /*
+ * We treat whole-row reference same as ordinary attribute references,
+ * because such transformation should be done in lower level.
+ */
+ appendStringInfo(context->buf, "%c.a%d", side, pos);
+}
+
+/*
+ * Deparse column alias list for a subquery in FROM clause.
+ */
+static void
+deparseColumnAliases(StringInfo buf, List *tlist)
+{
+ int pos;
+ ListCell *lc;
+
+ pos = 1;
+ foreach(lc, tlist)
+ {
+ /* Deparse column alias for the subquery */
+ if (pos > 1)
+ appendStringInfoString(buf, ", ");
+ appendStringInfo(buf, "a%d", pos);
+ pos++;
+ }
+}
+
+static const char *
+get_jointype_name(JoinType jointype)
+{
+ if (jointype == JOIN_INNER)
+ return "INNER";
+ else if (jointype == JOIN_LEFT)
+ return "LEFT";
+ else if (jointype == JOIN_RIGHT)
+ return "RIGHT";
+ else if (jointype == JOIN_FULL)
+ return "FULL";
+
+ /* not reached */
+ elog(ERROR, "unsupported join type %d", jointype);
+}
+
+void
+deparseTargetListFromRel(RelOptInfo *foreign_rel, deparse_expr_cxt *context,
+ List **retrieved_attrs, List **fdw_scan_tlist)
+{
+ ListCell *lc;
+ StringInfo buf = context->buf;
+ int i = 0;
+
+ *retrieved_attrs = NIL;
+ foreach(lc, foreign_rel->reltargetlist)
+ {
+ Var *var = (Var *) lfirst(lc);
+ TargetEntry *tle;
+
+ if (i > 0)
+ appendStringInfoString(buf, ", ");
+ deparseVar(var, context);
+
+ tle = makeTargetEntry((Expr *) var, i + 1, NULL, false);
+ if (fdw_scan_tlist)
+ *fdw_scan_tlist = lappend(*fdw_scan_tlist, tle);
+
+ *retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
+
+ i++;
+ }
+
+ if (i == 0)
+ appendStringInfoString(buf, "NULL");
+
+}
+
+/*
+ * Construct a SELECT statement which contains join clause.
+ *
+ * We also create an TargetEntry List of the columns being retrieved, which is
+ * returned to *fdw_scan_tlist.
+ *
+ * path_o, tl_o, sql_o are respectively path, targetlist, and remote query
+ * statement of the outer child relation. postfix _i means those for the inner
+ * child relation. jointype and joinclauses are information of join method.
+ * fdw_scan_tlist is output parameter to pass target list of the pseudo scan to
+ * caller.
+ */
+void
+deparseJoinSql(StringInfo buf,
+ PlannerInfo *root,
+ RelOptInfo *joinrel,
+ RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ const char *sql_o,
+ const char *sql_i,
+ JoinType jointype,
+ List *joinclauses,
+ List **fdw_scan_tlist,
+ List **retrieved_attrs)
+{
+ StringInfoData selbuf; /* buffer for SELECT clause */
+ StringInfoData abuf_o; /* buffer for column alias list of outer */
+ StringInfoData abuf_i; /* buffer for column alias list of inner */
+ int i;
+ ListCell *lc;
+ const char *jointype_str;
+ deparse_expr_cxt context;
+
+ context.root = root;
+ context.foreignrel = joinrel;
+ context.buf = &selbuf;
+ context.params_list = NULL;
+ context.outertlist = outerrel->reltargetlist;
+ context.innertlist = innerrel->reltargetlist;
+
+ jointype_str = get_jointype_name(jointype);
+ *retrieved_attrs = NIL;
+
+ /* print SELECT clause of the join scan */
+ initStringInfo(&selbuf);
+ deparseTargetListFromRel(joinrel, &context, retrieved_attrs, fdw_scan_tlist);
+
+ /* Deparse column alias portion of subquery in FROM clause. */
+ initStringInfo(&abuf_o);
+ deparseColumnAliases(&abuf_o, outerrel->reltargetlist);
+ initStringInfo(&abuf_i);
+ deparseColumnAliases(&abuf_i, innerrel->reltargetlist);
+
+ /* Construct SELECT statement */
+ appendStringInfo(buf, "SELECT %s FROM", selbuf.data);
+ appendStringInfo(buf, " (%s) l (%s) %s JOIN (%s) r (%s)",
+ sql_o, abuf_o.data, jointype_str, sql_i, abuf_i.data);
+ /* Append ON clause */
+ if (joinclauses)
+ appendConditions(buf, root, joinrel, joinclauses, " ON ", NULL);
+ else
+ appendStringInfoString(buf, " ON (TRUE)");
+}
+
+/*
* deparse remote INSERT statement
*
* The statement text is appended to buf, and we also create an integer List
* of the columns being retrieved by RETURNING (if any), which is returned
* to *retrieved_attrs.
*/
void
deparseInsertSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetAttrs, bool doNothing,
List *returningList, List **retrieved_attrs)
{
AttrNumber pindex;
bool first;
ListCell *lc;
appendStringInfoString(buf, "INSERT INTO ");
- deparseRelation(buf, rel);
+ deparseRelation(buf, rel, true);
if (targetAttrs)
{
appendStringInfoChar(buf, '(');
first = true;
foreach(lc, targetAttrs)
{
int attnum = lfirst_int(lc);
@@ -939,21 +1340,21 @@ void
deparseUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetAttrs, List *returningList,
List **retrieved_attrs)
{
AttrNumber pindex;
bool first;
ListCell *lc;
appendStringInfoString(buf, "UPDATE ");
- deparseRelation(buf, rel);
+ deparseRelation(buf, rel, true);
appendStringInfoString(buf, " SET ");
pindex = 2; /* ctid is always the first param */
first = true;
foreach(lc, targetAttrs)
{
int attnum = lfirst_int(lc);
if (!first)
appendStringInfoString(buf, ", ");
@@ -977,21 +1378,21 @@ deparseUpdateSql(StringInfo buf, PlannerInfo *root,
* of the columns being retrieved by RETURNING (if any), which is returned
* to *retrieved_attrs.
*/
void
deparseDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *returningList,
List **retrieved_attrs)
{
appendStringInfoString(buf, "DELETE FROM ");
- deparseRelation(buf, rel);
+ deparseRelation(buf, rel, true);
appendStringInfoString(buf, " WHERE ctid = $1");
deparseReturningList(buf, root, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_delete_after_row,
returningList, retrieved_attrs);
}
/*
* Add a RETURNING clause, if needed, to an INSERT/UPDATE/DELETE.
*/
@@ -1039,21 +1440,21 @@ deparseReturningList(StringInfo buf, PlannerInfo *root,
*
* Note: pg_relation_size() exists in 8.1 and later.
*/
void
deparseAnalyzeSizeSql(StringInfo buf, Relation rel)
{
StringInfoData relname;
/* We'll need the remote relation name as a literal. */
initStringInfo(&relname);
- deparseRelation(&relname, rel);
+ deparseRelation(&relname, rel, true);
appendStringInfoString(buf, "SELECT pg_catalog.pg_relation_size(");
deparseStringLiteral(buf, relname.data);
appendStringInfo(buf, "::pg_catalog.regclass) / %d", BLCKSZ);
}
/*
* Construct SELECT statement to acquire sample rows of given relation.
*
* SELECT command is appended to buf, and list of columns retrieved
@@ -1104,74 +1505,124 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
}
/* Don't generate bad syntax for zero-column relation. */
if (first)
appendStringInfoString(buf, "NULL");
/*
* Construct FROM clause
*/
appendStringInfoString(buf, " FROM ");
- deparseRelation(buf, rel);
+ deparseRelation(buf, rel, true);
}
/*
* Construct name to use for given column, and emit it into buf.
* If it has a column_name FDW option, use that instead of attribute name.
*/
static void
deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root)
{
- RangeTblEntry *rte;
- char *colname = NULL;
- List *options;
- ListCell *lc;
+ /* Get RangeTblEntry from array in PlannerInfo. */
+ RangeTblEntry *rte = planner_rt_fetch(varno, root);
/* varno must not be any of OUTER_VAR, INNER_VAR and INDEX_VAR. */
Assert(!IS_SPECIAL_VARNO(varno));
- /* Get RangeTblEntry from array in PlannerInfo. */
- rte = planner_rt_fetch(varno, root);
-
- /*
- * If it's a column of a foreign table, and it has the column_name FDW
- * option, use that value.
- */
- options = GetForeignColumnOptions(rte->relid, varattno);
- foreach(lc, options)
+ /* Deparse system columns */
+ switch(varattno)
{
- DefElem *def = (DefElem *) lfirst(lc);
-
- if (strcmp(def->defname, "column_name") == 0)
+ case 0:
{
- colname = defGetString(def);
- break;
+ /*
+ * The lock on the relation will be held by upper callers, so it's
+ * fine to open it with no lock here.
+ */
+ Relation rel = heap_open(rte->relid, NoLock);
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int i;
+ bool first;
+
+ /*
+ * The local name of the foreign table can not be recognized by the
+ * foreign server and the table it references on foreign server
+ * might have different column ordering or different columns than
+ * those declared locally. Hence we have to deparse whole-row
+ * reference as ROW(all column names)
+ */
+ appendStringInfoString(buf, "ROW(");
+ first = true;
+ for (i = 1; i <= tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = tupdesc->attrs[i - 1];
+
+ /* Ignore dropped attributes. */
+ if (attr->attisdropped)
+ continue;
+
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ deparseColumnRef(buf, varno, i, root);
+ }
+ appendStringInfoString(buf, ")");
+ heap_close(rel, NoLock);
}
- }
+ break;
- /*
- * If it's a column of a regular table or it doesn't have column_name FDW
- * option, use attribute name.
- */
- if (colname == NULL)
- colname = get_relid_attribute_name(rte->relid, varattno);
+ case SelfItemPointerAttributeNumber:
+ appendStringInfoString(buf, "ctid");
+ break;
+
+ default:
+ {
+ List *options;
+ ListCell *lc;
+ char *colname = NULL;
+
+ /*
+ * If it's a column of a foreign table, and it has the column_name
+ * FDW option, use that value.
+ */
+ options = GetForeignColumnOptions(rte->relid, varattno);
+ foreach(lc, options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "column_name") == 0)
+ {
+ colname = defGetString(def);
+ break;
+ }
+ }
- appendStringInfoString(buf, quote_identifier(colname));
+ /*
+ * If it's a column of a regular table or it doesn't have column_name
+ * FDW option, use attribute name.
+ */
+ if (colname == NULL)
+ colname = get_relid_attribute_name(rte->relid, varattno);
+
+ appendStringInfoString(buf, quote_identifier(colname));
+ }
+ break;
+ }
}
/*
* Append remote name of specified foreign table to buf.
* Use value of table_name FDW option (if any) instead of relation's name.
* Similarly, schema_name FDW option overrides schema name.
*/
static void
-deparseRelation(StringInfo buf, Relation rel)
+deparseRelation(StringInfo buf, Relation rel, bool qualified)
{
ForeignTable *table;
const char *nspname = NULL;
const char *relname = NULL;
ListCell *lc;
/* obtain additional catalog information. */
table = GetForeignTable(RelationGetRelid(rel));
/*
@@ -1189,22 +1640,23 @@ deparseRelation(StringInfo buf, Relation rel)
/*
* Note: we could skip printing the schema name if it's pg_catalog, but
* that doesn't seem worth the trouble.
*/
if (nspname == NULL)
nspname = get_namespace_name(RelationGetNamespace(rel));
if (relname == NULL)
relname = RelationGetRelationName(rel);
- appendStringInfo(buf, "%s.%s",
- quote_identifier(nspname), quote_identifier(relname));
+ if (qualified)
+ appendStringInfo(buf, "%s.", quote_identifier(nspname));
+ appendStringInfo(buf, "%s", quote_identifier(relname));
}
/*
* Append a SQL string literal representing "val" to buf.
*/
void
deparseStringLiteral(StringInfo buf, const char *val)
{
const char *valptr;
@@ -1285,63 +1737,72 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
default:
elog(ERROR, "unsupported expression type for deparse: %d",
(int) nodeTag(node));
break;
}
}
/*
* Deparse given Var node into context->buf.
*
+ * If context has valid innerrel, this is invoked for a join conditions.
+ *
* If the Var belongs to the foreign relation, just print its remote name.
* Otherwise, it's effectively a Param (and will in fact be a Param at
* run time). Handle it the same way we handle plain Params --- see
* deparseParam for comments.
*/
static void
deparseVar(Var *node, deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
- if (node->varno == context->foreignrel->relid &&
- node->varlevelsup == 0)
+ if (context->foreignrel->reloptkind == RELOPT_JOINREL)
{
- /* Var belongs to foreign table */
- deparseColumnRef(buf, node->varno, node->varattno, context->root);
+ deparseJoinVar(node, context);
}
else
{
- /* Treat like a Param */
- if (context->params_list)
+ if (node->varno == context->foreignrel->relid &&
+ node->varlevelsup == 0)
{
- int pindex = 0;
- ListCell *lc;
-
- /* find its index in params_list */
- foreach(lc, *context->params_list)
+ /* Var belongs to foreign table */
+ deparseColumnRef(buf, node->varno, node->varattno, context->root);
+ }
+ else
+ {
+ /* Treat like a Param */
+ if (context->params_list)
{
- pindex++;
- if (equal(node, (Node *) lfirst(lc)))
- break;
+ int pindex = 0;
+ ListCell *lc;
+
+ /* find its index in params_list */
+ foreach(lc, *context->params_list)
+ {
+ pindex++;
+ if (equal(node, (Node *) lfirst(lc)))
+ break;
+ }
+ if (lc == NULL)
+ {
+ /* not in list, so add it */
+ pindex++;
+ *context->params_list = lappend(*context->params_list, node);
+ }
+
+ printRemoteParam(pindex, node->vartype, node->vartypmod, context);
}
- if (lc == NULL)
+ else
{
- /* not in list, so add it */
- pindex++;
- *context->params_list = lappend(*context->params_list, node);
+ printRemotePlaceholder(node->vartype, node->vartypmod, context);
}
-
- printRemoteParam(pindex, node->vartype, node->vartypmod, context);
- }
- else
- {
- printRemotePlaceholder(node->vartype, node->vartypmod, context);
}
}
}
/*
* Deparse given constant value into context->buf.
*
* This function has to be kept in sync with ruleutils.c's get_const_expr.
*/
static void
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 866a09b..153c1ad 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2,25 +2,30 @@
-- create FDW objects
-- ===================================================================
CREATE EXTENSION postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
+ EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (dbname '$$||current_database()||$$',
+ port '$$||current_setting('port')||$$'
+ )$$;
END;
$d$;
CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
+CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
-- ===================================================================
-- create objects used through FDW loopback server
-- ===================================================================
CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
c3 text,
c4 timestamptz,
@@ -28,36 +33,62 @@ CREATE TABLE "S 1"."T 1" (
c6 varchar(10),
c7 char(10),
c8 user_enum,
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)
);
+CREATE TABLE "S 1"."T 3" (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ CONSTRAINT t3_pkey PRIMARY KEY (c1)
+);
+CREATE TABLE "S 1"."T 4" (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c4 text,
+ CONSTRAINT t4_pkey PRIMARY KEY (c1)
+);
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,
'foo'::user_enum
FROM generate_series(1, 1000) id;
INSERT INTO "S 1"."T 2"
SELECT id,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 100) id;
+INSERT INTO "S 1"."T 3"
+ SELECT id,
+ id + 1,
+ 'AAA' || to_char(id, 'FM000')
+ FROM generate_series(1, 100) id;
+DELETE FROM "S 1"."T 3" WHERE c1 % 2 != 0; -- delete for outer join tests
+INSERT INTO "S 1"."T 4"
+ SELECT id,
+ id + 1,
+ 'AAA' || to_char(id, 'FM000')
+ FROM generate_series(1, 100) id;
+DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests
ANALYZE "S 1"."T 1";
ANALYZE "S 1"."T 2";
+ANALYZE "S 1"."T 3";
+ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
c4 timestamptz,
c5 timestamp,
@@ -71,20 +102,40 @@ CREATE FOREIGN TABLE ft2 (
c2 int NOT NULL,
cx int,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10) default 'ft2',
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
+CREATE FOREIGN TABLE ft4 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text
+) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
+CREATE FOREIGN TABLE ft5 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text
+) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
+CREATE FOREIGN TABLE ft6 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text
+) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
+CREATE USER view_owner;
+GRANT ALL ON ft5 TO view_owner;
+CREATE VIEW v_ft5 AS SELECT * FROM ft5;
+ALTER VIEW v_ft5 OWNER TO view_owner;
+CREATE USER MAPPING FOR view_owner SERVER loopback;
-- ===================================================================
-- tests for validator
-- ===================================================================
-- requiressl, krbsrvname and gsslib are omitted because they depend on
-- configure options
ALTER SERVER testserver1 OPTIONS (
use_remote_estimate 'false',
updatable 'true',
fdw_startup_cost '123.456',
fdw_tuple_cost '0.123',
@@ -120,26 +171,29 @@ ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo, bar');
WARNING: extension "foo" is not installed
WARNING: extension "bar" is not installed
ALTER SERVER testserver1 OPTIONS (DROP extensions);
ALTER USER MAPPING FOR public SERVER testserver1
OPTIONS (DROP user, DROP password);
ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
\det+
- List of foreign tables
- Schema | Table | Server | FDW Options | Description
---------+-------+----------+---------------------------------------+-------------
- public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1') |
- public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1') |
-(2 rows)
+ List of foreign tables
+ Schema | Table | Server | FDW Options | Description
+--------+-------+-----------+---------------------------------------+-------------
+ public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1') |
+ public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1') |
+ public | ft4 | loopback | (schema_name 'S 1', table_name 'T 3') |
+ public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') |
+ public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') |
+(5 rows)
-- Now we should be able to run ANALYZE.
-- To exercise multiple code paths, we use local stats on ft1
-- and remote-estimate mode on ft2.
ANALYZE ft1;
ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
-- ===================================================================
-- simple queries
-- ===================================================================
-- single table without alias
@@ -274,36 +328,20 @@ SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
102 | 2 | 00102 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
(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 | c8
----+----+-------+------------------------------+--------------------------+----+------------+-----
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo
6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo
@@ -436,30 +474,30 @@ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote
QUERY PLAN
-------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
Filter: (t1.c8 = 'foo'::user_enum)
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)
--- parameterized remote path
+-- parameterized remote path for foreign table
EXPLAIN (VERBOSE, COSTS false)
- SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Nested Loop
- Output: a.c1, a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8, b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
- -> Foreign Scan on public.ft2 a
- Output: a.c1, a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 47))
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8, b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
+ -> Index Scan using t1_pkey on "S 1"."T 1" a
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8
+ Index Cond: (a."C 1" = 47)
-> Foreign Scan on public.ft2 b
Output: b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
(8 rows)
SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+-------+------------------------------+--------------------------+----+------------+-----
47 | 7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo | 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
(1 row)
@@ -750,36 +788,926 @@ EXPLAIN (VERBOSE, COSTS false)
Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2))
(5 rows)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
count
-------
9
(1 row)
-- ===================================================================
+-- JOIN queries
+-- ===================================================================
+-- join two tables
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t1.c3
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, r.a1 FROM (SELECT "C 1", c3 FROM "S 1"."T 1") l (a1, a2) INNER JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))
+(9 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+-- test the GUC enable_foreignjoin by default it's true, so test false
+SET enable_foreignjoin TO false;
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3
+ Sort Key: t1.c3, t1.c1
+ -> Hash Join
+ Output: t1.c1, t2.c1, t1.c3
+ Hash Cond: (t2.c1 = t1.c1)
+ -> Foreign Scan on public.ft2 t2
+ Output: t2.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+ -> Hash
+ Output: t1.c1, t1.c3
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1, t1.c3
+ Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1"
+(16 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+RESET enable_foreignjoin;
+-- join three tables
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c2, t3.c3, t1.c3
+ -> Sort
+ Output: t1.c1, t2.c2, t3.c3, t1.c3
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c2, t3.c3, t1.c3
+ Relations: ((public.ft1 t1) INNER JOIN (public.ft2 t2)) INNER JOIN (public.ft4 t3)
+ Remote SQL: SELECT l.a1, l.a2, l.a3, r.a1 FROM (SELECT l.a1, l.a2, r.a1, r.a2 FROM (SELECT "C 1", c3 FROM "S 1"."T 1") l (a1, a2) INNER JOIN (SELECT c2, "C 1" FROM "S 1"."T 1") r (a1, a2) ON ((l.a1 = r.a2))) l (a1, a2, a3, a4) INNER JOIN (SELECT c3, c1 FROM "S 1"."T 3") r (a1, a2) ON ((l.a1 = r.a2))
+(9 rows)
+
+SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
+ c1 | c2 | c3
+----+----+--------
+ 22 | 2 | AAA022
+ 24 | 4 | AAA024
+ 26 | 6 | AAA026
+ 28 | 8 | AAA028
+ 30 | 0 | AAA030
+ 32 | 2 | AAA032
+ 34 | 4 | AAA034
+ 36 | 6 | AAA036
+ 38 | 8 | AAA038
+ 40 | 0 | AAA040
+(10 rows)
+
+-- left outer join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Sort
+ Output: t1.c1, t2.c1
+ Sort Key: t1.c1, t2.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft4 t1) LEFT JOIN (public.ft5 t2)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT c1 FROM "S 1"."T 3") l (a1) LEFT JOIN (SELECT c1 FROM "S 1"."T 4") r (a1) ON ((l.a1 = r.a1))
+(9 rows)
+
+SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ c1 | c1
+----+----
+ 22 |
+ 24 | 24
+ 26 |
+ 28 |
+ 30 | 30
+ 32 |
+ 34 |
+ 36 | 36
+ 38 |
+ 40 |
+(10 rows)
+
+-- left outer join + placement of clauses.
+-- clauses within the nullable side are not pulled up, but top level clause on
+-- non-nullable side is pushed into non-nullable side
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: t1.c1, t1.c2, ft5.c1, ft5.c2
+ Relations: (public.ft4 t1) LEFT JOIN (public.ft5)
+ Remote SQL: SELECT l.a1, l.a2, r.a1, r.a2 FROM (SELECT c1, c2 FROM "S 1"."T 3" WHERE ((c1 < 10))) l (a1, a2) LEFT JOIN (SELECT c1, c2 FROM "S 1"."T 4" WHERE ((c1 < 10))) r (a1, a2) ON ((l.a1 = r.a1))
+(4 rows)
+
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10;
+ c1 | c2 | c1 | c2
+----+----+----+----
+ 2 | 3 | |
+ 4 | 5 | |
+ 6 | 7 | 6 | 7
+ 8 | 9 | |
+(4 rows)
+
+-- clauses within the nullable side are not pulled up, but the top level clause
+-- on nullable side is not pushed down into nullable side
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
+ WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: t1.c1, t1.c2, ft5.c1, ft5.c2
+ Relations: (public.ft4 t1) LEFT JOIN (public.ft5)
+ Remote SQL: SELECT l.a1, l.a2, r.a1, r.a2 FROM (SELECT c1, c2 FROM "S 1"."T 3" WHERE ((c1 < 10))) l (a1, a2) LEFT JOIN (SELECT c1, c2 FROM "S 1"."T 4" WHERE ((c1 < 10))) r (a1, a2) ON ((l.a1 = r.a1)) WHERE (((r.a1 < 10) OR (r.a1 IS NULL)))
+(4 rows)
+
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
+ WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
+ c1 | c2 | c1 | c2
+----+----+----+----
+ 2 | 3 | |
+ 4 | 5 | |
+ 6 | 7 | 6 | 7
+ 8 | 9 | |
+(4 rows)
+
+-- right outer join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Sort
+ Output: t1.c1, t2.c1
+ Sort Key: t2.c1, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft4 t2) LEFT JOIN (public.ft5 t1)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT c1 FROM "S 1"."T 3") l (a1) LEFT JOIN (SELECT c1 FROM "S 1"."T 4") r (a1) ON ((r.a1 = l.a1))
+(9 rows)
+
+SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
+ c1 | c1
+----+----
+ | 22
+ 24 | 24
+ | 26
+ | 28
+ 30 | 30
+ | 32
+ | 34
+ 36 | 36
+ | 38
+ | 40
+(10 rows)
+
+-- full outer join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Sort
+ Output: t1.c1, t2.c1
+ Sort Key: t1.c1, t2.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft4 t1) FULL JOIN (public.ft5 t2)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT c1 FROM "S 1"."T 3") l (a1) FULL JOIN (SELECT c1 FROM "S 1"."T 4") r (a1) ON ((l.a1 = r.a1))
+(9 rows)
+
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
+ c1 | c1
+-----+----
+ 92 |
+ 94 |
+ 96 | 96
+ 98 |
+ 100 |
+ | 3
+ | 9
+ | 15
+ | 21
+ | 27
+(10 rows)
+
+-- full outer join + WHERE clause, only matched rows
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Sort
+ Output: t1.c1, t2.c1
+ Sort Key: t1.c1, t2.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft4 t1) FULL JOIN (public.ft5 t2)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT c1 FROM "S 1"."T 3") l (a1) FULL JOIN (SELECT c1 FROM "S 1"."T 4") r (a1) ON ((l.a1 = r.a1)) WHERE (((l.a1 = r.a1) OR (l.a1 IS NULL)))
+(9 rows)
+
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ c1 | c1
+----+----
+ 66 | 66
+ 72 | 72
+ 78 | 78
+ 84 | 84
+ 90 | 90
+ 96 | 96
+ | 3
+ | 9
+ | 15
+ | 21
+(10 rows)
+
+-- join two tables with FOR UPDATE clause
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> LockRows
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, l.a3, r.a1, r.a2 FROM (SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR UPDATE) l (a1, a2, a3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1") r (a1, a2) ON ((l.a1 = r.a1))
+(11 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> LockRows
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, l.a3, r.a1, r.a2 FROM (SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR UPDATE) l (a1, a2, a3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR UPDATE) r (a1, a2) ON ((l.a1 = r.a1))
+(11 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+-- join two tables with FOR SHARE clause
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> LockRows
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, l.a3, r.a1, r.a2 FROM (SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR SHARE) l (a1, a2, a3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1") r (a1, a2) ON ((l.a1 = r.a1))
+(11 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> LockRows
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, l.a3, r.a1, r.a2 FROM (SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR SHARE) l (a1, a2, a3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR SHARE) r (a1, a2) ON ((l.a1 = r.a1))
+(11 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+-- TODO: why is this test? join at WHERE clause
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON true WHERE (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t1.c3
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, r.a1 FROM (SELECT "C 1", c3 FROM "S 1"."T 1") l (a1, a2) INNER JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))
+(9 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON true WHERE (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+-- join in CTE
+EXPLAIN (COSTS false, VERBOSE)
+WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t.c1_1, t.c2_1, t.c1_3
+ CTE t
+ -> Foreign Scan
+ Output: t1.c1, t1.c3, t2.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, r.a1 FROM (SELECT "C 1", c3 FROM "S 1"."T 1") l (a1, a2) INNER JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))
+ -> Sort
+ Output: t.c1_1, t.c2_1, t.c1_3
+ Sort Key: t.c1_3, t.c1_1
+ -> CTE Scan on t
+ Output: t.c1_1, t.c2_1, t.c1_3
+(12 rows)
+
+WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+ c1_1 | c2_1
+------+------
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+-- ctid with whole-row reference
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
+ -> Sort
+ Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
+ Sort Key: t1.c3, t1.c1
+ -> Foreign Scan
+ Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, l.a2, l.a3, l.a4, r.a1 FROM (SELECT ctid, ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1", c3 FROM "S 1"."T 1") l (a1, a2, a3, a4) INNER JOIN (SELECT ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1" FROM "S 1"."T 1") r (a1, a2) ON ((l.a3 = r.a2))
+(9 rows)
+
+SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ ctid | t1 | t2 | c1
+--------+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+-----
+ (1,4) | (101,1,00101,"Fri Jan 02 00:00:00 1970 PST","Fri Jan 02 00:00:00 1970",1,"1 ",foo) | (101,1,00101,"Fri Jan 02 00:00:00 1970 PST","Fri Jan 02 00:00:00 1970",1,"1 ",foo) | 101
+ (1,5) | (102,2,00102,"Sat Jan 03 00:00:00 1970 PST","Sat Jan 03 00:00:00 1970",2,"2 ",foo) | (102,2,00102,"Sat Jan 03 00:00:00 1970 PST","Sat Jan 03 00:00:00 1970",2,"2 ",foo) | 102
+ (1,6) | (103,3,00103,"Sun Jan 04 00:00:00 1970 PST","Sun Jan 04 00:00:00 1970",3,"3 ",foo) | (103,3,00103,"Sun Jan 04 00:00:00 1970 PST","Sun Jan 04 00:00:00 1970",3,"3 ",foo) | 103
+ (1,7) | (104,4,00104,"Mon Jan 05 00:00:00 1970 PST","Mon Jan 05 00:00:00 1970",4,"4 ",foo) | (104,4,00104,"Mon Jan 05 00:00:00 1970 PST","Mon Jan 05 00:00:00 1970",4,"4 ",foo) | 104
+ (1,8) | (105,5,00105,"Tue Jan 06 00:00:00 1970 PST","Tue Jan 06 00:00:00 1970",5,"5 ",foo) | (105,5,00105,"Tue Jan 06 00:00:00 1970 PST","Tue Jan 06 00:00:00 1970",5,"5 ",foo) | 105
+ (1,9) | (106,6,00106,"Wed Jan 07 00:00:00 1970 PST","Wed Jan 07 00:00:00 1970",6,"6 ",foo) | (106,6,00106,"Wed Jan 07 00:00:00 1970 PST","Wed Jan 07 00:00:00 1970",6,"6 ",foo) | 106
+ (1,10) | (107,7,00107,"Thu Jan 08 00:00:00 1970 PST","Thu Jan 08 00:00:00 1970",7,"7 ",foo) | (107,7,00107,"Thu Jan 08 00:00:00 1970 PST","Thu Jan 08 00:00:00 1970",7,"7 ",foo) | 107
+ (1,11) | (108,8,00108,"Fri Jan 09 00:00:00 1970 PST","Fri Jan 09 00:00:00 1970",8,"8 ",foo) | (108,8,00108,"Fri Jan 09 00:00:00 1970 PST","Fri Jan 09 00:00:00 1970",8,"8 ",foo) | 108
+ (1,12) | (109,9,00109,"Sat Jan 10 00:00:00 1970 PST","Sat Jan 10 00:00:00 1970",9,"9 ",foo) | (109,9,00109,"Sat Jan 10 00:00:00 1970 PST","Sat Jan 10 00:00:00 1970",9,"9 ",foo) | 109
+ (1,13) | (110,0,00110,"Sun Jan 11 00:00:00 1970 PST","Sun Jan 11 00:00:00 1970",0,"0 ",foo) | (110,0,00110,"Sun Jan 11 00:00:00 1970 PST","Sun Jan 11 00:00:00 1970",0,"0 ",foo) | 110
+(10 rows)
+
+-- partially unsafe to push down, not pushed down
+-- TODO what's unsafe to pushdown here?
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN ft4 t3 ON t2.c1 = t3.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1
+ -> Sort
+ Output: t1.c1
+ Sort Key: t1.c1
+ -> Foreign Scan
+ Output: t1.c1
+ Relations: ((public.ft1 t1) INNER JOIN (public.ft2 t2)) INNER JOIN (public.ft4 t3)
+ Remote SQL: SELECT l.a1 FROM (SELECT l.a1, r.a1 FROM (SELECT "C 1" FROM "S 1"."T 1") l (a1) INNER JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))) l (a1, a2) INNER JOIN (SELECT c1 FROM "S 1"."T 3") r (a1) ON ((l.a1 = r.a1))
+(9 rows)
+
+SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN ft4 t3 ON t2.c1 = t3.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+ c1
+----
+ 22
+ 24
+ 26
+ 28
+ 30
+ 32
+ 34
+ 36
+ 38
+ 40
+(10 rows)
+
+-- SEMI JOIN, not pushed down
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1
+ -> Merge Semi Join
+ Output: t1.c1
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+ -> Materialize
+ Output: t2.c1
+ -> Foreign Scan on public.ft2 t2
+ Output: t2.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+(13 rows)
+
+SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+ c1
+-----
+ 101
+ 102
+ 103
+ 104
+ 105
+ 106
+ 107
+ 108
+ 109
+ 110
+(10 rows)
+
+-- ANTI JOIN, not pushed down
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Limit
+ Output: t1.c1
+ -> Merge Anti Join
+ Output: t1.c1
+ Merge Cond: (t1.c1 = t2.c2)
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+ -> Sort
+ Output: t2.c2
+ Sort Key: t2.c2
+ -> Foreign Scan on public.ft2 t2
+ Output: t2.c2
+ Remote SQL: SELECT c2 FROM "S 1"."T 1"
+(14 rows)
+
+SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+ c1
+-----
+ 110
+ 111
+ 112
+ 113
+ 114
+ 115
+ 116
+ 117
+ 118
+ 119
+(10 rows)
+
+-- CROSS JOIN, not pushed down
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Sort
+ Output: t1.c1, t2.c1
+ Sort Key: t1.c1, t2.c1
+ -> Nested Loop
+ Output: t1.c1, t2.c1
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+ -> Materialize
+ Output: t2.c1
+ -> Foreign Scan on public.ft2 t2
+ Output: t2.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+(15 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+----+-----
+ 1 | 101
+ 1 | 102
+ 1 | 103
+ 1 | 104
+ 1 | 105
+ 1 | 106
+ 1 | 107
+ 1 | 108
+ 1 | 109
+ 1 | 110
+(10 rows)
+
+-- different server
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Merge Join
+ Output: t1.c1, t2.c1
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Foreign Scan on public.ft5 t1
+ Output: t1.c1, t1.c2, t1.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+ -> Materialize
+ Output: t2.c1, t2.c2, t2.c3
+ -> Foreign Scan on public.ft6 t2
+ Output: t2.c1, t2.c2, t2.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+(13 rows)
+
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+----+----
+(0 rows)
+
+-- different effective user for permission check
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, ft5.c1
+ -> Merge Join
+ Output: t1.c1, ft5.c1
+ Merge Cond: (t1.c1 = ft5.c1)
+ -> Foreign Scan on public.ft5 t1
+ Output: t1.c1, t1.c2, t1.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+ -> Materialize
+ Output: ft5.c1, ft5.c2, ft5.c3
+ -> Foreign Scan on public.ft5
+ Output: ft5.c1, ft5.c2, ft5.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+(13 rows)
+
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+----+----
+(0 rows)
+
+-- unsafe join conditions (c8 has a UDT)
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Sort
+ Output: t1.c1, t2.c1
+ Sort Key: t1.c1, t2.c1
+ -> Merge Join
+ Output: t1.c1, t2.c1
+ Merge Cond: (t1.c8 = t2.c8)
+ -> Sort
+ Output: t1.c1, t1.c8
+ Sort Key: t1.c8
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1, t1.c8
+ Remote SQL: SELECT "C 1", c8 FROM "S 1"."T 1"
+ -> Sort
+ Output: t2.c1, t2.c8
+ Sort Key: t2.c8
+ -> Foreign Scan on public.ft2 t2
+ Output: t2.c1, t2.c8
+ Remote SQL: SELECT "C 1", c8 FROM "S 1"."T 1"
+(20 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+----+-----
+ 1 | 101
+ 1 | 102
+ 1 | 103
+ 1 | 104
+ 1 | 105
+ 1 | 106
+ 1 | 107
+ 1 | 108
+ 1 | 109
+ 1 | 110
+(10 rows)
+
+-- local filter (unsafe conditions on one side, c8 has a UDT)
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t1.c3
+ -> Sort
+ Output: t1.c1, t2.c1, t1.c3
+ Sort Key: t1.c3, t1.c1
+ -> Hash Join
+ Output: t1.c1, t2.c1, t1.c3
+ Hash Cond: (t2.c1 = t1.c1)
+ -> Foreign Scan on public.ft2 t2
+ Output: t2.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+ -> Hash
+ Output: t1.c1, t1.c3
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1, t1.c3
+ Filter: (t1.c8 = 'foo'::user_enum)
+ Remote SQL: SELECT "C 1", c3, c8 FROM "S 1"."T 1"
+(17 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ c1 | c1
+-----+-----
+ 101 | 101
+ 102 | 102
+ 103 | 103
+ 104 | 104
+ 105 | 105
+ 106 | 106
+ 107 | 107
+ 108 | 108
+ 109 | 109
+ 110 | 110
+(10 rows)
+
+-- Aggregate after UNION, for testing setrefs
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, (avg((t1.c1 + t2.c1)))
+ -> Sort
+ Output: t1.c1, (avg((t1.c1 + t2.c1)))
+ Sort Key: t1.c1
+ -> HashAggregate
+ Output: t1.c1, avg((t1.c1 + t2.c1))
+ Group Key: t1.c1
+ -> HashAggregate
+ Output: t1.c1, t2.c1
+ Group Key: t1.c1, t2.c1
+ -> Append
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT "C 1" FROM "S 1"."T 1") l (a1) INNER JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))
+ -> Foreign Scan
+ Output: t1_1.c1, t2_1.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT "C 1" FROM "S 1"."T 1") l (a1) INNER JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))
+(20 rows)
+
+SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
+ t1c1 | avg
+------+----------------------
+ 101 | 202.0000000000000000
+ 102 | 204.0000000000000000
+ 103 | 206.0000000000000000
+ 104 | 208.0000000000000000
+ 105 | 210.0000000000000000
+ 106 | 212.0000000000000000
+ 107 | 214.0000000000000000
+ 108 | 216.0000000000000000
+ 109 | 218.0000000000000000
+ 110 | 220.0000000000000000
+(10 rows)
+
+-- join two foreign tables and two local tables
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN "S 1"."T 1" t3 ON t1.c1 = t3."C 1" JOIN "S 1"."T 2" t4 ON t1.c1 = t4.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1
+ -> Sort
+ Output: t1.c1, t2.c1
+ Sort Key: t1.c1
+ -> Hash Join
+ Output: t1.c1, t2.c1
+ Hash Cond: (t1.c1 = t3."C 1")
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft1 t1) LEFT JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT "C 1" FROM "S 1"."T 1") l (a1) LEFT JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))
+ -> Hash
+ Output: t3."C 1", t4.c1
+ -> Merge Join
+ Output: t3."C 1", t4.c1
+ Merge Cond: (t3."C 1" = t4.c1)
+ -> Index Only Scan using t1_pkey on "S 1"."T 1" t3
+ Output: t3."C 1"
+ -> Sort
+ Output: t4.c1
+ Sort Key: t4.c1
+ -> Seq Scan on "S 1"."T 2" t4
+ Output: t4.c1
+(24 rows)
+
+SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN "S 1"."T 1" t3 ON t1.c1 = t3."C 1" JOIN "S 1"."T 2" t4 ON t1.c1 = t4.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+ c1 | c1
+----+----
+ 11 | 11
+ 12 | 12
+ 13 | 13
+ 14 | 14
+ 15 | 15
+ 16 | 16
+ 17 | 17
+ 18 | 18
+ 19 | 19
+ 20 | 20
+(10 rows)
+
+-- TODO: join with lateral reference
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1."C 1"
+ -> Nested Loop
+ Output: t1."C 1"
+ -> Index Scan using t1_pkey on "S 1"."T 1" t1
+ Output: t1."C 1", t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
+ -> HashAggregate
+ Output: t2.c1, t3.c1
+ Group Key: t2.c1, t3.c1
+ -> Foreign Scan
+ Output: t2.c1, t3.c1
+ Relations: (public.ft1 t2) INNER JOIN (public.ft2 t3)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT "C 1" FROM "S 1"."T 1" WHERE ((c2 = $1::integer))) l (a1) INNER JOIN (SELECT "C 1" FROM "S 1"."T 1") r (a1) ON ((l.a1 = r.a1))
+(13 rows)
+
+SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
+ C 1
+-----
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+(10 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 (VERBOSE, COSTS false) EXECUTE st1(1, 2);
- QUERY PLAN
---------------------------------------------------------------------
- Nested Loop
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
Output: t1.c3, t2.c3
- -> Foreign Scan on public.ft1 t1
- Output: t1.c3
- Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 1))
- -> Foreign Scan on public.ft2 t2
- Output: t2.c3
- Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 2))
-(8 rows)
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 1))) l (a1) INNER JOIN (SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 2))) r (a1) ON (TRUE)
+(4 rows)
EXECUTE st1(1, 1);
c3 | c3
-------+-------
00001 | 00001
(1 row)
EXECUTE st1(101, 101);
c3 | c3
-------+-------
@@ -1348,36 +2276,29 @@ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING
977 | 407 | 00977_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo
987 | 407 | 00987_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo
997 | 407 | 00997_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo
1007 | 507 | 0000700007_update7 | | | | ft2 |
1017 | 507 | 0001700017_update7 | | | | ft2 |
(102 rows)
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3, c7 = $4 WHERE ctid = $1
- -> Hash Join
+ -> Foreign Scan
Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 || '_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8, ft2.ctid, ft1.*
- Hash Cond: (ft2.c2 = ft1.c1)
- -> Foreign Scan on public.ft2
- Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" FOR UPDATE
- -> Hash
- Output: ft1.*, ft1.c1
- -> Foreign Scan on public.ft1
- Output: ft1.*, ft1.c1
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 9))
-(13 rows)
+ Relations: (public.ft2) INNER JOIN (public.ft1)
+ Remote SQL: SELECT l.a1, l.a2, l.a3, l.a4, l.a5, l.a6, l.a7, l.a8, r.a1 FROM (SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" FOR UPDATE) l (a1, a2, a3, a4, a5, a6, a7, a8) INNER JOIN (SELECT ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1" FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 9))) r (a1, a2) ON ((l.a2 = r.a2))
+(6 rows)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
QUERY PLAN
----------------------------------------------------------------------------------------
Delete on public.ft2
Output: c1, c4
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c4
@@ -1489,36 +2410,29 @@ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
975 | Tue Mar 17 00:00:00 1970 PST
985 | Fri Mar 27 00:00:00 1970 PST
995 | Mon Apr 06 00:00:00 1970 PST
1005 |
1015 |
1105 |
(103 rows)
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
- -> Hash Join
+ -> Foreign Scan
Output: ft2.ctid, ft1.*
- Hash Cond: (ft2.c2 = ft1.c1)
- -> Foreign Scan on public.ft2
- Output: ft2.ctid, ft2.c2
- Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" FOR UPDATE
- -> Hash
- Output: ft1.*, ft1.c1
- -> Foreign Scan on public.ft1
- Output: ft1.*, ft1.c1
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 2))
-(13 rows)
+ Relations: (public.ft2) INNER JOIN (public.ft1)
+ Remote SQL: SELECT l.a1, r.a1 FROM (SELECT ctid, c2 FROM "S 1"."T 1" FOR UPDATE) l (a1, a2) INNER JOIN (SELECT ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1" FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 2))) r (a1, a2) ON ((l.a2 = r.a2))
+(6 rows)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
c1 | c2 | c3 | c4
------+-----+--------------------+------------------------------
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST
3 | 303 | 00003_update3 | Sun Jan 04 00:00:00 1970 PST
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST
6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST
7 | 407 | 00007_update7 | Thu Jan 08 00:00:00 1970 PST
@@ -3779,10 +4693,13 @@ ERROR: type "public.Colors" does not exist
LINE 4: "Col" public."Colors" OPTIONS (column_name 'Col')
^
QUERY: CREATE FOREIGN TABLE t5 (
c1 integer OPTIONS (column_name 'c1'),
c2 text OPTIONS (column_name 'c2') COLLATE pg_catalog."C",
"Col" public."Colors" OPTIONS (column_name 'Col')
) SERVER loopback
OPTIONS (schema_name 'import_source', table_name 't5');
CONTEXT: importing foreign table "t5"
ROLLBACK;
+-- Cleanup
+DROP OWNED BY view_owner;
+DROP USER view_owner;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index cd4ed0c..c789174 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -21,21 +21,20 @@
#include "commands/vacuum.h"
#include "foreign/fdwapi.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/cost.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
-#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/var.h"
#include "parser/parsetree.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/sampling.h"
@@ -61,21 +60,27 @@ PG_MODULE_MAGIC;
*
* These items are indexed with the enum FdwScanPrivateIndex, so an item
* can be fetched with list_nth(). For example, to get the SELECT statement:
* sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
*/
enum FdwScanPrivateIndex
{
/* SQL statement to execute remotely (as a String node) */
FdwScanPrivateSelectSql,
/* Integer list of attribute numbers retrieved by the SELECT */
- FdwScanPrivateRetrievedAttrs
+ FdwScanPrivateRetrievedAttrs,
+ /* Integer value of server for the scan */
+ FdwScanPrivateServerOid,
+ /* Integer value of user mapping for the scan */
+ FdwScanPrivateUserMappingOid,
+ /* Names of relation scanned, added when the scan is join */
+ FdwScanPrivateRelations,
};
/*
* Similarly, this enum describes what's kept in the fdw_private list for
* a ModifyTable node referencing a postgres_fdw foreign table. We store:
*
* 1) INSERT/UPDATE/DELETE statement text to be sent to the remote server
* 2) Integer list of target attribute numbers for INSERT/UPDATE
* (NIL for a DELETE)
* 3) Boolean flag showing if the remote query has a RETURNING clause
@@ -91,21 +96,22 @@ enum FdwModifyPrivateIndex
FdwModifyPrivateHasReturning,
/* Integer list of attribute numbers retrieved by RETURNING */
FdwModifyPrivateRetrievedAttrs
};
/*
* Execution state of a foreign scan using postgres_fdw.
*/
typedef struct PgFdwScanState
{
- Relation rel; /* relcache entry for the foreign table */
+ const char *relname; /* name of relation being scanned */
+ TupleDesc tupdesc; /* tuple descriptor of the scan */
AttInMetadata *attinmeta; /* attribute datatype conversion metadata */
/* extracted fdw_private data */
char *query; /* text of SELECT command */
List *retrieved_attrs; /* list of retrieved attribute numbers */
/* for remote query execution */
PGconn *conn; /* connection for the scan */
unsigned int cursor_number; /* quasi-unique ID for my cursor */
bool cursor_exists; /* have we created the cursor? */
@@ -157,41 +163,46 @@ typedef struct PgFdwModifyState
/*
* Workspace for analyzing a foreign table.
*/
typedef struct PgFdwAnalyzeState
{
Relation rel; /* relcache entry for the foreign table */
AttInMetadata *attinmeta; /* attribute datatype conversion metadata */
List *retrieved_attrs; /* attr numbers retrieved by query */
+ char *query; /* text of SELECT command */
+
/* collected sample rows */
HeapTuple *rows; /* array of size targrows */
int targrows; /* target # of sample rows */
int numrows; /* # of sample rows collected */
/* for random sampling */
double samplerows; /* # of rows fetched */
double rowstoskip; /* # of rows to skip before next sample */
ReservoirStateData rstate; /* state for reservoir sampling */
/* working memory contexts */
MemoryContext anl_cxt; /* context for per-analyze lifespan data */
MemoryContext temp_cxt; /* context for per-tuple temporary data */
} PgFdwAnalyzeState;
/*
* Identify the attribute where data conversion fails.
*/
typedef struct ConversionLocation
{
- Relation rel; /* foreign table's relcache entry */
+ const char *relname; /* name of relation being processed, or NULL for
+ a foreign join */
+ const char *query; /* query being processed */
+ TupleDesc tupdesc; /* tuple descriptor for attribute names */
AttrNumber cur_attno; /* attribute number being processed, or 0 */
} ConversionLocation;
/* Callback argument for ec_member_matches_foreign */
typedef struct
{
Expr *current; /* current expr, or NULL if not yet found */
List *already_used; /* expressions already dealt with */
} ec_member_foreign_arg;
@@ -251,20 +262,26 @@ static void postgresExplainForeignScan(ForeignScanState *node,
static void postgresExplainForeignModify(ModifyTableState *mtstate,
ResultRelInfo *rinfo,
List *fdw_private,
int subplan_index,
ExplainState *es);
static bool postgresAnalyzeForeignTable(Relation relation,
AcquireSampleRowsFunc *func,
BlockNumber *totalpages);
static List *postgresImportForeignSchema(ImportForeignSchemaStmt *stmt,
Oid serverOid);
+static void postgresGetForeignJoinPaths(PlannerInfo *root,
+ RelOptInfo *joinrel,
+ RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ JoinType jointype,
+ JoinPathExtraData *extra);
/*
* Helper functions
*/
static void estimate_path_cost_size(PlannerInfo *root,
RelOptInfo *baserel,
List *join_conds,
List *pathkeys,
double *p_rows, int *p_width,
Cost *p_startup_cost, Cost *p_total_cost);
@@ -287,26 +304,54 @@ static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
static int postgresAcquireSampleRowsFunc(Relation relation, int elevel,
HeapTuple *rows, int targrows,
double *totalrows,
double *totaldeadrows);
static void analyze_row_processor(PGresult *res, int row,
PgFdwAnalyzeState *astate);
static HeapTuple make_tuple_from_result_row(PGresult *res,
int row,
- Relation rel,
+ const char *relname,
+ const char *query,
+ TupleDesc tupdesc,
AttInMetadata *attinmeta,
List *retrieved_attrs,
MemoryContext temp_context);
static void conversion_error_callback(void *arg);
+/*
+ * Describe Bitmapset as comma-separated integer list.
+ * For debug purpose.
+ * XXX Can this become a member of bitmapset.c?
+ */
+static char *
+bms_to_str(Bitmapset *bmp)
+{
+ StringInfoData buf;
+ bool first = true;
+ int x;
+
+ initStringInfo(&buf);
+
+ x = -1;
+ while ((x = bms_next_member(bmp, x)) >= 0)
+ {
+ if (!first)
+ appendStringInfoString(&buf, ", ");
+ appendStringInfo(&buf, "%d", x);
+
+ first = false;
+ }
+
+ return buf.data;
+}
/*
* Foreign-data wrapper handler function: return a struct with pointers
* to my callback routines.
*/
Datum
postgres_fdw_handler(PG_FUNCTION_ARGS)
{
FdwRoutine *routine = makeNode(FdwRoutine);
@@ -332,48 +377,61 @@ postgres_fdw_handler(PG_FUNCTION_ARGS)
/* Support functions for EXPLAIN */
routine->ExplainForeignScan = postgresExplainForeignScan;
routine->ExplainForeignModify = postgresExplainForeignModify;
/* Support functions for ANALYZE */
routine->AnalyzeForeignTable = postgresAnalyzeForeignTable;
/* Support functions for IMPORT FOREIGN SCHEMA */
routine->ImportForeignSchema = postgresImportForeignSchema;
+ /* Support functions for join push-down */
+ routine->GetForeignJoinPaths = postgresGetForeignJoinPaths;
+
PG_RETURN_POINTER(routine);
}
/*
* postgresGetForeignRelSize
* Estimate # of rows and width of the result of the scan
*
* We should consider the effect of all baserestrictinfo clauses here, but
* not any join clauses.
*/
static void
postgresGetForeignRelSize(PlannerInfo *root,
RelOptInfo *baserel,
Oid foreigntableid)
{
PgFdwRelationInfo *fpinfo;
ListCell *lc;
+ RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root);
+ /*
+ * Identify which user to do the remote access as. This should match what
+ * ExecCheckRTEPerms() does.
+ */
+ Oid userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
/*
* We use PgFdwRelationInfo to pass various information to subsequent
* functions.
*/
fpinfo = (PgFdwRelationInfo *) palloc0(sizeof(PgFdwRelationInfo));
baserel->fdw_private = (void *) fpinfo;
+ /* This scan can be pushed down to the remote. */
+ fpinfo->pushdown_safe = true;
+
/* Look up foreign-table catalog info. */
fpinfo->table = GetForeignTable(foreigntableid);
fpinfo->server = GetForeignServer(fpinfo->table->serverid);
+ fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid);
/*
* Extract user-settable option values. Note that per-table setting of
* use_remote_estimate overrides per-server setting.
*/
fpinfo->use_remote_estimate = false;
fpinfo->fdw_startup_cost = DEFAULT_FDW_STARTUP_COST;
fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
fpinfo->shippable_extensions = NIL;
@@ -396,36 +454,20 @@ postgresGetForeignRelSize(PlannerInfo *root,
DefElem *def = (DefElem *) lfirst(lc);
if (strcmp(def->defname, "use_remote_estimate") == 0)
{
fpinfo->use_remote_estimate = defGetBoolean(def);
break; /* only need the one value */
}
}
/*
- * If the table or the server is configured to use remote estimates,
- * identify which user to do remote access as during planning. This
- * should match what ExecCheckRTEPerms() does. If we fail due to lack of
- * permissions, the query would have failed at runtime anyway.
- */
- if (fpinfo->use_remote_estimate)
- {
- RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root);
- Oid userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
-
- fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid);
- }
- else
- fpinfo->user = NULL;
-
- /*
* Identify which baserestrictinfo clauses can be sent to the remote
* server and which can't.
*/
classifyConditions(root, baserel, baserel->baserestrictinfo,
&fpinfo->remote_conds, &fpinfo->local_conds);
/*
* Identify which attributes will need to be retrieved from the remote
* server. These include all attrs needed for joins or final output, plus
* all attrs used in the local_conds. (Note: if we end up using a
@@ -760,219 +802,170 @@ postgresGetForeignPaths(PlannerInfo *root,
add_path(baserel, (Path *) path);
}
}
/*
* postgresGetForeignPlan
* Create ForeignScan plan node which implements selected best path
*/
static ForeignScan *
postgresGetForeignPlan(PlannerInfo *root,
- RelOptInfo *baserel,
+ RelOptInfo *foreign_rel,
Oid foreigntableid,
ForeignPath *best_path,
List *tlist,
List *scan_clauses)
{
- PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) baserel->fdw_private;
- Index scan_relid = baserel->relid;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreign_rel->fdw_private;
+ /* TODO: this should change for join relation */
+ Index scan_relid = foreign_rel->relid;
List *fdw_private;
List *remote_conds = NIL;
List *remote_exprs = NIL;
List *local_exprs = NIL;
List *params_list = NIL;
List *retrieved_attrs;
StringInfoData sql;
ListCell *lc;
+ List *fdw_scan_tlist = NIL;
+ StringInfoData relations;
/*
* Separate the scan_clauses into those that can be executed remotely and
* those that can't. baserestrictinfo clauses that were previously
* determined to be safe or unsafe by classifyConditions are shown in
* fpinfo->remote_conds and fpinfo->local_conds. Anything else in the
* scan_clauses list will be a join clause, which we have to check for
* remote-safety.
*
* Note: the join clauses we see here should be the exact same ones
* previously examined by postgresGetForeignPaths. Possibly it'd be worth
* passing forward the classification work done then, rather than
* repeating it here.
*
* This code must match "extract_actual_clauses(scan_clauses, false)"
* except for the additional decision about remote versus local execution.
* Note however that we don't strip the RestrictInfo nodes from the
* remote_conds list, since appendWhereClause expects a list of
* RestrictInfos.
+ *
+ * TODO: We need to reassess this in the light of joins.
*/
foreach(lc, scan_clauses)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
Assert(IsA(rinfo, RestrictInfo));
/* Ignore any pseudoconstants, they're dealt with elsewhere */
if (rinfo->pseudoconstant)
continue;
if (list_member_ptr(fpinfo->remote_conds, rinfo))
{
remote_conds = lappend(remote_conds, rinfo);
remote_exprs = lappend(remote_exprs, rinfo->clause);
}
else if (list_member_ptr(fpinfo->local_conds, rinfo))
local_exprs = lappend(local_exprs, rinfo->clause);
- else if (is_foreign_expr(root, baserel, rinfo->clause))
+ else if (is_foreign_expr(root, foreign_rel, rinfo->clause))
{
remote_conds = lappend(remote_conds, rinfo);
remote_exprs = lappend(remote_exprs, rinfo->clause);
}
else
local_exprs = lappend(local_exprs, rinfo->clause);
}
/*
* Build the query string to be sent for execution, and identify
- * expressions to be sent as parameters.
+ * expressions to be sent as parameters. If the relation to scan is a join
+ * relation, receive constructed relations string from deparseSelectSql.
*/
initStringInfo(&sql);
- deparseSelectSql(&sql, root, baserel, fpinfo->attrs_used,
- &retrieved_attrs);
- if (remote_conds)
- appendWhereClause(&sql, root, baserel, remote_conds,
- true, ¶ms_list);
-
- /* Add ORDER BY clause if we found any useful pathkeys */
- if (best_path->path.pathkeys)
- appendOrderByClause(&sql, root, baserel, best_path->path.pathkeys);
-
- /*
- * Add FOR UPDATE/SHARE if appropriate. We apply locking during the
- * initial row fetch, rather than later on as is done for local tables.
- * The extra roundtrips involved in trying to duplicate the local
- * semantics exactly don't seem worthwhile (see also comments for
- * RowMarkType).
- *
- * Note: because we actually run the query as a cursor, this assumes that
- * DECLARE CURSOR ... FOR UPDATE is supported, which it isn't before 8.3.
- */
- if (baserel->relid == root->parse->resultRelation &&
- (root->parse->commandType == CMD_UPDATE ||
- root->parse->commandType == CMD_DELETE))
- {
- /* Relation is UPDATE/DELETE target, so use FOR UPDATE */
- appendStringInfoString(&sql, " FOR UPDATE");
- }
- else
- {
- PlanRowMark *rc = get_plan_rowmark(root->rowMarks, baserel->relid);
-
- if (rc)
- {
- /*
- * Relation is specified as a FOR UPDATE/SHARE target, so handle
- * that. (But we could also see LCS_NONE, meaning this isn't a
- * target relation after all.)
- *
- * For now, just ignore any [NO] KEY specification, since (a) it's
- * not clear what that means for a remote table that we don't have
- * complete information about, and (b) it wouldn't work anyway on
- * older remote servers. Likewise, we don't worry about NOWAIT.
- */
- switch (rc->strength)
- {
- case LCS_NONE:
- /* No locking needed */
- break;
- case LCS_FORKEYSHARE:
- case LCS_FORSHARE:
- appendStringInfoString(&sql, " FOR SHARE");
- break;
- case LCS_FORNOKEYUPDATE:
- case LCS_FORUPDATE:
- appendStringInfoString(&sql, " FOR UPDATE");
- break;
- }
- }
- }
+ if (foreign_rel->reloptkind == RELOPT_JOINREL)
+ initStringInfo(&relations);
+ deparseSelectSqlForRel(&sql, root, foreign_rel, fpinfo->attrs_used, remote_conds,
+ ¶ms_list, &fdw_scan_tlist, &retrieved_attrs,
+ foreign_rel->reloptkind == RELOPT_JOINREL ? &relations : NULL,
+ best_path->path.pathkeys,
+ false);
/*
* Build the fdw_private list that will be available to the executor.
* Items in the list must match enum FdwScanPrivateIndex, above.
+ * TODO
+ * We already have umid in the list, do we need separate serverid? Doesn't
+ * given umid map to only a single server.
*/
- fdw_private = list_make2(makeString(sql.data),
- retrieved_attrs);
+ fdw_private = list_make4(makeString(sql.data),
+ retrieved_attrs,
+ makeInteger(foreign_rel->serverid),
+ makeInteger(foreign_rel->umid));
+ if (foreign_rel->reloptkind == RELOPT_JOINREL)
+ fdw_private = lappend(fdw_private, makeString(relations.data));
/*
* Create the ForeignScan node from target list, local filtering
* expressions, remote parameter expressions, and FDW private information.
*
* Note that the remote parameter expressions are stored in the fdw_exprs
* field of the finished plan node; we can't keep them in private state
* because then they wouldn't be subject to later planner processing.
*/
return make_foreignscan(tlist,
local_exprs,
scan_relid,
params_list,
fdw_private,
- NIL, /* no custom tlist */
+ fdw_scan_tlist,
remote_exprs);
}
/*
* postgresBeginForeignScan
* Initiate an executor scan of a foreign PostgreSQL table.
*/
static void
postgresBeginForeignScan(ForeignScanState *node, int eflags)
{
ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan;
EState *estate = node->ss.ps.state;
PgFdwScanState *fsstate;
- RangeTblEntry *rte;
- Oid userid;
- ForeignTable *table;
+ Oid umid;
+ Oid serverid;
ForeignServer *server;
UserMapping *user;
int numParams;
int i;
ListCell *lc;
/*
* Do nothing in EXPLAIN (no ANALYZE) case. node->fdw_state stays NULL.
*/
if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
return;
/*
* We'll save private state in node->fdw_state.
*/
fsstate = (PgFdwScanState *) palloc0(sizeof(PgFdwScanState));
node->fdw_state = (void *) fsstate;
/*
- * Identify which user to do the remote access as. This should match what
- * ExecCheckRTEPerms() does.
- */
- rte = rt_fetch(fsplan->scan.scanrelid, estate->es_range_table);
- userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
-
- /* Get info about foreign table. */
- fsstate->rel = node->ss.ss_currentRelation;
- table = GetForeignTable(RelationGetRelid(fsstate->rel));
- server = GetForeignServer(table->serverid);
- user = GetUserMapping(userid, server->serverid);
-
- /*
* Get connection to the foreign server. Connection manager will
* establish new connection if necessary.
*/
+ serverid = intVal(list_nth(fsplan->fdw_private, FdwScanPrivateServerOid));
+ umid = intVal(list_nth(fsplan->fdw_private, FdwScanPrivateUserMappingOid));
+ server = GetForeignServer(serverid);
+ user = GetUserMappingById(umid);
fsstate->conn = GetConnection(server, user, false);
/* Assign a unique ID for my cursor */
fsstate->cursor_number = GetCursorNumber(fsstate->conn);
fsstate->cursor_exists = false;
/* Get private info created by planner functions. */
fsstate->query = strVal(list_nth(fsplan->fdw_private,
FdwScanPrivateSelectSql));
fsstate->retrieved_attrs = (List *) list_nth(fsplan->fdw_private,
@@ -983,22 +976,32 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
"postgres_fdw tuple data",
ALLOCSET_DEFAULT_MINSIZE,
ALLOCSET_DEFAULT_INITSIZE,
ALLOCSET_DEFAULT_MAXSIZE);
fsstate->temp_cxt = AllocSetContextCreate(estate->es_query_cxt,
"postgres_fdw temporary data",
ALLOCSET_SMALL_MINSIZE,
ALLOCSET_SMALL_INITSIZE,
ALLOCSET_SMALL_MAXSIZE);
- /* Get info we'll need for input data conversion. */
- fsstate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(fsstate->rel));
+ /* Get info we'll need for input data conversion and error report. */
+ if (fsplan->scan.scanrelid > 0)
+ {
+ fsstate->relname = RelationGetRelationName(node->ss.ss_currentRelation);
+ fsstate->tupdesc = RelationGetDescr(node->ss.ss_currentRelation);
+ }
+ else
+ {
+ fsstate->relname = NULL;
+ fsstate->tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
+ }
+ fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
/* Prepare for output conversion of parameters used in remote query. */
numParams = list_length(fsplan->fdw_exprs);
fsstate->numParams = numParams;
fsstate->param_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * numParams);
i = 0;
foreach(lc, fsplan->fdw_exprs)
{
Node *param_expr = (Node *) lfirst(lc);
@@ -1713,24 +1716,39 @@ postgresIsForeignRelUpdatable(Relation rel)
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
*/
static void
postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ char *relations;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+
+ /*
+ * Add names of relation handled by the foreign scan when the scan is a
+ * join
+ */
+ if (list_length(fdw_private) > FdwScanPrivateRelations)
+ {
+ relations = strVal(list_nth(fdw_private, FdwScanPrivateRelations));
+ ExplainPropertyText("Relations", relations, es);
+ }
+ /*
+ * Add remote query, when VERBOSE option is specified.
+ */
if (es->verbose)
{
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
ExplainPropertyText("Remote SQL", sql, es);
}
}
/*
* postgresExplainForeignModify
* Produce extra output for EXPLAIN of a ModifyTable on a foreign table
*/
static void
@@ -1749,93 +1767,93 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
}
}
/*
* estimate_path_cost_size
* Get cost and size estimates for a foreign scan
*
* We assume that all the baserestrictinfo clauses will be applied, plus
* any join clauses listed in join_conds.
+ *
+ * TODO: Check if we need any changes to this function because of join push down
+ * work. Now the relation passed in is a join relation and join_conds have
+ * different meaning.
*/
static void
estimate_path_cost_size(PlannerInfo *root,
- RelOptInfo *baserel,
+ RelOptInfo *foreign_rel,
List *join_conds,
List *pathkeys,
double *p_rows, int *p_width,
Cost *p_startup_cost, Cost *p_total_cost)
{
- PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) baserel->fdw_private;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreign_rel->fdw_private;
double rows;
double retrieved_rows;
int width;
Cost startup_cost;
Cost total_cost;
Cost run_cost;
Cost cpu_per_tuple;
/*
* If the table or the server is configured to use remote estimates,
* connect to the foreign server and execute EXPLAIN to estimate the
* number of rows selected by the restriction+join clauses. Otherwise,
* estimate rows using whatever statistics we have locally, in a way
* similar to ordinary tables.
*/
if (fpinfo->use_remote_estimate)
{
+ List *remote_conds;
List *remote_join_conds;
List *local_join_conds;
StringInfoData sql;
List *retrieved_attrs;
PGconn *conn;
Selectivity local_sel;
QualCost local_cost;
/*
* join_conds might contain both clauses that are safe to send across,
* and clauses that aren't.
*/
- classifyConditions(root, baserel, join_conds,
+ classifyConditions(root, foreign_rel, join_conds,
&remote_join_conds, &local_join_conds);
+ remote_conds = copyObject(fpinfo->remote_conds);
+ remote_conds = list_concat(remote_conds, remote_join_conds);
/*
* Construct EXPLAIN query including the desired SELECT, FROM, and
* WHERE clauses. Params and other-relation Vars are replaced by
* dummy values.
+ * Here we waste params_list and fdw_scan_tlist because they are
+ * unnecessary for EXPLAIN.
*/
initStringInfo(&sql);
appendStringInfoString(&sql, "EXPLAIN ");
- deparseSelectSql(&sql, root, baserel, fpinfo->attrs_used,
- &retrieved_attrs);
- if (fpinfo->remote_conds)
- appendWhereClause(&sql, root, baserel, fpinfo->remote_conds,
- true, NULL);
- if (remote_join_conds)
- appendWhereClause(&sql, root, baserel, remote_join_conds,
- (fpinfo->remote_conds == NIL), NULL);
-
- if (pathkeys)
- appendOrderByClause(&sql, root, baserel, pathkeys);
+ deparseSelectSqlForRel(&sql, root, foreign_rel, fpinfo->attrs_used, remote_conds,
+ NULL, NULL, &retrieved_attrs, NULL, pathkeys, false);
/* Get the remote estimate */
conn = GetConnection(fpinfo->server, fpinfo->user, false);
get_remote_estimate(sql.data, conn, &rows, &width,
&startup_cost, &total_cost);
ReleaseConnection(conn);
retrieved_rows = rows;
/* Factor in the selectivity of the locally-checked quals */
local_sel = clauselist_selectivity(root,
local_join_conds,
- baserel->relid,
+ foreign_rel->relid,
JOIN_INNER,
NULL);
local_sel *= fpinfo->local_conds_sel;
rows = clamp_row_est(rows * local_sel);
/* Add in the eval cost of the locally-checked quals */
startup_cost += fpinfo->local_conds_cost.startup;
total_cost += fpinfo->local_conds_cost.per_tuple * retrieved_rows;
cost_qual_eval(&local_cost, local_join_conds, root);
@@ -1843,43 +1861,44 @@ estimate_path_cost_size(PlannerInfo *root,
total_cost += local_cost.per_tuple * retrieved_rows;
}
else
{
/*
* We don't support join conditions in this mode (hence, no
* parameterized paths can be made).
*/
Assert(join_conds == NIL);
+ /* TODO: this comment changes for the join relations */
/* Use rows/width estimates made by set_baserel_size_estimates. */
- rows = baserel->rows;
- width = baserel->width;
+ rows = foreign_rel->rows;
+ width = foreign_rel->width;
/*
* Back into an estimate of the number of retrieved rows. Just in
- * case this is nuts, clamp to at most baserel->tuples.
+ * case this is nuts, clamp to at most foreign_rel->tuples.
*/
retrieved_rows = clamp_row_est(rows / fpinfo->local_conds_sel);
- retrieved_rows = Min(retrieved_rows, baserel->tuples);
+ retrieved_rows = Min(retrieved_rows, foreign_rel->tuples);
/*
* Cost as though this were a seqscan, which is pessimistic. We
* effectively imagine the local_conds are being evaluated remotely,
* too.
*/
startup_cost = 0;
run_cost = 0;
- run_cost += seq_page_cost * baserel->pages;
+ run_cost += seq_page_cost * foreign_rel->pages;
- startup_cost += baserel->baserestrictcost.startup;
- cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
- run_cost += cpu_per_tuple * baserel->tuples;
+ startup_cost += foreign_rel->baserestrictcost.startup;
+ cpu_per_tuple = cpu_tuple_cost + foreign_rel->baserestrictcost.per_tuple;
+ run_cost += cpu_per_tuple * foreign_rel->tuples;
/*
* Without remote estimates, we have no real way to estimate the cost
* of generating sorted output. It could be free if the query plan
* the remote side would have chosen generates properly-sorted output
* anyway, but in most cases it will cost something. Estimate a value
* high enough that we won't pick the sorted path when the ordering
* isn't locally useful, but low enough that we'll err on the side of
* pushing down the ORDER BY clause when it's useful to do so.
*/
@@ -2123,21 +2142,23 @@ fetch_more_data(ForeignScanState *node)
/* Convert the data into HeapTuples */
numrows = PQntuples(res);
fsstate->tuples = (HeapTuple *) palloc0(numrows * sizeof(HeapTuple));
fsstate->num_tuples = numrows;
fsstate->next_tuple = 0;
for (i = 0; i < numrows; i++)
{
fsstate->tuples[i] =
make_tuple_from_result_row(res, i,
- fsstate->rel,
+ fsstate->relname,
+ fsstate->query,
+ fsstate->tupdesc,
fsstate->attinmeta,
fsstate->retrieved_attrs,
fsstate->temp_cxt);
}
/* Update fetch_ct_2 */
if (fsstate->fetch_ct_2 < 2)
fsstate->fetch_ct_2++;
/* Must be EOF if we didn't get as many tuples as we asked for. */
@@ -2341,21 +2362,23 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
*/
static void
store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res)
{
PG_TRY();
{
HeapTuple newtup;
newtup = make_tuple_from_result_row(res, 0,
- fmstate->rel,
+ RelationGetRelationName(fmstate->rel),
+ fmstate->query,
+ RelationGetDescr(fmstate->rel),
fmstate->attinmeta,
fmstate->retrieved_attrs,
fmstate->temp_cxt);
/* tuple will be deleted when it is cleared from the slot */
ExecStoreTuple(newtup, slot, InvalidBuffer, true);
}
PG_CATCH();
{
if (res)
PQclear(res);
@@ -2491,20 +2514,21 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
user = GetUserMapping(relation->rd_rel->relowner, server->serverid);
conn = GetConnection(server, user, false);
/*
* Construct cursor that retrieves whole rows from remote.
*/
cursor_number = GetCursorNumber(conn);
initStringInfo(&sql);
appendStringInfo(&sql, "DECLARE c%u CURSOR FOR ", cursor_number);
deparseAnalyzeSql(&sql, relation, &astate.retrieved_attrs);
+ astate.query = sql.data;
/* In what follows, do not risk leaking any PGresults. */
PG_TRY();
{
res = PQexec(conn, sql.data);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
pgfdw_report_error(ERROR, res, conn, false, sql.data);
PQclear(res);
res = NULL;
@@ -2632,21 +2656,23 @@ analyze_row_processor(PGresult *res, int row, PgFdwAnalyzeState *astate)
if (pos >= 0)
{
/*
* Create sample tuple from current result row, and store it in the
* position determined above. The tuple has to be created in anl_cxt.
*/
oldcontext = MemoryContextSwitchTo(astate->anl_cxt);
astate->rows[pos] = make_tuple_from_result_row(res, row,
- astate->rel,
+ RelationGetRelationName(astate->rel),
+ astate->query,
+ RelationGetDescr(astate->rel),
astate->attinmeta,
astate->retrieved_attrs,
astate->temp_cxt);
MemoryContextSwitchTo(oldcontext);
}
}
/*
* Import a foreign schema
@@ -2906,37 +2932,299 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
PG_RE_THROW();
}
PG_END_TRY();
ReleaseConnection(conn);
return commands;
}
/*
+ * Construct PgFdwRelationInfo from two join sources
+ */
+static void
+merge_fpinfo(RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ PgFdwRelationInfo *fpinfo,
+ JoinType jointype,
+ double rows,
+ int width)
+{
+ PgFdwRelationInfo *fpinfo_o;
+ PgFdwRelationInfo *fpinfo_i;
+
+ fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
+ fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
+
+ /* Mark that this join can be pushed down safely */
+ fpinfo->pushdown_safe = true;
+
+ /* Join relation must have conditions come from sources */
+ fpinfo->remote_conds = list_concat(copyObject(fpinfo_o->remote_conds),
+ copyObject(fpinfo_i->remote_conds));
+ fpinfo->local_conds = list_concat(copyObject(fpinfo_o->local_conds),
+ copyObject(fpinfo_i->local_conds));
+
+ /* Only for simple foreign table scan */
+ fpinfo->attrs_used = NULL;
+
+ /* rows and width will be set later */
+ fpinfo->rows = rows;
+ fpinfo->width = width;
+
+ /* A join have local conditions for outer and inner, so sum up them. */
+ fpinfo->local_conds_cost.startup = fpinfo_o->local_conds_cost.startup +
+ fpinfo_i->local_conds_cost.startup;
+ fpinfo->local_conds_cost.per_tuple = fpinfo_o->local_conds_cost.per_tuple +
+ fpinfo_i->local_conds_cost.per_tuple;
+
+ /* Don't consider correlation between local filters. */
+ fpinfo->local_conds_sel = fpinfo_o->local_conds_sel *
+ fpinfo_i->local_conds_sel;
+
+ fpinfo->use_remote_estimate = false;
+
+ /*
+ * These two comes default or per-server setting, so outer and inner must
+ * have same value.
+ */
+ fpinfo->fdw_startup_cost = fpinfo_o->fdw_startup_cost;
+ fpinfo->fdw_tuple_cost = fpinfo_o->fdw_tuple_cost;
+
+ /*
+ * TODO estimate more accurately
+ */
+ fpinfo->startup_cost = fpinfo->fdw_startup_cost +
+ fpinfo->local_conds_cost.startup;
+ fpinfo->total_cost = fpinfo->startup_cost +
+ (fpinfo->fdw_tuple_cost +
+ fpinfo->local_conds_cost.per_tuple +
+ cpu_tuple_cost) * fpinfo->rows;
+
+ /* serverid and userid are respectively identical */
+ fpinfo->server = fpinfo_o->server;
+ fpinfo->user = fpinfo_o->user;
+
+ fpinfo->outerrel = outerrel;
+ fpinfo->innerrel = innerrel;
+ fpinfo->jointype = jointype;
+
+#ifdef TODO_IF_NOT_NEEDED
+ /* This join can be pushed down safely */
+ fpinfo->pushdown_safe = true;
+#endif /* TODO_IF_NOT_NEEDED */
+
+ /* joinclauses and otherclauses will be set later */
+}
+
+/*
+ * postgresGetForeignJoinPaths
+ * Add possible ForeignPath to joinrel.
+ *
+ * Joins satisfy conditions below can be pushed down to the remote PostgreSQL
+ * server.
+ *
+ * 1) Join type is INNER or OUTER (one of LEFT/RIGHT/FULL)
+ * 2) Both outer and inner portions are safe to push-down
+ * 3) All foreign tables in the join belong to the same foreign server and use
+ * the same user mapping.
+ * 4) All join conditions are safe to push down
+ * 5) No relation has local filter (this can be relaxed for INNER JOIN, if we
+ * can move unpushable clauses upwards in the join tree).
+ */
+static void
+postgresGetForeignJoinPaths(PlannerInfo *root,
+ RelOptInfo *joinrel,
+ RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ JoinType jointype,
+ JoinPathExtraData *extra)
+{
+ PgFdwRelationInfo *fpinfo;
+ PgFdwRelationInfo *fpinfo_o;
+ PgFdwRelationInfo *fpinfo_i;
+ ForeignPath *joinpath;
+ double rows;
+ Cost startup_cost;
+ Cost total_cost;
+
+ ListCell *lc;
+ List *joinclauses;
+ List *otherclauses;
+
+ /*
+ * Skip if this join combination has been considered already.
+ */
+ if (joinrel->fdw_private)
+ {
+ ereport(DEBUG3, (errmsg("combination already considered")));
+ return;
+ }
+
+ /*
+ * Create unfinished PgFdwRelationInfo entry which is used to indicate that
+ * the join relaiton is already considered but the join can't be pushed
+ * down. Once we know that this join can be pushed down, we fill the entry
+ * and make it valid by calling merge_fpinfo.
+ *
+ * This unfinished entry prevents redandunt checks for a join combination
+ * which is already known as unsafe to push down.
+ */
+ fpinfo = (PgFdwRelationInfo *) palloc0(sizeof(PgFdwRelationInfo));
+ fpinfo->pushdown_safe = false;
+ joinrel->fdw_private = fpinfo;
+
+ /*
+ * We support all outer joins in addition to inner join. CROSS JOIN is
+ * an INNER JOIN with no conditions internally, so will be checked later.
+ */
+ if (jointype != JOIN_INNER && jointype != JOIN_LEFT &&
+ jointype != JOIN_RIGHT && jointype != JOIN_FULL)
+ {
+ ereport(DEBUG3, (errmsg("unsupported join type (SEMI, ANTI)")));
+ return;
+ }
+
+ /*
+ * Having valid PgFdwRelationInfo marked as "safe to push down" in
+ * RelOptInfo#fdw_private indicates that scanning against the relation can
+ * be pushed down. If either of them doesn't have PgFdwRelationInfo or it
+ * is not marked as safe, give up to push down this join relation.
+ */
+ fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
+ if (!fpinfo_o || !fpinfo_o->pushdown_safe)
+ {
+ ereport(DEBUG3, (errmsg("outer is not safe to push-down")));
+ return;
+ }
+ fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
+ if (!fpinfo_i || !fpinfo_i->pushdown_safe)
+ {
+ ereport(DEBUG3, (errmsg("inner is not safe to push-down")));
+ return;
+ }
+
+ /*
+ * No source relation can have local conditions.
+ * TODO:
+ * This can be relaxed
+ * if the join is an inner join and local conditions don't contain
+ * volatile function/operator, but as of now we leave it as future
+ * enhancement.
+ */
+ if (fpinfo_o->local_conds != NULL || fpinfo_i->local_conds != NULL)
+ {
+ ereport(DEBUG3, (errmsg("join with local filter")));
+ return;
+ }
+
+ /* Separate restrict list into join quals and quals on join relation */
+ joinclauses = extra->restrictlist;
+ if (IS_OUTER_JOIN(jointype))
+ {
+ extract_actual_join_clauses(joinclauses, &joinclauses, &otherclauses);
+ }
+ else
+ {
+ /* For inner join all clauses can be treated as join quals */
+ /*
+ * TODO:
+ * For an inner join all join quals can be treated as other quals and
+ * need not be all pushable.
+ */
+ joinclauses = extract_actual_clauses(joinclauses, false);
+ otherclauses = NIL;
+ }
+
+ /* Join quals must be safe to push down. */
+ foreach(lc, joinclauses)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+
+ if (!is_foreign_expr(root, joinrel, expr))
+ {
+ ereport(DEBUG3, (errmsg("join quals contains unsafe conditions")));
+ return;
+ }
+ }
+
+ /*
+ * Other condition for the join must be safe to push down.
+ * TODO: It's ok to have other conditions unsafe to push down. Add them to
+ * local_conds for fpinfo.
+ */
+ foreach(lc, otherclauses)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+
+ if (!is_foreign_expr(root, joinrel, expr))
+ {
+ ereport(DEBUG3, (errmsg("filter contains unsafe conditions")));
+ return;
+ }
+ }
+
+ /* Here we know that this join can be pushed-down to remote side. */
+
+ /* Construct fpinfo for the join relation */
+ merge_fpinfo(outerrel, innerrel, fpinfo, jointype, joinrel->rows,
+ joinrel->width);
+ fpinfo->joinclauses = joinclauses;
+ fpinfo->otherclauses = otherclauses;
+
+ /* TODO determine more accurate cost and rows of the join. */
+ rows = joinrel->rows;
+ startup_cost = fpinfo->startup_cost;
+ total_cost = fpinfo->total_cost;
+
+ /*
+ * Create a new join path and add it to the joinrel which represents a join
+ * between foreign tables.
+ */
+ joinpath = create_foreignscan_path(root,
+ joinrel,
+ rows,
+ startup_cost,
+ total_cost,
+ NIL, /* no pathkeys */
+ NULL, /* no required_outer */
+ NIL); /* no fdw_private */
+
+ /* Add generated path into joinrel by add_path(). */
+ add_path(joinrel, (Path *) joinpath);
+ elog(DEBUG3, "join path added for (%s) join (%s)",
+ bms_to_str(outerrel->relids), bms_to_str(innerrel->relids));
+
+ /* TODO Consider pathkeys for the join relation */
+
+ /* TODO consider parameterized paths */
+}
+
+/*
* Create a tuple from the specified row of the PGresult.
*
* rel is the local representation of the foreign table, attinmeta is
* conversion data for the rel's tupdesc, and retrieved_attrs is an
* integer list of the table column numbers present in the PGresult.
* temp_context is a working context that can be reset after each tuple.
*/
static HeapTuple
make_tuple_from_result_row(PGresult *res,
int row,
- Relation rel,
+ const char *relname,
+ const char *query,
+ TupleDesc tupdesc,
AttInMetadata *attinmeta,
List *retrieved_attrs,
MemoryContext temp_context)
{
HeapTuple tuple;
- TupleDesc tupdesc = RelationGetDescr(rel);
Datum *values;
bool *nulls;
ItemPointer ctid = NULL;
ConversionLocation errpos;
ErrorContextCallback errcallback;
MemoryContext oldcontext;
ListCell *lc;
int j;
Assert(row < PQntuples(res));
@@ -2949,21 +3237,23 @@ make_tuple_from_result_row(PGresult *res,
oldcontext = MemoryContextSwitchTo(temp_context);
values = (Datum *) palloc0(tupdesc->natts * sizeof(Datum));
nulls = (bool *) palloc(tupdesc->natts * sizeof(bool));
/* Initialize to nulls for any columns not present in result */
memset(nulls, true, tupdesc->natts * sizeof(bool));
/*
* Set up and install callback to report where conversion error occurs.
*/
- errpos.rel = rel;
+ errpos.relname = relname;
+ errpos.query = query;
+ errpos.tupdesc = tupdesc;
errpos.cur_attno = 0;
errcallback.callback = conversion_error_callback;
errcallback.arg = (void *) &errpos;
errcallback.previous = error_context_stack;
error_context_stack = &errcallback;
/*
* i indexes columns in the relation, j indexes columns in the PGresult.
*/
j = 0;
@@ -3039,27 +3329,55 @@ make_tuple_from_result_row(PGresult *res,
return tuple;
}
/*
* Callback function which is called when error occurs during column value
* conversion. Print names of column and relation.
*/
static void
conversion_error_callback(void *arg)
{
+ const char *attname;
+ const char *relname;
ConversionLocation *errpos = (ConversionLocation *) arg;
- TupleDesc tupdesc = RelationGetDescr(errpos->rel);
+ TupleDesc tupdesc = errpos->tupdesc;
+ StringInfoData buf;
+
+ if (errpos->relname)
+ {
+ /* error occurred in a scan against a foreign table */
+ initStringInfo(&buf);
+ if (errpos->cur_attno > 0)
+ appendStringInfo(&buf, "column \"%s\"",
+ NameStr(tupdesc->attrs[errpos->cur_attno - 1]->attname));
+ else if (errpos->cur_attno == SelfItemPointerAttributeNumber)
+ appendStringInfoString(&buf, "column \"ctid\"");
+ attname = buf.data;
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "foreign table \"%s\"", errpos->relname);
+ relname = buf.data;
+ }
+ else
+ {
+ /* error occurred in a scan against a foreign join */
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "column %d", errpos->cur_attno - 1);
+ attname = buf.data;
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "foreign join \"%s\"", errpos->query);
+ relname = buf.data;
+ }
if (errpos->cur_attno > 0 && errpos->cur_attno <= tupdesc->natts)
- errcontext("column \"%s\" of foreign table \"%s\"",
- NameStr(tupdesc->attrs[errpos->cur_attno - 1]->attname),
- RelationGetRelationName(errpos->rel));
+ errcontext("%s of %s", attname, relname);
}
/*
* Find an equivalence class member expression, all of whose Vars, come from
* the indicated relation.
*/
extern Expr *
find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
{
ListCell *lc_em;
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index f243de8..fc924bc 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -19,20 +19,26 @@
#include "utils/relcache.h"
#include "libpq-fe.h"
/*
* FDW-specific planner information kept in RelOptInfo.fdw_private for a
* foreign table. This information is collected by postgresGetForeignRelSize.
*/
typedef struct PgFdwRelationInfo
{
+ /*
+ * True means that the relation can be pushed down. Always true for
+ * simple foreign scan.
+ */
+ bool pushdown_safe;
+
/* baserestrictinfo clauses, broken down into safe and unsafe subsets. */
List *remote_conds;
List *local_conds;
/* Bitmap of attr numbers we need to fetch from the remote server. */
Bitmapset *attrs_used;
/* Cost and selectivity of local_conds. */
QualCost local_conds_cost;
Selectivity local_conds_sel;
@@ -46,20 +52,27 @@ typedef struct PgFdwRelationInfo
/* Options extracted from catalogs. */
bool use_remote_estimate;
Cost fdw_startup_cost;
Cost fdw_tuple_cost;
List *shippable_extensions; /* OIDs of whitelisted extensions */
/* Cached catalog information. */
ForeignTable *table;
ForeignServer *server;
UserMapping *user; /* only set in use_remote_estimate mode */
+
+ /* Join information */
+ RelOptInfo *outerrel;
+ RelOptInfo *innerrel;
+ JoinType jointype;
+ List *joinclauses;
+ List *otherclauses;
} PgFdwRelationInfo;
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
/* in connection.c */
extern PGconn *GetConnection(ForeignServer *server, UserMapping *user,
bool will_prep_stmt);
extern void ReleaseConnection(PGconn *conn);
@@ -77,31 +90,48 @@ extern List *ExtractExtensionList(const char *extensionsString,
/* in deparse.c */
extern void classifyConditions(PlannerInfo *root,
RelOptInfo *baserel,
List *input_conds,
List **remote_conds,
List **local_conds);
extern bool is_foreign_expr(PlannerInfo *root,
RelOptInfo *baserel,
Expr *expr);
-extern void deparseSelectSql(StringInfo buf,
+extern void deparseSelectSqlForRel(StringInfo buf,
PlannerInfo *root,
- RelOptInfo *baserel,
+ RelOptInfo *foreign_rel,
Bitmapset *attrs_used,
- List **retrieved_attrs);
-extern void appendWhereClause(StringInfo buf,
+ List *remote_conds,
+ List **params_list,
+ List **fdw_scan_tlist,
+ List **retrieved_attrs,
+ StringInfo relations,
+ List *pathkeys,
+ bool alias);
+extern void appendConditions(StringInfo buf,
PlannerInfo *root,
- RelOptInfo *baserel,
+ RelOptInfo *foreign_rel,
List *exprs,
- bool is_first,
+ const char *prefix,
List **params);
+extern void deparseJoinSql(StringInfo sql,
+ PlannerInfo *root,
+ RelOptInfo *baserel,
+ RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ const char *sql_o,
+ const char *sql_i,
+ JoinType jointype,
+ List *joinclauses,
+ List **fdw_scan_tlist,
+ List **retrieved_attrs);
extern void deparseInsertSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetAttrs, bool doNothing, List *returningList,
List **retrieved_attrs);
extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetAttrs, List *returningList,
List **retrieved_attrs);
extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 671e38c..59cd17c 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4,26 +4,31 @@
CREATE EXTENSION postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
+ EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (dbname '$$||current_database()||$$',
+ port '$$||current_setting('port')||$$'
+ )$$;
END;
$d$;
CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
+CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
-- ===================================================================
-- create objects used through FDW loopback server
-- ===================================================================
CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
c3 text,
@@ -32,38 +37,64 @@ CREATE TABLE "S 1"."T 1" (
c6 varchar(10),
c7 char(10),
c8 user_enum,
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)
);
+CREATE TABLE "S 1"."T 3" (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text,
+ CONSTRAINT t3_pkey PRIMARY KEY (c1)
+);
+CREATE TABLE "S 1"."T 4" (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c4 text,
+ CONSTRAINT t4_pkey PRIMARY KEY (c1)
+);
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,
'foo'::user_enum
FROM generate_series(1, 1000) id;
INSERT INTO "S 1"."T 2"
SELECT id,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 100) id;
+INSERT INTO "S 1"."T 3"
+ SELECT id,
+ id + 1,
+ 'AAA' || to_char(id, 'FM000')
+ FROM generate_series(1, 100) id;
+DELETE FROM "S 1"."T 3" WHERE c1 % 2 != 0; -- delete for outer join tests
+INSERT INTO "S 1"."T 4"
+ SELECT id,
+ id + 1,
+ 'AAA' || to_char(id, 'FM000')
+ FROM generate_series(1, 100) id;
+DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests
ANALYZE "S 1"."T 1";
ANALYZE "S 1"."T 2";
+ANALYZE "S 1"."T 3";
+ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
c4 timestamptz,
@@ -80,20 +111,43 @@ CREATE FOREIGN TABLE ft2 (
cx int,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10) default 'ft2',
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
+CREATE FOREIGN TABLE ft4 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text
+) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
+
+CREATE FOREIGN TABLE ft5 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text
+) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
+
+CREATE FOREIGN TABLE ft6 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 text
+) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
+CREATE USER view_owner;
+GRANT ALL ON ft5 TO view_owner;
+CREATE VIEW v_ft5 AS SELECT * FROM ft5;
+ALTER VIEW v_ft5 OWNER TO view_owner;
+CREATE USER MAPPING FOR view_owner SERVER loopback;
+
-- ===================================================================
-- tests for validator
-- ===================================================================
-- requiressl, krbsrvname and gsslib are omitted because they depend on
-- configure options
ALTER SERVER testserver1 OPTIONS (
use_remote_estimate 'false',
updatable 'true',
fdw_startup_cost '123.456',
fdw_tuple_cost '0.123',
@@ -161,22 +215,20 @@ SELECT * FROM ft1 WHERE false;
-- 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';
-- with FOR UPDATE/SHARE
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
-- 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;
-- ===================================================================
@@ -187,24 +239,25 @@ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r)
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote
--- parameterized remote path
+-- parameterized remote path for foreign table
EXPLAIN (VERBOSE, COSTS false)
- SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
+
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS false)
SELECT * FROM ft2 a, ft2 b
WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
SELECT * FROM ft2 a, ft2 b
WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5));
SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
-- we should not push order by clause with volatile expressions or unsafe
@@ -250,20 +303,138 @@ ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
-- ... now they can be shipped
EXPLAIN (VERBOSE, COSTS false)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
EXPLAIN (VERBOSE, COSTS false)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
-- ===================================================================
+-- JOIN queries
+-- ===================================================================
+-- join two tables
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+-- test the GUC enable_foreignjoin by default it's true, so test false
+SET enable_foreignjoin TO false;
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+RESET enable_foreignjoin;
+-- join three tables
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
+SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
+-- left outer join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+-- left outer join + placement of clauses.
+-- clauses within the nullable side are not pulled up, but top level clause on
+-- non-nullable side is pushed into non-nullable side
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10;
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10;
+-- clauses within the nullable side are not pulled up, but the top level clause
+-- on nullable side is not pushed down into nullable side
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
+ WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
+SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
+ WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
+-- right outer join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
+-- full outer join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
+-- full outer join + WHERE clause, only matched rows
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+-- join two tables with FOR UPDATE clause
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
+-- join two tables with FOR SHARE clause
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
+-- TODO: why is this test? join at WHERE clause
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON true WHERE (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON true WHERE (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+-- join in CTE
+EXPLAIN (COSTS false, VERBOSE)
+WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+-- ctid with whole-row reference
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+-- partially unsafe to push down, not pushed down
+-- TODO what's unsafe to pushdown here?
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN ft4 t3 ON t2.c1 = t3.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN ft4 t3 ON t2.c1 = t3.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+-- SEMI JOIN, not pushed down
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+-- ANTI JOIN, not pushed down
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
+-- CROSS JOIN, not pushed down
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+-- different server
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+-- different effective user for permission check
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+-- unsafe join conditions (c8 has a UDT)
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
+-- local filter (unsafe conditions on one side, c8 has a UDT)
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+-- Aggregate after UNION, for testing setrefs
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
+SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
+-- join two foreign tables and two local tables
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN "S 1"."T 1" t3 ON t1.c1 = t3."C 1" JOIN "S 1"."T 2" t4 ON t1.c1 = t4.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON t1.c1 = t2.c1 JOIN "S 1"."T 1" t3 ON t1.c1 = t3."C 1" JOIN "S 1"."T 2" t4 ON t1.c1 = t4.c1 ORDER BY t1.c1 OFFSET 10 LIMIT 10;
+-- TODO: join with lateral reference
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
+SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
+
+-- ===================================================================
-- 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 (VERBOSE, COSTS false) EXECUTE st1(1, 2);
EXECUTE st1(1, 1);
EXECUTE st1(101, 101);
-- subquery using stable function (can't be sent to remote)
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 date(c4) = '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20);
@@ -873,10 +1044,14 @@ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
-- We can fake this by dropping the type locally in our transaction.
CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors");
CREATE SCHEMA import_dest5;
BEGIN;
DROP TYPE "Colors" CASCADE;
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
FROM SERVER loopback INTO import_dest5; -- ERROR
ROLLBACK;
+
+-- Cleanup
+DROP OWNED BY view_owner;
+DROP USER view_owner;
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 1533a6b..d961f1b 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -985,20 +985,46 @@ GetForeignTable(Oid relid);
This function returns a ForeignTable object for
the foreign table with the given OID. A
ForeignTable object contains properties of the
foreign table (see foreign/foreign.h for details).
+UserMapping *
+GetUserMappingById(Oid umid);
+
+
+ This function returns a UserMapping object for
+ the given user mapping OID. The OID of a user mapping is available in
+ RelOptInfo for a foreign scan.
+ (If there is no mapping for the OID, it will throw an error.)
+ A UserMapping object contains properties of the
+ user mapping (see foreign/foreign.h for details).
+
+
+
+
+ForeignTable *
+GetForeignTable(Oid relid);
+
+
+ This function returns a ForeignTable object for
+ the foreign table with the given OID. A
+ ForeignTable object contains properties of the
+ foreign table (see foreign/foreign.h for details).
+
+
+
+
List *
GetForeignColumnOptions(Oid relid, AttrNumber attnum);
This function returns the per-column FDW options for the column with the
given foreign table OID and attribute number, in the form of a list of
DefElem. NIL is returned if the column has no
options.
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index 763ee7c..85ef743 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -24,20 +24,21 @@
#include "miscadmin.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/syscache.h"
extern Datum pg_options_to_table(PG_FUNCTION_ARGS);
extern Datum postgresql_fdw_validator(PG_FUNCTION_ARGS);
+static HeapTuple find_user_mapping(Oid userid, Oid serverid);
/*
* GetForeignDataWrapper - look up the foreign-data wrapper by OID.
*/
ForeignDataWrapper *
GetForeignDataWrapper(Oid fdwid)
{
Form_pg_foreign_data_wrapper fdwform;
ForeignDataWrapper *fdw;
Datum datum;
@@ -152,72 +153,158 @@ ForeignServer *
GetForeignServerByName(const char *srvname, bool missing_ok)
{
Oid serverid = get_foreign_server_oid(srvname, missing_ok);
if (!OidIsValid(serverid))
return NULL;
return GetForeignServer(serverid);
}
+/*
+ * GetUserMappingById - look up the user mapping by its OID.
+ */
+UserMapping *
+GetUserMappingById(Oid umid)
+{
+ Datum datum;
+ HeapTuple tp;
+ bool isnull;
+ UserMapping *um;
+
+ tp = SearchSysCache1(USERMAPPINGOID, ObjectIdGetDatum(umid));
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for user mapping %u", umid);
+
+ um = (UserMapping *) palloc(sizeof(UserMapping));
+
+ /* Extract the umuser */
+ datum = SysCacheGetAttr(USERMAPPINGOID,
+ tp,
+ Anum_pg_user_mapping_umuser,
+ &isnull);
+ Assert(!isnull);
+ um->userid = DatumGetObjectId(datum);
+
+ /* Extract the umserver */
+ datum = SysCacheGetAttr(USERMAPPINGOID,
+ tp,
+ Anum_pg_user_mapping_umserver,
+ &isnull);
+ Assert(!isnull);
+ um->serverid = DatumGetObjectId(datum);
+
+ /* Extract the umoptions */
+ datum = SysCacheGetAttr(USERMAPPINGOID,
+ tp,
+ Anum_pg_user_mapping_umoptions,
+ &isnull);
+ if (isnull)
+ um->options = NIL;
+ else
+ um->options = untransformRelOptions(datum);
+
+ ReleaseSysCache(tp);
+
+ return um;
+}
/*
* GetUserMapping - look up the user mapping.
*
* If no mapping is found for the supplied user, we also look for
* PUBLIC mappings (userid == InvalidOid).
*/
UserMapping *
GetUserMapping(Oid userid, Oid serverid)
{
Datum datum;
HeapTuple tp;
bool isnull;
UserMapping *um;
- tp = SearchSysCache2(USERMAPPINGUSERSERVER,
- ObjectIdGetDatum(userid),
- ObjectIdGetDatum(serverid));
-
- if (!HeapTupleIsValid(tp))
- {
- /* Not found for the specific user -- try PUBLIC */
- tp = SearchSysCache2(USERMAPPINGUSERSERVER,
- ObjectIdGetDatum(InvalidOid),
- ObjectIdGetDatum(serverid));
- }
-
- if (!HeapTupleIsValid(tp))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("user mapping not found for \"%s\"",
- MappingUserName(userid))));
+ tp = find_user_mapping(userid, serverid);
um = (UserMapping *) palloc(sizeof(UserMapping));
um->userid = userid;
um->serverid = serverid;
/* Extract the umoptions */
datum = SysCacheGetAttr(USERMAPPINGUSERSERVER,
tp,
Anum_pg_user_mapping_umoptions,
&isnull);
if (isnull)
um->options = NIL;
else
um->options = untransformRelOptions(datum);
ReleaseSysCache(tp);
return um;
}
+/*
+ * GetUserMappingId - look up the user mapping, and return its OID
+ *
+ * If no mapping is found for the supplied user, we also look for
+ * PUBLIC mappings (userid == InvalidOid).
+ */
+Oid
+GetUserMappingId(Oid userid, Oid serverid)
+{
+ HeapTuple tp;
+ Oid umid;
+
+ tp = find_user_mapping(userid, serverid);
+
+ /* Extract the Oid */
+ umid = HeapTupleGetOid(tp);
+
+ ReleaseSysCache(tp);
+
+ return umid;
+}
+
+
+/*
+ * find_user_mapping - Guts of GetUserMapping family.
+ *
+ * If no mapping is found for the supplied user, we also look for
+ * PUBLIC mappings (userid == InvalidOid).
+ */
+static HeapTuple
+find_user_mapping(Oid userid, Oid serverid)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache2(USERMAPPINGUSERSERVER,
+ ObjectIdGetDatum(userid),
+ ObjectIdGetDatum(serverid));
+
+ if (HeapTupleIsValid(tp))
+ return tp;
+
+ /* Not found for the specific user -- try PUBLIC */
+ tp = SearchSysCache2(USERMAPPINGUSERSERVER,
+ ObjectIdGetDatum(InvalidOid),
+ ObjectIdGetDatum(serverid));
+
+ if (!HeapTupleIsValid(tp))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("user mapping not found for \"%s\"",
+ MappingUserName(userid))));
+
+ return tp;
+}
+
/*
* GetForeignTable - look up the foreign table definition by relation oid.
*/
ForeignTable *
GetForeignTable(Oid relid)
{
Form_pg_foreign_table tableform;
ForeignTable *ft;
HeapTuple tp;
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 1b61fd9..a90f4f4 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -117,20 +117,21 @@ bool enable_seqscan = true;
bool enable_indexscan = true;
bool enable_indexonlyscan = true;
bool enable_bitmapscan = true;
bool enable_tidscan = true;
bool enable_sort = true;
bool enable_hashagg = true;
bool enable_nestloop = true;
bool enable_material = true;
bool enable_mergejoin = true;
bool enable_hashjoin = true;
+bool enable_foreignjoin = true;
typedef struct
{
PlannerInfo *root;
QualCost total;
} cost_qual_eval_context;
static List *extract_nonindex_conditions(List *qual_clauses, List *indexquals);
static MergeScanSelCache *cached_scansel(PlannerInfo *root,
RestrictInfo *rinfo,
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index a35c881..b632b94 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -249,21 +249,22 @@ add_paths_to_joinrel(PlannerInfo *root,
* joins, because there may be no other alternative.
*/
if (enable_hashjoin || jointype == JOIN_FULL)
hash_inner_and_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
/*
* 5. If inner and outer relations are foreign tables (or joins) belonging
* to the same server, give the FDW a chance to push down joins.
*/
- if (joinrel->fdwroutine &&
+ if (enable_foreignjoin &&
+ joinrel->fdwroutine &&
joinrel->fdwroutine->GetForeignJoinPaths)
joinrel->fdwroutine->GetForeignJoinPaths(root, joinrel,
outerrel, innerrel,
jointype, &extra);
/*
* 6. Finally, give extensions a chance to manipulate the path list.
*/
if (set_join_pathlist_hook)
set_join_pathlist_hook(root, joinrel, outerrel, innerrel,
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 9442e5f..8aa2e67 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -21,20 +21,21 @@
#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/nbtree.h"
#include "access/sysattr.h"
#include "access/transam.h"
#include "access/xlog.h"
#include "catalog/catalog.h"
#include "catalog/dependency.h"
#include "catalog/heap.h"
#include "foreign/fdwapi.h"
+#include "foreign/foreign.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/plancat.h"
#include "optimizer/predtest.h"
#include "optimizer/prep.h"
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
@@ -377,26 +378,34 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
}
list_free(indexoidlist);
}
rel->indexlist = indexinfos;
/* Grab foreign-table info using the relcache, while we have it */
if (relation->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
{
+ RangeTblEntry *rte;
+ Oid userid;
+
rel->serverid = GetForeignServerIdByRelId(RelationGetRelid(relation));
rel->fdwroutine = GetFdwRoutineForRelation(relation, true);
+
+ rte = planner_rt_fetch(rel->relid, root);
+ userid = OidIsValid(rte->checkAsUser) ? rte->checkAsUser : GetUserId();
+ rel->umid = GetUserMappingId(userid, rel->serverid);
}
else
{
rel->serverid = InvalidOid;
+ rel->umid = InvalidOid;
rel->fdwroutine = NULL;
}
heap_close(relation, NoLock);
/*
* Allow a plugin to editorialize on the info we obtained from the
* catalogs. Actions might include altering the assumed relation size,
* removing an index, or adding a hypothetical index to the indexlist.
*/
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 68a93a1..284da6d 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -116,20 +116,21 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
rel->lateral_relids = NULL;
rel->lateral_referencers = NULL;
rel->indexlist = NIL;
rel->pages = 0;
rel->tuples = 0;
rel->allvisfrac = 0;
rel->subplan = NULL;
rel->subroot = NULL;
rel->subplan_params = NIL;
rel->serverid = InvalidOid;
+ rel->umid = InvalidOid;
rel->fdwroutine = NULL;
rel->fdw_private = NULL;
rel->baserestrictinfo = NIL;
rel->baserestrictcost.startup = 0;
rel->baserestrictcost.per_tuple = 0;
rel->joininfo = NIL;
rel->has_eclass_joins = false;
/* Check type of rtable entry */
switch (rte->rtekind)
@@ -380,36 +381,46 @@ build_join_rel(PlannerInfo *root,
joinrel->lateral_relids = NULL;
joinrel->lateral_referencers = NULL;
joinrel->indexlist = NIL;
joinrel->pages = 0;
joinrel->tuples = 0;
joinrel->allvisfrac = 0;
joinrel->subplan = NULL;
joinrel->subroot = NULL;
joinrel->subplan_params = NIL;
joinrel->serverid = InvalidOid;
+ joinrel->umid = InvalidOid;
joinrel->fdwroutine = NULL;
joinrel->fdw_private = NULL;
joinrel->baserestrictinfo = NIL;
joinrel->baserestrictcost.startup = 0;
joinrel->baserestrictcost.per_tuple = 0;
joinrel->joininfo = NIL;
joinrel->has_eclass_joins = false;
/*
* Set up foreign-join fields if outer and inner relation are foreign
- * tables (or joins) belonging to the same server.
+ * tables (or joins) belonging to the same server and using the same
+ * user mapping.
+ *
+ * Otherwise those fields are left invalid, so FDW API will not be called
+ * for the join relation.
+ * TODO: It should suffice to just check the umid. If umid of both the
+ * relations is same, it implies same serverid and same user mapping both.
*/
if (OidIsValid(outer_rel->serverid) &&
- inner_rel->serverid == outer_rel->serverid)
+ inner_rel->serverid == outer_rel->serverid &&
+ OidIsValid(outer_rel->umid) &&
+ inner_rel->umid == outer_rel->umid)
{
joinrel->serverid = outer_rel->serverid;
+ joinrel->umid = outer_rel->umid;
joinrel->fdwroutine = outer_rel->fdwroutine;
}
/*
* Create a new tlist containing just the vars that need to be output from
* this join (ie, are needed for higher joinclauses or final output).
*
* NOTE: the tlist order for a join rel will depend on which pair of outer
* and inner rels we first try to build it from. But the contents should
* be the same regardless.
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index fda0fb9..459f793 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -857,20 +857,29 @@ static struct config_bool ConfigureNamesBool[] =
{
{"enable_hashjoin", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of hash join plans."),
NULL
},
&enable_hashjoin,
true,
NULL, NULL, NULL
},
{
+ {"enable_foreignjoin", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Allows the planner to push join between two foreign relations to the foreign server."),
+ NULL
+ },
+ &enable_foreignjoin,
+ true,
+ NULL, NULL, NULL
+ },
+ {
{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Enables genetic query optimization."),
gettext_noop("This algorithm attempts to do planning without "
"exhaustive searching.")
},
&enable_geqo,
true,
NULL, NULL, NULL
},
{
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index c820e09..cd1a3cb 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -64,20 +64,22 @@ typedef struct ForeignTable
{
Oid relid; /* relation Oid */
Oid serverid; /* server Oid */
List *options; /* ftoptions as DefElem list */
} ForeignTable;
extern ForeignServer *GetForeignServer(Oid serverid);
extern ForeignServer *GetForeignServerByName(const char *name, bool missing_ok);
extern UserMapping *GetUserMapping(Oid userid, Oid serverid);
+extern Oid GetUserMappingId(Oid userid, Oid serverid);
+extern UserMapping *GetUserMappingById(Oid umid);
extern ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
bool missing_ok);
extern ForeignTable *GetForeignTable(Oid relid);
extern List *GetForeignColumnOptions(Oid relid, AttrNumber attnum);
extern Oid get_foreign_data_wrapper_oid(const char *fdwname, bool missing_ok);
extern Oid get_foreign_server_oid(const char *servername, bool missing_ok);
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 6cf2e24..416ce99 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -477,20 +477,21 @@ typedef struct RelOptInfo
BlockNumber pages; /* size estimates derived from pg_class */
double tuples;
double allvisfrac;
/* use "struct Plan" to avoid including plannodes.h here */
struct Plan *subplan; /* if subquery */
PlannerInfo *subroot; /* if subquery */
List *subplan_params; /* if subquery */
/* Information about foreign tables and foreign joins */
Oid serverid; /* identifies server for the table or join */
+ Oid umid; /* identifies user mapping for the table or join */
/* use "struct FdwRoutine" to avoid including fdwapi.h here */
struct FdwRoutine *fdwroutine;
void *fdw_private;
/* used by various scans and joins: */
List *baserestrictinfo; /* RestrictInfo structures (if base
* rel) */
QualCost baserestrictcost; /* cost of evaluating the above */
List *joininfo; /* RestrictInfo structures for join clauses
* involving this rel */
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 25a7303..6f9fd37 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -59,20 +59,21 @@ extern bool enable_seqscan;
extern bool enable_indexscan;
extern bool enable_indexonlyscan;
extern bool enable_bitmapscan;
extern bool enable_tidscan;
extern bool enable_sort;
extern bool enable_hashagg;
extern bool enable_nestloop;
extern bool enable_material;
extern bool enable_mergejoin;
extern bool enable_hashjoin;
+extern bool enable_foreignjoin;
extern int constraint_exclusion;
extern double clamp_row_est(double nrows);
extern double index_pages_fetched(double tuples_fetched, BlockNumber pages,
double index_pages, PlannerInfo *root);
extern void cost_seqscan(Path *path, PlannerInfo *root, RelOptInfo *baserel,
ParamPathInfo *param_info);
extern void cost_samplescan(Path *path, PlannerInfo *root, RelOptInfo *baserel,
ParamPathInfo *param_info);
extern void cost_index(IndexPath *path, PlannerInfo *root,