pgsql_fdw_pushdown_v12.patch
text/plain
Filename: pgsql_fdw_pushdown_v12.patch
Type: text/plain
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
Series: patch v12
| File | + | − |
|---|---|---|
| contrib/pgsql_fdw/deparse.c | 0 | 0 |
| contrib/pgsql_fdw/expected/pgsql_fdw.out | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw.c | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw.h | 0 | 0 |
| contrib/pgsql_fdw/sql/pgsql_fdw.sql | 0 | 0 |
diff --git a/contrib/pgsql_fdw/deparse.c b/contrib/pgsql_fdw/deparse.c
index 8e79232..6f8f753 100644
*** a/contrib/pgsql_fdw/deparse.c
--- b/contrib/pgsql_fdw/deparse.c
***************
*** 14,19 ****
--- 14,21 ----
#include "access/transam.h"
#include "catalog/pg_class.h"
+ #include "catalog/pg_operator.h"
+ #include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
***************
*** 22,30 ****
--- 24,34 ----
#include "nodes/makefuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/var.h"
+ #include "parser/parser.h"
#include "parser/parsetree.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
+ #include "utils/syscache.h"
#include "pgsql_fdw.h"
*************** typedef struct foreign_executable_cxt
*** 35,49 ****
--- 39,77 ----
{
PlannerInfo *root;
RelOptInfo *foreignrel;
+ bool has_param;
} foreign_executable_cxt;
/*
* Get string representation which can be used in SQL statement from a node.
*/
+ static void deparseExpr(StringInfo buf, Expr *expr, PlannerInfo *root);
static void deparseRelation(StringInfo buf, Oid relid, PlannerInfo *root,
bool need_prefix);
static void deparseVar(StringInfo buf, Var *node, PlannerInfo *root,
bool need_prefix);
+ static void deparseConst(StringInfo buf, Const *node, PlannerInfo *root);
+ static void deparseBoolExpr(StringInfo buf, BoolExpr *node, PlannerInfo *root);
+ static void deparseNullTest(StringInfo buf, NullTest *node, PlannerInfo *root);
+ static void deparseDistinctExpr(StringInfo buf, DistinctExpr *node,
+ PlannerInfo *root);
+ static void deparseRelabelType(StringInfo buf, RelabelType *node,
+ PlannerInfo *root);
+ static void deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root);
+ static void deparseParam(StringInfo buf, Param *node, PlannerInfo *root);
+ static void deparseScalarArrayOpExpr(StringInfo buf, ScalarArrayOpExpr *node,
+ PlannerInfo *root);
+ static void deparseOpExpr(StringInfo buf, OpExpr *node, PlannerInfo *root);
+ static void deparseArrayRef(StringInfo buf, ArrayRef *node, PlannerInfo *root);
+ static void deparseArrayExpr(StringInfo buf, ArrayExpr *node, PlannerInfo *root);
+
+ /*
+ * Determine whether an expression can be evaluated on remote side safely.
+ */
+ static bool is_foreign_expr(PlannerInfo *root, RelOptInfo *baserel, Expr *expr,
+ bool *has_param);
+ static bool foreign_expr_walker(Node *node, foreign_executable_cxt *context);
+ static bool is_builtin(Oid procid);
/*
* Deparse query representation into SQL statement which suits for remote
*************** deparseSimpleSql(StringInfo buf,
*** 151,156 ****
--- 179,285 ----
}
/*
+ * Examine each element in the list baserestrictinfo of baserel, and sort them
+ * into three groups: remote_conds contains conditions which can be evaluated
+ * - remote_conds is push-down safe, and don't contain any Param node
+ * - param_conds is push-down safe, but contain some Param node
+ * - local_conds is not push-down safe
+ *
+ * Only remote_conds can be used in remote EXPLAIN, and remote_conds and
+ * param_conds can be used in final remote query.
+ */
+ void
+ sortConditions(PlannerInfo *root,
+ RelOptInfo *baserel,
+ List **remote_conds,
+ List **param_conds,
+ List **local_conds)
+ {
+ ListCell *lc;
+ bool has_param;
+
+ Assert(remote_conds);
+ Assert(param_conds);
+ Assert(local_conds);
+
+ foreach(lc, baserel->baserestrictinfo)
+ {
+ RestrictInfo *ri = (RestrictInfo *) lfirst(lc);
+
+ if (is_foreign_expr(root, baserel, ri->clause, &has_param))
+ {
+ if (has_param)
+ *param_conds = lappend(*param_conds, ri);
+ else
+ *remote_conds = lappend(*remote_conds, ri);
+ }
+ else
+ *local_conds = lappend(*local_conds, ri);
+ }
+ }
+
+ /*
+ * Deparse given expression into buf. Actual string operation is delegated to
+ * node-type-specific functions.
+ *
+ * Note that switch statement of this function MUST match the one in
+ * foreign_expr_walker to avoid unsupported error..
+ */
+ static void
+ deparseExpr(StringInfo buf, Expr *node, PlannerInfo *root)
+ {
+ /*
+ * This part must be match foreign_expr_walker.
+ */
+ switch (nodeTag(node))
+ {
+ case T_Const:
+ deparseConst(buf, (Const *) node, root);
+ break;
+ case T_BoolExpr:
+ deparseBoolExpr(buf, (BoolExpr *) node, root);
+ break;
+ case T_NullTest:
+ deparseNullTest(buf, (NullTest *) node, root);
+ break;
+ case T_DistinctExpr:
+ deparseDistinctExpr(buf, (DistinctExpr *) node, root);
+ break;
+ case T_RelabelType:
+ deparseRelabelType(buf, (RelabelType *) node, root);
+ break;
+ case T_FuncExpr:
+ deparseFuncExpr(buf, (FuncExpr *) node, root);
+ break;
+ case T_Param:
+ deparseParam(buf, (Param *) node, root);
+ break;
+ case T_ScalarArrayOpExpr:
+ deparseScalarArrayOpExpr(buf, (ScalarArrayOpExpr *) node, root);
+ break;
+ case T_OpExpr:
+ deparseOpExpr(buf, (OpExpr *) node, root);
+ break;
+ case T_Var:
+ deparseVar(buf, (Var *) node, root, false);
+ break;
+ case T_ArrayRef:
+ deparseArrayRef(buf, (ArrayRef *) node, root);
+ break;
+ case T_ArrayExpr:
+ deparseArrayExpr(buf, (ArrayExpr *) node, root);
+ break;
+ default:
+ {
+ ereport(ERROR,
+ (errmsg("unsupported expression for deparse"),
+ errdetail("%s", nodeToString(node))));
+ }
+ break;
+ }
+ }
+
+ /*
* Deparse node into buf, with relation qualifier if need_prefix was true. If
* node is a column of a foreign table, use value of colname FDW option (if any)
* instead of attribute name.
*************** deparseRelation(StringInfo buf,
*** 286,288 ****
--- 415,1146 ----
appendStringInfo(buf, "%s.", q_nspname);
appendStringInfo(buf, "%s", q_relname);
}
+
+ /*
+ * Deparse given constant value into buf. This function have to be kept in
+ * sync with get_const_expr.
+ */
+ static void
+ deparseConst(StringInfo buf,
+ Const *node,
+ PlannerInfo *root)
+ {
+ Oid typoutput;
+ bool typIsVarlena;
+ char *extval;
+ bool isfloat = false;
+ bool needlabel;
+
+ if (node->constisnull)
+ {
+ appendStringInfo(buf, "NULL");
+ return;
+ }
+
+ getTypeOutputInfo(node->consttype,
+ &typoutput, &typIsVarlena);
+ extval = OidOutputFunctionCall(typoutput, node->constvalue);
+
+ switch (node->consttype)
+ {
+ case ANYARRAYOID:
+ case ANYNONARRAYOID:
+ elog(ERROR, "anyarray and anyenum are not supported");
+ break;
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case OIDOID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ {
+ /*
+ * No need to quote unless they contain special values such as
+ * 'Nan'.
+ */
+ if (strspn(extval, "0123456789+-eE.") == strlen(extval))
+ {
+ if (extval[0] == '+' || extval[0] == '-')
+ appendStringInfo(buf, "(%s)", extval);
+ else
+ appendStringInfoString(buf, extval);
+ if (strcspn(extval, "eE.") != strlen(extval))
+ isfloat = true; /* it looks like a float */
+ }
+ else
+ appendStringInfo(buf, "'%s'", extval);
+ }
+ break;
+ case BITOID:
+ case VARBITOID:
+ appendStringInfo(buf, "B'%s'", extval);
+ break;
+ case BOOLOID:
+ if (strcmp(extval, "t") == 0)
+ appendStringInfoString(buf, "true");
+ else
+ appendStringInfoString(buf, "false");
+ break;
+
+ default:
+ {
+ const char *valptr;
+
+ appendStringInfoChar(buf, '\'');
+ for (valptr = extval; *valptr; valptr++)
+ {
+ char ch = *valptr;
+
+ /*
+ * standard_conforming_strings of remote session should be
+ * set to similar value as local session.
+ */
+ if (SQL_STR_DOUBLE(ch, !standard_conforming_strings))
+ appendStringInfoChar(buf, ch);
+ appendStringInfoChar(buf, ch);
+ }
+ appendStringInfoChar(buf, '\'');
+ }
+ break;
+ }
+
+ /*
+ * Append ::typename unless the constant will be implicitly typed as the
+ * right type when it is read in.
+ *
+ * XXX this code has to be kept in sync with the behavior of the parser,
+ * especially make_const.
+ */
+ switch (node->consttype)
+ {
+ case BOOLOID:
+ case INT4OID:
+ case UNKNOWNOID:
+ needlabel = false;
+ break;
+ case NUMERICOID:
+ needlabel = !isfloat || (node->consttypmod >= 0);
+ break;
+ default:
+ needlabel = true;
+ break;
+ }
+ if (needlabel)
+ {
+ appendStringInfo(buf, "::%s",
+ format_type_with_typemod(node->consttype,
+ node->consttypmod));
+ }
+ }
+
+ static void
+ deparseBoolExpr(StringInfo buf,
+ BoolExpr *node,
+ PlannerInfo *root)
+ {
+ ListCell *lc;
+ char *op;
+ bool first;
+
+ switch (node->boolop)
+ {
+ case AND_EXPR:
+ op = "AND";
+ break;
+ case OR_EXPR:
+ op = "OR";
+ break;
+ case NOT_EXPR:
+ appendStringInfo(buf, "(NOT ");
+ deparseExpr(buf, list_nth(node->args, 0), root);
+ appendStringInfo(buf, ")");
+ return;
+ }
+
+ first = true;
+ appendStringInfo(buf, "(");
+ foreach(lc, node->args)
+ {
+ if (!first)
+ appendStringInfo(buf, " %s ", op);
+ deparseExpr(buf, (Expr *) lfirst(lc), root);
+ first = false;
+ }
+ appendStringInfo(buf, ")");
+ }
+
+ /*
+ * Deparse given IS [NOT] NULL test expression into buf.
+ */
+ static void
+ deparseNullTest(StringInfo buf,
+ NullTest *node,
+ PlannerInfo *root)
+ {
+ appendStringInfoChar(buf, '(');
+ deparseExpr(buf, node->arg, root);
+ if (node->nulltesttype == IS_NULL)
+ appendStringInfo(buf, " IS NULL)");
+ else
+ appendStringInfo(buf, " IS NOT NULL)");
+ }
+
+ static void
+ deparseDistinctExpr(StringInfo buf,
+ DistinctExpr *node,
+ PlannerInfo *root)
+ {
+ Assert(list_length(node->args) == 2);
+
+ deparseExpr(buf, linitial(node->args), root);
+ appendStringInfo(buf, " IS DISTINCT FROM ");
+ deparseExpr(buf, lsecond(node->args), root);
+ }
+
+ static void
+ deparseRelabelType(StringInfo buf,
+ RelabelType *node,
+ PlannerInfo *root)
+ {
+ char *typname;
+
+ Assert(node->arg);
+
+ /* We don't need to deparse cast when argument has same type as result. */
+ if (IsA(node->arg, Const) &&
+ ((Const *) node->arg)->consttype == node->resulttype &&
+ ((Const *) node->arg)->consttypmod == -1)
+ {
+ deparseExpr(buf, node->arg, root);
+ return;
+ }
+
+ typname = format_type_with_typemod(node->resulttype, node->resulttypmod);
+ appendStringInfoChar(buf, '(');
+ deparseExpr(buf, node->arg, root);
+ appendStringInfo(buf, ")::%s", typname);
+ }
+
+ /*
+ * Deparse given node which represents a function call into buf. We treat only
+ * explicit function call and explicit cast (coerce), because others are
+ * processed on remote side if necessary.
+ *
+ * Function name (and type name) is always qualified by schema name to avoid
+ * problems caused by different setting of search_path on remote side.
+ */
+ static void
+ deparseFuncExpr(StringInfo buf,
+ FuncExpr *node,
+ PlannerInfo *root)
+ {
+ Oid pronamespace;
+ const char *schemaname;
+ const char *funcname;
+ ListCell *arg;
+ bool first;
+
+ pronamespace = get_func_namespace(node->funcid);
+ schemaname = quote_identifier(get_namespace_name(pronamespace));
+ funcname = quote_identifier(get_func_name(node->funcid));
+
+ if (node->funcformat == COERCE_EXPLICIT_CALL)
+ {
+ /* Function call, deparse all arguments recursively. */
+ appendStringInfo(buf, "%s.%s(", schemaname, funcname);
+ first = true;
+ foreach(arg, node->args)
+ {
+ if (!first)
+ appendStringInfo(buf, ", ");
+ deparseExpr(buf, lfirst(arg), root);
+ first = false;
+ }
+ appendStringInfoChar(buf, ')');
+ }
+ else if (node->funcformat == COERCE_EXPLICIT_CAST)
+ {
+ /* Explicit cast, deparse only first argument. */
+ appendStringInfoChar(buf, '(');
+ deparseExpr(buf, linitial(node->args), root);
+ appendStringInfo(buf, ")::%s", funcname);
+ }
+ else
+ {
+ /* Implicit cast, deparse only first argument. */
+ deparseExpr(buf, linitial(node->args), root);
+ }
+ }
+
+ /*
+ * Deparse given Param node into buf.
+ *
+ * We don't renumber parameter id, because skipping $1 is not cause problem
+ * as far as we pass through all arguments.
+ */
+ static void
+ deparseParam(StringInfo buf,
+ Param *node,
+ PlannerInfo *root)
+ {
+ Assert(node->paramkind == PARAM_EXTERN);
+
+ appendStringInfo(buf, "$%d", node->paramid);
+ }
+
+ /*
+ * Deparse given ScalarArrayOpExpr expression into buf. To avoid problems
+ * around priority of operations, we always parenthesize the arguments. Also we
+ * use OPERATOR(schema.operator) notation to determine remote operator exactly.
+ */
+ static void
+ deparseScalarArrayOpExpr(StringInfo buf,
+ ScalarArrayOpExpr *node,
+ PlannerInfo *root)
+ {
+ HeapTuple tuple;
+ Form_pg_operator form;
+ const char *opnspname;
+ char *opname;
+ Expr *arg1;
+ Expr *arg2;
+
+ /* Retrieve necessary information about the operator from system catalog. */
+ tuple = SearchSysCache1(OPEROID, ObjectIdGetDatum(node->opno));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for operator %u", node->opno);
+ form = (Form_pg_operator) GETSTRUCT(tuple);
+ /* opname is not a SQL identifier, so we don't need to quote it. */
+ opname = NameStr(form->oprname);
+ opnspname = quote_identifier(get_namespace_name(form->oprnamespace));
+ ReleaseSysCache(tuple);
+
+ /* Sanity check. */
+ Assert(list_length(node->args) == 2);
+
+ /* Always parenthesize the expression. */
+ appendStringInfoChar(buf, '(');
+
+ /* Extract operands. */
+ arg1 = linitial(node->args);
+ arg2 = lsecond(node->args);
+
+ /* Deparse fully qualified operator name. */
+ deparseExpr(buf, arg1, root);
+ appendStringInfo(buf, " OPERATOR(%s.%s) %s (",
+ opnspname, opname, node->useOr ? "ANY" : "ALL");
+ deparseExpr(buf, arg2, root);
+ appendStringInfoChar(buf, ')');
+
+ /* Always parenthesize the expression. */
+ appendStringInfoChar(buf, ')');
+ }
+
+ /*
+ * Deparse given operator expression into buf. To avoid problems around
+ * priority of operations, we always parenthesize the arguments. Also we use
+ * OPERATOR(schema.operator) notation to determine remote operator exactly.
+ */
+ static void
+ deparseOpExpr(StringInfo buf,
+ OpExpr *node,
+ PlannerInfo *root)
+ {
+ HeapTuple tuple;
+ Form_pg_operator form;
+ const char *opnspname;
+ char *opname;
+ char oprkind;
+ ListCell *arg;
+
+ /* Retrieve necessary information about the operator from system catalog. */
+ tuple = SearchSysCache1(OPEROID, ObjectIdGetDatum(node->opno));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for operator %u", node->opno);
+ form = (Form_pg_operator) GETSTRUCT(tuple);
+ opnspname = quote_identifier(get_namespace_name(form->oprnamespace));
+ /* opname is not a SQL identifier, so we don't need to quote it. */
+ opname = NameStr(form->oprname);
+ oprkind = form->oprkind;
+ ReleaseSysCache(tuple);
+
+ /* Sanity check. */
+ Assert((oprkind == 'r' && list_length(node->args) == 1) ||
+ (oprkind == 'l' && list_length(node->args) == 1) ||
+ (oprkind == 'b' && list_length(node->args) == 2));
+
+ /* Always parenthesize the expression. */
+ appendStringInfoChar(buf, '(');
+
+ /* Deparse first operand. */
+ arg = list_head(node->args);
+ if (oprkind == 'r' || oprkind == 'b')
+ {
+ deparseExpr(buf, lfirst(arg), root);
+ appendStringInfoChar(buf, ' ');
+ }
+
+ /* Deparse fully qualified operator name. */
+ appendStringInfo(buf, "OPERATOR(%s.%s)", opnspname, opname);
+
+ /* Deparse last operand. */
+ arg = list_tail(node->args);
+ if (oprkind == 'l' || oprkind == 'b')
+ {
+ appendStringInfoChar(buf, ' ');
+ deparseExpr(buf, lfirst(arg), root);
+ }
+
+ appendStringInfoChar(buf, ')');
+ }
+
+ static void
+ deparseArrayRef(StringInfo buf,
+ ArrayRef *node,
+ PlannerInfo *root)
+ {
+ ListCell *lowlist_item;
+ ListCell *uplist_item;
+
+ /* Always parenthesize the expression. */
+ appendStringInfoChar(buf, '(');
+
+ /* Deparse referenced array expression first. */
+ appendStringInfoChar(buf, '(');
+ deparseExpr(buf, node->refexpr, root);
+ appendStringInfoChar(buf, ')');
+
+ /* Deparse subscripts expression. */
+ lowlist_item = list_head(node->reflowerindexpr); /* could be NULL */
+ foreach(uplist_item, node->refupperindexpr)
+ {
+ appendStringInfoChar(buf, '[');
+ if (lowlist_item)
+ {
+ deparseExpr(buf, lfirst(lowlist_item), root);
+ appendStringInfoChar(buf, ':');
+ lowlist_item = lnext(lowlist_item);
+ }
+ deparseExpr(buf, lfirst(uplist_item), root);
+ appendStringInfoChar(buf, ']');
+ }
+
+ appendStringInfoChar(buf, ')');
+ }
+
+
+ /*
+ * Deparse given array of something into buf.
+ */
+ static void
+ deparseArrayExpr(StringInfo buf,
+ ArrayExpr *node,
+ PlannerInfo *root)
+ {
+ ListCell *lc;
+ bool first = true;
+
+ appendStringInfo(buf, "ARRAY[");
+ foreach(lc, node->elements)
+ {
+ if (!first)
+ appendStringInfo(buf, ", ");
+ deparseExpr(buf, lfirst(lc), root);
+
+ first = false;
+ }
+ appendStringInfoChar(buf, ']');
+
+ /* If the array is empty, we need explicit cast to the array type. */
+ if (node->elements == NIL)
+ {
+ char *typname;
+
+ typname = format_type_with_typemod(node->array_typeid, -1);
+ appendStringInfo(buf, "::%s", typname);
+ }
+ }
+
+ /*
+ * Returns true if given expr is safe to evaluate on the foreign server. If
+ * result is true, extra information has_param tells whether given expression
+ * contains any Param node. This is useful to determine whether the expression
+ * can be used in remote EXPLAIN.
+ */
+ static bool
+ is_foreign_expr(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Expr *expr,
+ bool *has_param)
+ {
+ foreign_executable_cxt context;
+ context.root = root;
+ context.foreignrel = baserel;
+ context.has_param = false;
+
+ /*
+ * An expression which includes any mutable function can't be pushed down
+ * because it's result is not stable. For example, pushing now() down to
+ * remote side would cause confusion from the clock offset.
+ * If we have routine mapping infrastructure in future release, we will be
+ * able to choose function to be pushed down in finer granularity.
+ */
+ if (contain_mutable_functions((Node *) expr))
+ return false;
+
+ /*
+ * Check that the expression consists of nodes which are known as safe to
+ * be pushed down.
+ */
+ if (foreign_expr_walker((Node *) expr, &context))
+ return false;
+
+ /*
+ * Tell caller whether the given expression contains any Param node, which
+ * can't be used in EXPLAIN statement before executor starts.
+ */
+ *has_param = context.has_param;
+
+ return true;
+ }
+
+ /*
+ * Return true if node includes any node which is not known as safe to be
+ * pushed down.
+ */
+ static bool
+ foreign_expr_walker(Node *node, foreign_executable_cxt *context)
+ {
+ if (node == NULL)
+ return false;
+
+ /*
+ * Special case handling for List; expression_tree_walker handles List as
+ * well as other Expr nodes. For instance, List is used in RestrictInfo
+ * for args of FuncExpr node.
+ *
+ * Although the comments of expression_tree_walker mention that
+ * RangeTblRef, FromExpr, JoinExpr, and SetOperationStmt are handled as
+ * well, but we don't care them because they are not used in RestrictInfo.
+ * If one of them was passed into, default label catches it and give up
+ * traversing.
+ */
+ if (IsA(node, List))
+ {
+ ListCell *lc;
+
+ foreach(lc, (List *) node)
+ {
+ if (foreign_expr_walker(lfirst(lc), context))
+ return true;
+ }
+ return false;
+ }
+
+ /*
+ * If return type of given expression is not built-in, it can't be pushed
+ * down because it might has incompatible semantics on remote side.
+ */
+ if (!is_builtin(exprType(node)))
+ return true;
+
+ switch (nodeTag(node))
+ {
+ case T_Const:
+ /*
+ * Using anyarray and/or anyenum in remote query is not supported.
+ */
+ if (((Const *) node)->consttype == ANYARRAYOID ||
+ ((Const *) node)->consttype == ANYNONARRAYOID)
+ return true;
+ break;
+ case T_BoolExpr:
+ case T_NullTest:
+ case T_DistinctExpr:
+ case T_RelabelType:
+ /*
+ * These type of nodes are known as safe to be pushed down.
+ * Of course the subtree of the node, if any, should be checked
+ * continuously at the tail of this function.
+ */
+ break;
+ /*
+ * If function used by the expression is not built-in, it can't be
+ * pushed down because it might has incompatible semantics on remote
+ * side.
+ */
+ case T_FuncExpr:
+ {
+ FuncExpr *fe = (FuncExpr *) node;
+ if (!is_builtin(fe->funcid))
+ return true;
+ }
+ break;
+ case T_Param:
+ /*
+ * Only external parameters can be pushed down.:
+ */
+ {
+ if (((Param *) node)->paramkind != PARAM_EXTERN)
+ return true;
+
+ /* Mark that this expression contains Param node. */
+ context->has_param = true;
+ }
+ break;
+ case T_ScalarArrayOpExpr:
+ /*
+ * Only built-in operators can be pushed down. In addition,
+ * underlying function must be built-in and immutable, but we don't
+ * check volatility here; such check must be done already with
+ * contain_mutable_functions.
+ */
+ {
+ ScalarArrayOpExpr *oe = (ScalarArrayOpExpr *) node;
+
+ if (!is_builtin(oe->opno) || !is_builtin(oe->opfuncid))
+ return true;
+
+ /*
+ * If the operator takes collatable type as operands, we push
+ * down only "=" and "<>" which are not affected by collation.
+ * Other operators might be safe about collation, but these two
+ * seem enogh to cover practical use cases.
+ */
+ if (exprInputCollation(node) != InvalidOid)
+ {
+ char *opname = get_opname(oe->opno);
+
+ if (strcmp(opname, "=") != 0 && strcmp(opname, "<>") != 0)
+ return true;
+ }
+
+ /* operands are checked later */
+ }
+ break;
+ case T_OpExpr:
+ /*
+ * Only built-in operators can be pushed down. In addition,
+ * underlying function must be built-in and immutable, but we don't
+ * check volatility here; such check must be done already with
+ * contain_mutable_functions.
+ */
+ {
+ OpExpr *oe = (OpExpr *) node;
+
+ if (!is_builtin(oe->opno) || !is_builtin(oe->opfuncid))
+ return true;
+
+ /*
+ * If the operator takes collatable type as operands, we push
+ * down only "=" and "<>" which are not affected by collation.
+ * Other operators might be safe about collation, but these two
+ * seem enogh to cover practical use cases.
+ */
+ if (exprInputCollation(node) != InvalidOid)
+ {
+ char *opname = get_opname(oe->opno);
+
+ if (strcmp(opname, "=") != 0 && strcmp(opname, "<>") != 0)
+ return true;
+ }
+
+ /* operands are checked later */
+ }
+ break;
+ case T_Var:
+ /*
+ * Var can be pushed down if it is in the foreign table.
+ * XXX Var of other relation can be here?
+ */
+ {
+ Var *var = (Var *) node;
+ foreign_executable_cxt *f_context;
+
+ f_context = (foreign_executable_cxt *) context;
+ if (var->varno != f_context->foreignrel->relid ||
+ var->varlevelsup != 0)
+ return true;
+ }
+ break;
+ case T_ArrayRef:
+ /*
+ * ArrayRef which holds non-built-in typed elements can't be pushed
+ * down.
+ */
+ {
+ ArrayRef *ar = (ArrayRef *) node;;
+
+ if (!is_builtin(ar->refelemtype))
+ return true;
+
+ /* Assignment should not be in restrictions. */
+ if (ar->refassgnexpr != NULL)
+ return true;
+ }
+ break;
+ case T_ArrayExpr:
+ /*
+ * ArrayExpr which holds non-built-in typed elements can't be pushed
+ * down.
+ */
+ {
+ if (!is_builtin(((ArrayExpr *) node)->element_typeid))
+ return true;
+ }
+ break;
+ default:
+ {
+ ereport(DEBUG3,
+ (errmsg("expression is too complex"),
+ errdetail("%s", nodeToString(node))));
+ return true;
+ }
+ break;
+ }
+
+ return expression_tree_walker(node, foreign_expr_walker, context);
+ }
+
+ /*
+ * Return true if given object is one of built-in objects.
+ */
+ static bool
+ is_builtin(Oid oid)
+ {
+ return (oid < FirstNormalObjectId);
+ }
+
+ /*
+ * Deparse WHERE clause from given list of RestrictInfo and append them to buf.
+ * We assume that buf already holds a SQL statement which ends with valid WHERE
+ * clause.
+ */
+ void
+ appendWhereClause(StringInfo buf,
+ bool has_where,
+ List *exprs,
+ PlannerInfo *root)
+ {
+ bool first = true;
+ ListCell *lc;
+
+ if (!has_where)
+ appendStringInfo(buf, " WHERE ");
+
+ foreach(lc, exprs)
+ {
+ RestrictInfo *ri = (RestrictInfo *) lfirst(lc);
+
+ /* Connect expressions with "AND" and parenthesize whole condition. */
+ if (!first)
+ appendStringInfo(buf, " AND ");
+
+ appendStringInfoChar(buf, '(');
+ deparseExpr(buf, ri->clause, root);
+ appendStringInfoChar(buf, ')');
+
+ first = false;
+ }
+ }
diff --git a/contrib/pgsql_fdw/expected/pgsql_fdw.out b/contrib/pgsql_fdw/expected/pgsql_fdw.out
index 4252cc0..8e50614 100644
*** a/contrib/pgsql_fdw/expected/pgsql_fdw.out
--- b/contrib/pgsql_fdw/expected/pgsql_fdw.out
*************** SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.
*** 229,244 ****
(10 rows)
-- with WHERE clause
! EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7
! Filter: ((t1.c1 = 101) AND ((t1.c6)::text = '1'::text) AND (t1.c7 = '1'::bpchar))
! Remote SQL: DECLARE pgsql_fdw_cursor_4 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
(4 rows)
! SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
c1 | c2 | c3 | c4 | c5 | c6 | c7
-----+----+-------+------------------------------+--------------------------+----+------------
101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1
--- 229,244 ----
(10 rows)
-- with WHERE clause
! EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7
! Filter: (t1.c7 >= '1'::bpchar)
! Remote SQL: DECLARE pgsql_fdw_cursor_4 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 101)) AND (((c6)::text OPERATOR(pg_catalog.=) '1'::text))
(4 rows)
! SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
c1 | c2 | c3 | c4 | c5 | c6 | c7
-----+----+-------+------------------------------+--------------------------+----+------------
101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1
*************** EXPLAIN (COSTS false) SELECT * FROM ft1
*** 343,362 ****
(3 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = abs(c2))
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = c2)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
-- ===================================================================
-- parameterized queries
--- 343,433 ----
(3 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) pg_catalog.abs(c2)))
! (2 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) c2))
! (2 rows)
!
! -- ===================================================================
! -- WHERE push down
! -- ===================================================================
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 100)) AND ((c2 OPERATOR(pg_catalog.=) 0))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
! QUERY PLAN
! ---------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" IS NULL))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
! QUERY PLAN
! -------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" IS NOT NULL))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ((pg_catalog.round(pg_catalog.abs("C 1"), 0) OPERATOR(pg_catalog.=) 1::numeric))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) (OPERATOR(pg_catalog.-) "C 1")))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r)
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ((1::numeric OPERATOR(pg_catalog.=) ("C 1" OPERATOR(pg_catalog.!))))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" IS NOT NULL) IS DISTINCT FROM ("C 1" IS NOT NULL))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) ANY (ARRAY[c2, 1, ("C 1" OPERATOR(pg_catalog.+) 0)])))
! (2 rows)
!
! EXPLAIN (COSTS false) SELECT * FROM ft1 ft WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on ft1 ft
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) ((ARRAY["C 1", c2, 3])[1])))
! (2 rows)
-- ===================================================================
-- parameterized queries
*************** EXPLAIN (COSTS false) SELECT * FROM ft1
*** 364,380 ****
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (COSTS false) EXECUTE st1(1, 2);
! QUERY PLAN
! -------------------------------------------------------------------------------------------
Nested Loop
-> Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1"
! -> Materialize
! -> Foreign Scan on ft2 t2
! Filter: (c1 = 2)
! Remote SQL: SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1"
! (8 rows)
EXECUTE st1(1, 1);
c3 | c3
--- 435,448 ----
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (COSTS false) EXECUTE st1(1, 2);
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------
Nested Loop
-> Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1))
! -> Foreign Scan on ft2 t2
! Remote SQL: SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 2))
! (5 rows)
EXECUTE st1(1, 1);
c3 | c3
*************** EXECUTE st1(101, 101);
*** 391,411 ****
-- subquery using stable function (can't be pushed down)
PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st2(10, 20);
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
-> Hash Join
Hash Cond: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Filter: (c1 < 20)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
-> Hash
-> HashAggregate
-> Foreign Scan on ft2 t2
! Filter: ((c1 > 10) AND (date_part('dow'::text, c4) = 6::double precision))
! Remote SQL: SELECT "C 1", NULL, c3, c4, NULL, NULL, NULL FROM "S 1"."T 1"
! (12 rows)
EXECUTE st2(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
--- 459,478 ----
-- subquery using stable function (can't be pushed down)
PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st2(10, 20);
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
-> Hash Join
Hash Cond: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.<) 20))
-> Hash
-> HashAggregate
-> Foreign Scan on ft2 t2
! Filter: (date_part('dow'::text, c4) = 6::double precision)
! Remote SQL: SELECT "C 1", NULL, c3, c4, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.>) 10))
! (11 rows)
EXECUTE st2(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
*************** EXECUTE st1(101, 101);
*** 422,442 ****
-- subquery using immutable function (can be pushed down)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c5) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st3(10, 20);
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
! -> Hash Join
! Hash Cond: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Filter: (c1 < 20)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! -> Hash
! -> HashAggregate
! -> Foreign Scan on ft2 t2
! Filter: ((c1 > 10) AND (date_part('dow'::text, c5) = 6::double precision))
! Remote SQL: SELECT "C 1", NULL, c3, NULL, c5, NULL, NULL FROM "S 1"."T 1"
! (12 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
--- 489,506 ----
-- subquery using immutable function (can be pushed down)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c5) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st3(10, 20);
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
! -> Nested Loop Semi Join
! Join Filter: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.<) 20))
! -> Materialize
! -> Foreign Scan on ft2 t2
! Remote SQL: SELECT "C 1", NULL, c3, NULL, c5, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.>) 10)) AND ((pg_catalog.date_part('dow'::text, c5) OPERATOR(pg_catalog.=) 6::double precision))
! (9 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
*************** EXECUTE st3(20, 30);
*** 453,504 ****
-- custom plan should be chosen
PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! ---------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = $1)
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
-- cleanup
DEALLOCATE st1;
--- 517,562 ----
-- custom plan should be chosen
PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1))
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1))
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1))
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1))
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1))
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) $1))
! (2 rows)
-- cleanup
DEALLOCATE st1;
diff --git a/contrib/pgsql_fdw/pgsql_fdw.c b/contrib/pgsql_fdw/pgsql_fdw.c
index 17693e0..8975955 100644
*** a/contrib/pgsql_fdw/pgsql_fdw.c
--- b/contrib/pgsql_fdw/pgsql_fdw.c
***************
*** 17,23 ****
#include "catalog/pg_foreign_table.h"
#include "commands/defrem.h"
#include "commands/explain.h"
- #include "foreign/fdwapi.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "optimizer/cost.h"
--- 17,22 ----
*************** PG_MODULE_MAGIC;
*** 55,60 ****
--- 54,64 ----
#define CURSOR_NAME_FORMAT "pgsql_fdw_cursor_%u"
static uint32 cursor_id = 0;
+ /* Convenient macros for accessing the first record of PGresult. */
+ #define PGRES_VAL0(col) (PQgetvalue(res, 0, (col)))
+ #define PGRES_NULL0(col) (PQgetisnull(res, 0, (col)))
+
+
/*
* FDW-specific information for RelOptInfo.fdw_private. This is used to pass
* information from pgsqlGetForeignRelSize to pgsqlGetForeignPaths.
*************** typedef struct PgsqlFdwPlanState {
*** 65,72 ****
--- 69,80 ----
* GetForeignPaths.
*/
StringInfoData sql;
+ bool has_where;
Cost startup_cost;
Cost total_cost;
+ List *remote_conds;
+ List *param_conds;
+ List *local_conds;
/* Cached catalog information. */
ForeignTable *table;
*************** pgsqlGetForeignRelSize(PlannerInfo *root
*** 244,249 ****
--- 252,258 ----
RelOptInfo *baserel,
Oid foreigntableid)
{
+ PgsqlFdwPlanState *fpstate;
StringInfo sql;
ForeignTable *table;
ForeignServer *server;
*************** pgsqlGetForeignRelSize(PlannerInfo *root
*** 253,259 ****
int width;
Cost startup_cost;
Cost total_cost;
! PgsqlFdwPlanState *fpstate;
Selectivity sel;
/*
--- 262,270 ----
int width;
Cost startup_cost;
Cost total_cost;
! List *remote_conds = NIL;
! List *param_conds = NIL;
! List *local_conds = NIL;
Selectivity sel;
/*
*************** pgsqlGetForeignRelSize(PlannerInfo *root
*** 263,268 ****
--- 274,280 ----
fpstate = palloc0(sizeof(PgsqlFdwPlanState));
initStringInfo(&fpstate->sql);
sql = &fpstate->sql;
+ fpstate->has_where = false;
/* Retrieve catalog objects which are necessary to estimate rows. */
table = GetForeignTable(foreigntableid);
*************** pgsqlGetForeignRelSize(PlannerInfo *root
*** 270,292 ****
user = GetUserMapping(GetOuterUserId(), server->serverid);
/*
! * Create plain SELECT statement with no WHERE clause for this scan in
! * order to obtain meaningful rows estimation by executing EXPLAIN on
! * remote server.
*/
deparseSimpleSql(sql, foreigntableid, root, baserel, table);
conn = GetConnection(server, user, false);
get_remote_estimate(sql->data, conn, &rows, &width,
&startup_cost, &total_cost);
ReleaseConnection(conn);
/*
! * Estimate selectivity of local filtering by calling
! * clauselist_selectivity() against baserestrictinfo, and modify rows
! * estimate with it.
*/
! sel = clauselist_selectivity(root, baserel->baserestrictinfo,
! baserel->relid, JOIN_INNER, NULL);
baserel->rows = rows * sel;
baserel->width = width;
--- 282,321 ----
user = GetUserMapping(GetOuterUserId(), server->serverid);
/*
! * Construct remote query which consists of SELECT, FROM, and WHERE
! * clauses, but conditions contain any Param node are excluded because
! * placeholder can't be used in EXPLAIN statement. Such conditions are
! * appended later.
*/
+ sortConditions(root, baserel, &remote_conds, ¶m_conds, &local_conds);
deparseSimpleSql(sql, foreigntableid, root, baserel, table);
+ if (list_length(remote_conds) > 0)
+ {
+ appendWhereClause(sql, fpstate->has_where, remote_conds, root);
+ fpstate->has_where = true;
+ }
conn = GetConnection(server, user, false);
get_remote_estimate(sql->data, conn, &rows, &width,
&startup_cost, &total_cost);
ReleaseConnection(conn);
+ if (list_length(param_conds) > 0)
+ {
+ appendWhereClause(sql, fpstate->has_where, param_conds, root);
+ fpstate->has_where = true;
+ }
/*
! * Estimate selectivity of conditions which are not used in remote EXPLAIN
! * by calling clauselist_selectivity(). The best we can do for
! * parameterized condition is to estimate selectivity on the basis of local
! * statistics. When we actually obtain result rows, such conditions are
! * deparsed into remote query and reduce rows transferred.
*/
! sel = 1.0;
! sel *= clauselist_selectivity(root, param_conds,
! baserel->relid, JOIN_INNER, NULL);
! sel *= clauselist_selectivity(root, local_conds,
! baserel->relid, JOIN_INNER, NULL);
baserel->rows = rows * sel;
baserel->width = width;
*************** pgsqlGetForeignRelSize(PlannerInfo *root
*** 296,301 ****
--- 325,333 ----
*/
fpstate->startup_cost = startup_cost;
fpstate->total_cost = total_cost;
+ fpstate->remote_conds = remote_conds;
+ fpstate->param_conds = param_conds;
+ fpstate->local_conds = local_conds;
fpstate->table = table;
fpstate->server = server;
baserel->fdw_private = (void *) fpstate;
*************** pgsqlGetForeignPlan(PlannerInfo *root,
*** 372,392 ****
StringInfoData cursor;
int fetch_count;
char *sql;
/*
! * We have no native ability to evaluate restriction clauses, so we just
! * put all the scan_clauses into the plan node's qual list for the
! * executor to check. So all we have to do here is strip RestrictInfo
! * nodes from the clauses and ignore pseudoconstants (which will be
! * handled elsewhere).
*/
! scan_clauses = extract_actual_clauses(scan_clauses, false);
fetch_count = GetFetchCountOption(fpstate->table, fpstate->server);
elog(DEBUG1, "relid=%u fetch_count=%d", foreigntableid, fetch_count);
! /*
! * Construct cursor name with sequential value.
! */
sprintf(name, CURSOR_NAME_FORMAT, cursor_id++);
/*
--- 404,429 ----
StringInfoData cursor;
int fetch_count;
char *sql;
+ List *fdw_exprs = NIL;
+ List *local_exprs = NIL;
+ ListCell *lc;
/*
! * We need lists of Expr other than the lists of RestrictInfo. Now we can
! * merge remote_conds and param_conds into fdw_exprs, because they are
! * evaluated on remote side for actual remote query.
*/
! foreach(lc, fpstate->remote_conds)
! fdw_exprs = lappend(fdw_exprs, ((RestrictInfo *) lfirst(lc))->clause);
! foreach(lc, fpstate->param_conds)
! fdw_exprs = lappend(fdw_exprs, ((RestrictInfo *) lfirst(lc))->clause);
! foreach(lc, fpstate->local_conds)
! local_exprs = lappend(local_exprs,
! ((RestrictInfo *) lfirst(lc))->clause);
fetch_count = GetFetchCountOption(fpstate->table, fpstate->server);
elog(DEBUG1, "relid=%u fetch_count=%d", foreigntableid, fetch_count);
! /* Construct cursor name from sequential value */
sprintf(name, CURSOR_NAME_FORMAT, cursor_id++);
/*
*************** pgsqlGetForeignPlan(PlannerInfo *root,
*** 413,423 ****
appendStringInfo(&cursor, "CLOSE %s", name);
fdw_private = lappend(fdw_private, makeString(cursor.data));
! /* Create the ForeignScan node with fdw_private of selected path. */
return make_foreignscan(tlist,
! scan_clauses,
scan_relid,
! NIL,
fdw_private);
}
--- 450,469 ----
appendStringInfo(&cursor, "CLOSE %s", name);
fdw_private = lappend(fdw_private, makeString(cursor.data));
! /*
! * Create the ForeignScan node from target list, local filtering
! * expressions, remote filtering expressions, and FDW private information.
! *
! * We remove expressions which are evaluated on remote side from qual of
! * the scan node to avoid redundant filtering. Such filter reduction
! * can be done only here, done after choosing best path, because
! * baserestrictinfo in RelOptInfo is shared by all possible paths until
! * best path is chosen.
! */
return make_foreignscan(tlist,
! local_exprs,
scan_relid,
! fdw_exprs,
fdw_private);
}
diff --git a/contrib/pgsql_fdw/pgsql_fdw.h b/contrib/pgsql_fdw/pgsql_fdw.h
index c67006b..5487d52 100644
*** a/contrib/pgsql_fdw/pgsql_fdw.h
--- b/contrib/pgsql_fdw/pgsql_fdw.h
***************
*** 15,20 ****
--- 15,21 ----
#define PGSQL_FDW_H
#include "postgres.h"
+ #include "foreign/fdwapi.h"
#include "foreign/foreign.h"
#include "nodes/relation.h"
*************** void deparseSimpleSql(StringInfo buf,
*** 30,34 ****
--- 31,44 ----
PlannerInfo *root,
RelOptInfo *baserel,
ForeignTable *table);
+ void appendWhereClause(StringInfo buf,
+ bool has_where,
+ List *exprs,
+ PlannerInfo *root);
+ void sortConditions(PlannerInfo *root,
+ RelOptInfo *baserel,
+ List **remote_conds,
+ List **param_conds,
+ List **local_conds);
#endif /* PGSQL_FDW_H */
diff --git a/contrib/pgsql_fdw/sql/pgsql_fdw.sql b/contrib/pgsql_fdw/sql/pgsql_fdw.sql
index 905cc2e..6692af7 100644
*** a/contrib/pgsql_fdw/sql/pgsql_fdw.sql
--- b/contrib/pgsql_fdw/sql/pgsql_fdw.sql
*************** SELECT * FROM ft1 ORDER BY c3, c1 OFFSET
*** 149,156 ****
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- with WHERE clause
! EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
! SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
-- aggregate
SELECT COUNT(*) FROM ft1 t1;
-- join two tables
--- 149,156 ----
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- with WHERE clause
! EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
! SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
-- aggregate
SELECT COUNT(*) FROM ft1 t1;
-- join two tables
*************** EXPLAIN (COSTS false) SELECT * FROM ft1
*** 182,187 ****
--- 182,201 ----
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
-- ===================================================================
+ -- WHERE push down
+ -- ===================================================================
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r)
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
+ EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
+ EXPLAIN (COSTS false) SELECT * FROM ft1 ft WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
+
+ -- ===================================================================
-- parameterized queries
-- ===================================================================
-- simple join