Tom Lane писал 2022-01-18 23:01:
I wrote:
Alexander Pyhalov <a.pyha...@postgrespro.ru> writes:
This means we'll translate something like
explain select * from t where d > now() - '1 day'::interval;
to
select * from t where d > $1;
Right.
After thinking about that a bit more, I see that this will result
in a major redefinition of what is "shippable". Right now, we do not
consider the above WHERE clause to be shippable, not only because of
now() but because the timestamptz-minus-interval operator is dependent
on the timezone setting, which might be different at the remote.
But if we evaluate that operator locally and send its result as a
parameter, the objection vanishes. In fact, I don't think we even
need to require the subexpression to contain only built-in functions.
Its result still has to be of a built-in type, but that's a much
weaker restriction.
Hi.
So far I have the following prototype. It seems to be working, but I
think it can be enhanced.
At least, some sort of caching seems to be necessary for
is_stable_expr().
1) Now expression can be either 'stable shippable' or 'shippable
according to old rules'. We check if it's 'stable shippable' in
foreign_expr_walker(),
is_foreign_param() and deparseExpr(). All such exprs are replaced by
params while deparsing.
2) contain_mutable_functions() now is calculated only for current node,
if node is not considered 'stable shippable'.
Is it step in the right direction or do I miss something?
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From 60e6e0bf98326cb557c70a365797026e9925b7a3 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Thu, 29 Jul 2021 11:45:28 +0300
Subject: [PATCH] Push down stable expressions
Stable expressions can be computed locally and sent to remote side as parameters values.
---
contrib/postgres_fdw/deparse.c | 1162 +++++++++--------
.../postgres_fdw/expected/postgres_fdw.out | 87 ++
contrib/postgres_fdw/postgres_fdw.c | 9 +-
contrib/postgres_fdw/postgres_fdw.h | 1 +
contrib/postgres_fdw/shippable.c | 74 ++
contrib/postgres_fdw/sql/postgres_fdw.sql | 22 +
6 files changed, 811 insertions(+), 544 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index bf12eac0288..33a79026574 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -158,6 +158,7 @@ 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 deparseStableExpr(Expr *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 deparseCaseExpr(CaseExpr *node, deparse_expr_cxt *context);
@@ -267,20 +268,17 @@ is_foreign_expr(PlannerInfo *root,
if (loc_cxt.state == FDW_COLLATE_UNSAFE)
return false;
- /*
- * An expression which includes any mutable functions can't be sent over
- * because its result is not stable. For example, sending now() remote
- * side could cause confusion from clock offsets. Future versions might
- * be able to make this choice with more granularity. (We check this last
- * because it requires a lot of expensive catalog lookups.)
- */
- if (contain_mutable_functions((Node *) expr))
- return false;
/* OK to evaluate on the remote server */
return true;
}
+static bool
+contain_mutable_functions_checker(Oid func_id, void *context)
+{
+ return (func_volatile(func_id) != PROVOLATILE_IMMUTABLE);
+}
+
/*
* Check if expression is safe to execute remotely, and return true if so.
*
@@ -321,616 +319,650 @@ foreign_expr_walker(Node *node,
inner_cxt.collation = InvalidOid;
inner_cxt.state = FDW_COLLATE_NONE;
- switch (nodeTag(node))
+ if (is_stable_expr(node))
+ {
+ collation = exprCollation(node);
+ if (collation == InvalidOid || collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ else
{
- case T_Var:
- {
- 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 (bms_is_member(var->varno, glob_cxt->relids) &&
- var->varlevelsup == 0)
+ /*
+ * An expression which includes any mutable functions can't be sent
+ * over because its result is not stable.
+ */
+ if (check_functions_in_node(node, contain_mutable_functions_checker, NULL))
+ return false;
+
+ switch (nodeTag(node))
+ {
+ case T_Var:
{
- /* Var belongs to foreign table */
+ Var *var = (Var *) node;
/*
- * 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).
+ * 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->varattno < 0 &&
- var->varattno != SelfItemPointerAttributeNumber)
- return false;
-
- /* Else check the collation */
- collation = var->varcollid;
- state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE;
- }
- else
- {
- /* Var belongs to some other table */
- collation = var->varcollid;
- if (collation == InvalidOid ||
- collation == DEFAULT_COLLATION_OID)
+ if (bms_is_member(var->varno, glob_cxt->relids) &&
+ var->varlevelsup == 0)
{
+ /* Var belongs to foreign table */
+
/*
- * It's noncollatable, or it's safe to combine with a
- * collatable foreign Var, so set state to NONE.
+ * 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).
*/
- state = FDW_COLLATE_NONE;
+ if (var->varattno < 0 &&
+ var->varattno != SelfItemPointerAttributeNumber)
+ return false;
+
+ /* Else check the collation */
+ collation = var->varcollid;
+ state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE;
}
else
{
- /*
- * Do not fail right away, since the Var might appear
- * in a collation-insensitive context.
- */
- state = FDW_COLLATE_UNSAFE;
+ /* Var belongs to some other table */
+ collation = var->varcollid;
+ if (collation == InvalidOid ||
+ collation == DEFAULT_COLLATION_OID)
+ {
+ /*
+ * It's noncollatable, or it's safe to combine
+ * with a collatable foreign Var, so set state to
+ * NONE.
+ */
+ state = FDW_COLLATE_NONE;
+ }
+ else
+ {
+ /*
+ * Do not fail right away, since the Var might
+ * appear in a collation-insensitive context.
+ */
+ state = FDW_COLLATE_UNSAFE;
+ }
}
}
- }
- break;
- case T_Const:
- {
- Const *c = (Const *) node;
+ break;
+ case T_Const:
+ {
+ Const *c = (Const *) node;
- /*
- * If the constant has nondefault collation, either it's of a
- * non-builtin type, or it reflects folding of a CollateExpr.
- * It's unsafe to send to the remote unless it's used in a
- * non-collation-sensitive context.
- */
- collation = c->constcollid;
- if (collation == InvalidOid ||
- collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_Param:
- {
- Param *p = (Param *) node;
+ /*
+ * If the constant has nondefault collation, either it's
+ * of a non-builtin type, or it reflects folding of a
+ * CollateExpr. It's unsafe to send to the remote unless
+ * it's used in a non-collation-sensitive context.
+ */
+ collation = c->constcollid;
+ if (collation == InvalidOid ||
+ collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_Param:
+ {
+ Param *p = (Param *) node;
- /*
- * If it's a MULTIEXPR Param, punt. We can't tell from here
- * whether the referenced sublink/subplan contains any remote
- * Vars; if it does, handling that is too complicated to
- * consider supporting at present. Fortunately, MULTIEXPR
- * Params are not reduced to plain PARAM_EXEC until the end of
- * planning, so we can easily detect this case. (Normal
- * PARAM_EXEC Params are safe to ship because their values
- * come from somewhere else in the plan tree; but a MULTIEXPR
- * references a sub-select elsewhere in the same targetlist,
- * so we'd be on the hook to evaluate it somehow if we wanted
- * to handle such cases as direct foreign updates.)
- */
- if (p->paramkind == PARAM_MULTIEXPR)
- return false;
+ /*
+ * If it's a MULTIEXPR Param, punt. We can't tell from
+ * here whether the referenced sublink/subplan contains
+ * any remote Vars; if it does, handling that is too
+ * complicated to consider supporting at present.
+ * Fortunately, MULTIEXPR Params are not reduced to plain
+ * PARAM_EXEC until the end of planning, so we can easily
+ * detect this case. (Normal PARAM_EXEC Params are safe
+ * to ship because their values come from somewhere else
+ * in the plan tree; but a MULTIEXPR references a
+ * sub-select elsewhere in the same targetlist, so we'd be
+ * on the hook to evaluate it somehow if we wanted to
+ * handle such cases as direct foreign updates.)
+ */
+ if (p->paramkind == PARAM_MULTIEXPR)
+ return false;
- /*
- * Collation rule is same as for Consts and non-foreign Vars.
- */
- collation = p->paramcollid;
- if (collation == InvalidOid ||
- collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_SubscriptingRef:
- {
- SubscriptingRef *sr = (SubscriptingRef *) node;
+ /*
+ * Collation rule is same as for Consts and non-foreign
+ * Vars.
+ */
+ collation = p->paramcollid;
+ if (collation == InvalidOid ||
+ collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_SubscriptingRef:
+ {
+ SubscriptingRef *sr = (SubscriptingRef *) node;
- /* Assignment should not be in restrictions. */
- if (sr->refassgnexpr != NULL)
- return false;
+ /* Assignment should not be in restrictions. */
+ if (sr->refassgnexpr != NULL)
+ return false;
- /*
- * Recurse into the remaining subexpressions. The container
- * subscripts will not affect collation of the SubscriptingRef
- * result, so do those first and reset inner_cxt afterwards.
- */
- if (!foreign_expr_walker((Node *) sr->refupperindexpr,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
- inner_cxt.collation = InvalidOid;
- inner_cxt.state = FDW_COLLATE_NONE;
- if (!foreign_expr_walker((Node *) sr->reflowerindexpr,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
- inner_cxt.collation = InvalidOid;
- inner_cxt.state = FDW_COLLATE_NONE;
- if (!foreign_expr_walker((Node *) sr->refexpr,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /*
+ * Recurse into the remaining subexpressions. The
+ * container subscripts will not affect collation of the
+ * SubscriptingRef result, so do those first and reset
+ * inner_cxt afterwards.
+ */
+ if (!foreign_expr_walker((Node *) sr->refupperindexpr,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+ inner_cxt.collation = InvalidOid;
+ inner_cxt.state = FDW_COLLATE_NONE;
+ if (!foreign_expr_walker((Node *) sr->reflowerindexpr,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+ inner_cxt.collation = InvalidOid;
+ inner_cxt.state = FDW_COLLATE_NONE;
+ if (!foreign_expr_walker((Node *) sr->refexpr,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
- /*
- * Container subscripting typically yields same collation as
- * refexpr's, but in case it doesn't, use same logic as for
- * function nodes.
- */
- collation = sr->refcollid;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (inner_cxt.state == FDW_COLLATE_SAFE &&
- collation == inner_cxt.collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_FuncExpr:
- {
- FuncExpr *fe = (FuncExpr *) node;
+ /*
+ * Container subscripting typically yields same collation
+ * as refexpr's, but in case it doesn't, use same logic as
+ * for function nodes.
+ */
+ collation = sr->refcollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_FuncExpr:
+ {
+ FuncExpr *fe = (FuncExpr *) node;
- /*
- * If function used by the expression is not shippable, it
- * can't be sent to remote because it might have incompatible
- * semantics on remote side.
- */
- if (!is_shippable(fe->funcid, ProcedureRelationId, fpinfo))
- return false;
+ /*
+ * If function used by the expression is not shippable, it
+ * can't be sent to remote because it might have
+ * incompatible semantics on remote side.
+ */
+ if (!is_shippable(fe->funcid, ProcedureRelationId, fpinfo))
+ return false;
- /*
- * Recurse to input subexpressions.
- */
- if (!foreign_expr_walker((Node *) fe->args,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /*
+ * Recurse to input subexpressions.
+ */
+ if (!foreign_expr_walker((Node *) fe->args,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
- /*
- * If function's input collation is not derived from a foreign
- * Var, it can't be sent to remote.
- */
- if (fe->inputcollid == InvalidOid)
- /* OK, inputs are all noncollatable */ ;
- else if (inner_cxt.state != FDW_COLLATE_SAFE ||
- fe->inputcollid != inner_cxt.collation)
- return false;
+ /*
+ * If function's input collation is not derived from a
+ * foreign Var, it can't be sent to remote.
+ */
+ if (fe->inputcollid == InvalidOid)
+ /* OK, inputs are all noncollatable */ ;
+ else if (inner_cxt.state != FDW_COLLATE_SAFE ||
+ fe->inputcollid != inner_cxt.collation)
+ return false;
- /*
- * Detect whether node is introducing a collation not derived
- * from a foreign Var. (If so, we just mark it unsafe for now
- * rather than immediately returning false, since the parent
- * node might not care.)
- */
- collation = fe->funccollid;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (inner_cxt.state == FDW_COLLATE_SAFE &&
- collation == inner_cxt.collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_OpExpr:
- case T_DistinctExpr: /* struct-equivalent to OpExpr */
- {
- OpExpr *oe = (OpExpr *) node;
+ /*
+ * Detect whether node is introducing a collation not
+ * derived from a foreign Var. (If so, we just mark it
+ * unsafe for now rather than immediately returning false,
+ * since the parent node might not care.)
+ */
+ collation = fe->funccollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_OpExpr:
+ case T_DistinctExpr: /* struct-equivalent to OpExpr */
+ {
+ OpExpr *oe = (OpExpr *) node;
- /*
- * Similarly, only shippable operators can be sent to remote.
- * (If the operator is shippable, we assume its underlying
- * function is too.)
- */
- if (!is_shippable(oe->opno, OperatorRelationId, fpinfo))
- return false;
+ /*
+ * Similarly, only shippable operators can be sent to
+ * remote. (If the operator is shippable, we assume its
+ * underlying function is too.)
+ */
+ if (!is_shippable(oe->opno, OperatorRelationId, fpinfo))
+ return false;
- /*
- * Recurse to input subexpressions.
- */
- if (!foreign_expr_walker((Node *) oe->args,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /*
+ * Recurse to input subexpressions.
+ */
+ if (!foreign_expr_walker((Node *) oe->args,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
- /*
- * If operator's input collation is not derived from a foreign
- * Var, it can't be sent to remote.
- */
- if (oe->inputcollid == InvalidOid)
- /* OK, inputs are all noncollatable */ ;
- else if (inner_cxt.state != FDW_COLLATE_SAFE ||
- oe->inputcollid != inner_cxt.collation)
- return false;
-
- /* Result-collation handling is same as for functions */
- collation = oe->opcollid;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (inner_cxt.state == FDW_COLLATE_SAFE &&
- collation == inner_cxt.collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_ScalarArrayOpExpr:
- {
- ScalarArrayOpExpr *oe = (ScalarArrayOpExpr *) node;
+ /*
+ * If operator's input collation is not derived from a
+ * foreign Var, it can't be sent to remote.
+ */
+ if (oe->inputcollid == InvalidOid)
+ /* OK, inputs are all noncollatable */ ;
+ else if (inner_cxt.state != FDW_COLLATE_SAFE ||
+ oe->inputcollid != inner_cxt.collation)
+ return false;
- /*
- * Again, only shippable operators can be sent to remote.
- */
- if (!is_shippable(oe->opno, OperatorRelationId, fpinfo))
- return false;
+ /* Result-collation handling is same as for functions */
+ collation = oe->opcollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_ScalarArrayOpExpr:
+ {
+ ScalarArrayOpExpr *oe = (ScalarArrayOpExpr *) node;
- /*
- * Recurse to input subexpressions.
- */
- if (!foreign_expr_walker((Node *) oe->args,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /*
+ * Again, only shippable operators can be sent to remote.
+ */
+ if (!is_shippable(oe->opno, OperatorRelationId, fpinfo))
+ return false;
- /*
- * If operator's input collation is not derived from a foreign
- * Var, it can't be sent to remote.
- */
- if (oe->inputcollid == InvalidOid)
- /* OK, inputs are all noncollatable */ ;
- else if (inner_cxt.state != FDW_COLLATE_SAFE ||
- oe->inputcollid != inner_cxt.collation)
- return false;
-
- /* Output is always boolean and so noncollatable. */
- collation = InvalidOid;
- state = FDW_COLLATE_NONE;
- }
- break;
- case T_RelabelType:
- {
- RelabelType *r = (RelabelType *) node;
+ /*
+ * Recurse to input subexpressions.
+ */
+ if (!foreign_expr_walker((Node *) oe->args,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
- /*
- * Recurse to input subexpression.
- */
- if (!foreign_expr_walker((Node *) r->arg,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /*
+ * If operator's input collation is not derived from a
+ * foreign Var, it can't be sent to remote.
+ */
+ if (oe->inputcollid == InvalidOid)
+ /* OK, inputs are all noncollatable */ ;
+ else if (inner_cxt.state != FDW_COLLATE_SAFE ||
+ oe->inputcollid != inner_cxt.collation)
+ return false;
- /*
- * RelabelType must not introduce a collation not derived from
- * an input foreign Var (same logic as for a real function).
- */
- collation = r->resultcollid;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (inner_cxt.state == FDW_COLLATE_SAFE &&
- collation == inner_cxt.collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
+ /* Output is always boolean and so noncollatable. */
+ collation = InvalidOid;
state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_BoolExpr:
- {
- BoolExpr *b = (BoolExpr *) node;
-
- /*
- * Recurse to input subexpressions.
- */
- if (!foreign_expr_walker((Node *) b->args,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ }
+ break;
+ case T_RelabelType:
+ {
+ RelabelType *r = (RelabelType *) node;
- /* Output is always boolean and so noncollatable. */
- collation = InvalidOid;
- state = FDW_COLLATE_NONE;
- }
- break;
- case T_NullTest:
- {
- NullTest *nt = (NullTest *) node;
+ /*
+ * Recurse to input subexpression.
+ */
+ if (!foreign_expr_walker((Node *) r->arg,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
- /*
- * Recurse to input subexpressions.
- */
- if (!foreign_expr_walker((Node *) nt->arg,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /*
+ * RelabelType must not introduce a collation not derived
+ * from an input foreign Var (same logic as for a real
+ * function).
+ */
+ collation = r->resultcollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_BoolExpr:
+ {
+ BoolExpr *b = (BoolExpr *) node;
- /* Output is always boolean and so noncollatable. */
- collation = InvalidOid;
- state = FDW_COLLATE_NONE;
- }
- break;
- case T_CaseExpr:
- {
- CaseExpr *ce = (CaseExpr *) node;
- foreign_loc_cxt arg_cxt;
- foreign_loc_cxt tmp_cxt;
- ListCell *lc;
+ /*
+ * Recurse to input subexpressions.
+ */
+ if (!foreign_expr_walker((Node *) b->args,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
- /*
- * Recurse to CASE's arg expression, if any. Its collation
- * has to be saved aside for use while examining CaseTestExprs
- * within the WHEN expressions.
- */
- arg_cxt.collation = InvalidOid;
- arg_cxt.state = FDW_COLLATE_NONE;
- if (ce->arg)
+ /* Output is always boolean and so noncollatable. */
+ collation = InvalidOid;
+ state = FDW_COLLATE_NONE;
+ }
+ break;
+ case T_NullTest:
{
- if (!foreign_expr_walker((Node *) ce->arg,
- glob_cxt, &arg_cxt, case_arg_cxt))
+ NullTest *nt = (NullTest *) node;
+
+ /*
+ * Recurse to input subexpressions.
+ */
+ if (!foreign_expr_walker((Node *) nt->arg,
+ glob_cxt, &inner_cxt, case_arg_cxt))
return false;
- }
- /* Examine the CaseWhen subexpressions. */
- foreach(lc, ce->args)
+ /* Output is always boolean and so noncollatable. */
+ collation = InvalidOid;
+ state = FDW_COLLATE_NONE;
+ }
+ break;
+ case T_CaseExpr:
{
- CaseWhen *cw = lfirst_node(CaseWhen, lc);
+ CaseExpr *ce = (CaseExpr *) node;
+ foreign_loc_cxt arg_cxt;
+ foreign_loc_cxt tmp_cxt;
+ ListCell *lc;
+ /*
+ * Recurse to CASE's arg expression, if any. Its
+ * collation has to be saved aside for use while examining
+ * CaseTestExprs within the WHEN expressions.
+ */
+ arg_cxt.collation = InvalidOid;
+ arg_cxt.state = FDW_COLLATE_NONE;
if (ce->arg)
{
+ if (!foreign_expr_walker((Node *) ce->arg,
+ glob_cxt, &arg_cxt, case_arg_cxt))
+ return false;
+ }
+
+ /* Examine the CaseWhen subexpressions. */
+ foreach(lc, ce->args)
+ {
+ CaseWhen *cw = lfirst_node(CaseWhen, lc);
+
+ if (ce->arg)
+ {
+ /*
+ * In a CASE-with-arg, the parser should have
+ * produced WHEN clauses of the form "CaseTestExpr
+ * = RHS", possibly with an implicit coercion
+ * inserted above the CaseTestExpr. However in an
+ * expression that's been through the optimizer,
+ * the WHEN clause could be almost anything (since
+ * the equality operator could have been expanded
+ * into an inline function). In such cases forbid
+ * pushdown, because deparseCaseExpr can't handle
+ * it.
+ */
+ Node *whenExpr = (Node *) cw->expr;
+ List *opArgs;
+
+ if (!IsA(whenExpr, OpExpr))
+ return false;
+
+ opArgs = ((OpExpr *) whenExpr)->args;
+ if (list_length(opArgs) != 2 ||
+ !IsA(strip_implicit_coercions(linitial(opArgs)),
+ CaseTestExpr))
+ return false;
+ }
+
/*
- * In a CASE-with-arg, the parser should have produced
- * WHEN clauses of the form "CaseTestExpr = RHS",
- * possibly with an implicit coercion inserted above
- * the CaseTestExpr. However in an expression that's
- * been through the optimizer, the WHEN clause could
- * be almost anything (since the equality operator
- * could have been expanded into an inline function).
- * In such cases forbid pushdown, because
- * deparseCaseExpr can't handle it.
+ * Recurse to WHEN expression, passing down the arg
+ * info. Its collation doesn't affect the result
+ * (really, it should be boolean and thus not have a
+ * collation).
*/
- Node *whenExpr = (Node *) cw->expr;
- List *opArgs;
-
- if (!IsA(whenExpr, OpExpr))
+ tmp_cxt.collation = InvalidOid;
+ tmp_cxt.state = FDW_COLLATE_NONE;
+ if (!foreign_expr_walker((Node *) cw->expr,
+ glob_cxt, &tmp_cxt, &arg_cxt))
return false;
- opArgs = ((OpExpr *) whenExpr)->args;
- if (list_length(opArgs) != 2 ||
- !IsA(strip_implicit_coercions(linitial(opArgs)),
- CaseTestExpr))
+ /* Recurse to THEN expression. */
+ if (!foreign_expr_walker((Node *) cw->result,
+ glob_cxt, &inner_cxt, case_arg_cxt))
return false;
}
- /*
- * Recurse to WHEN expression, passing down the arg info.
- * Its collation doesn't affect the result (really, it
- * should be boolean and thus not have a collation).
- */
- tmp_cxt.collation = InvalidOid;
- tmp_cxt.state = FDW_COLLATE_NONE;
- if (!foreign_expr_walker((Node *) cw->expr,
- glob_cxt, &tmp_cxt, &arg_cxt))
- return false;
-
- /* Recurse to THEN expression. */
- if (!foreign_expr_walker((Node *) cw->result,
+ /* Recurse to ELSE expression. */
+ if (!foreign_expr_walker((Node *) ce->defresult,
glob_cxt, &inner_cxt, case_arg_cxt))
return false;
+
+ /*
+ * Detect whether node is introducing a collation not
+ * derived from a foreign Var. (If so, we just mark it
+ * unsafe for now rather than immediately returning false,
+ * since the parent node might not care.) This is the
+ * same as for function nodes, except that the input
+ * collation is derived from only the THEN and ELSE
+ * subexpressions.
+ */
+ collation = ce->casecollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
}
+ break;
+ case T_CaseTestExpr:
+ {
+ CaseTestExpr *c = (CaseTestExpr *) node;
- /* Recurse to ELSE expression. */
- if (!foreign_expr_walker((Node *) ce->defresult,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /* Punt if we seem not to be inside a CASE arg WHEN. */
+ if (!case_arg_cxt)
+ return false;
- /*
- * Detect whether node is introducing a collation not derived
- * from a foreign Var. (If so, we just mark it unsafe for now
- * rather than immediately returning false, since the parent
- * node might not care.) This is the same as for function
- * nodes, except that the input collation is derived from only
- * the THEN and ELSE subexpressions.
- */
- collation = ce->casecollid;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (inner_cxt.state == FDW_COLLATE_SAFE &&
- collation == inner_cxt.collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_CaseTestExpr:
- {
- CaseTestExpr *c = (CaseTestExpr *) node;
+ /*
+ * Otherwise, any nondefault collation attached to the
+ * CaseTestExpr node must be derived from foreign Var(s)
+ * in the CASE arg.
+ */
+ collation = c->collation;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (case_arg_cxt->state == FDW_COLLATE_SAFE &&
+ collation == case_arg_cxt->collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_ArrayExpr:
+ {
+ ArrayExpr *a = (ArrayExpr *) node;
- /* Punt if we seem not to be inside a CASE arg WHEN. */
- if (!case_arg_cxt)
- return false;
+ /*
+ * Recurse to input subexpressions.
+ */
+ if (!foreign_expr_walker((Node *) a->elements,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
- /*
- * Otherwise, any nondefault collation attached to the
- * CaseTestExpr node must be derived from foreign Var(s) in
- * the CASE arg.
- */
- collation = c->collation;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (case_arg_cxt->state == FDW_COLLATE_SAFE &&
- collation == case_arg_cxt->collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_ArrayExpr:
- {
- ArrayExpr *a = (ArrayExpr *) node;
+ /*
+ * ArrayExpr must not introduce a collation not derived
+ * from an input foreign Var (same logic as for a
+ * function).
+ */
+ collation = a->array_collid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
+ case T_List:
+ {
+ List *l = (List *) node;
+ ListCell *lc;
- /*
- * Recurse to input subexpressions.
- */
- if (!foreign_expr_walker((Node *) a->elements,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
+ /*
+ * Recurse to component subexpressions.
+ */
+ foreach(lc, l)
+ {
+ if (!foreign_expr_walker((Node *) lfirst(lc),
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+ }
- /*
- * ArrayExpr must not introduce a collation not derived from
- * an input foreign Var (same logic as for a function).
- */
- collation = a->array_collid;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (inner_cxt.state == FDW_COLLATE_SAFE &&
- collation == inner_cxt.collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- case T_List:
- {
- List *l = (List *) node;
- ListCell *lc;
+ /*
+ * When processing a list, collation state just bubbles up
+ * from the list elements.
+ */
+ collation = inner_cxt.collation;
+ state = inner_cxt.state;
- /*
- * Recurse to component subexpressions.
- */
- foreach(lc, l)
+ /* Don't apply exprType() to the list. */
+ check_type = false;
+ }
+ break;
+ case T_Aggref:
{
- if (!foreign_expr_walker((Node *) lfirst(lc),
- glob_cxt, &inner_cxt, case_arg_cxt))
+ Aggref *agg = (Aggref *) node;
+ ListCell *lc;
+
+ /* Not safe to pushdown when not in grouping context */
+ if (!IS_UPPER_REL(glob_cxt->foreignrel))
return false;
- }
- /*
- * When processing a list, collation state just bubbles up
- * from the list elements.
- */
- collation = inner_cxt.collation;
- state = inner_cxt.state;
+ /* Only non-split aggregates are pushable. */
+ if (agg->aggsplit != AGGSPLIT_SIMPLE)
+ return false;
- /* Don't apply exprType() to the list. */
- check_type = false;
- }
- break;
- case T_Aggref:
- {
- Aggref *agg = (Aggref *) node;
- ListCell *lc;
+ /* As usual, it must be shippable. */
+ if (!is_shippable(agg->aggfnoid, ProcedureRelationId, fpinfo))
+ return false;
- /* Not safe to pushdown when not in grouping context */
- if (!IS_UPPER_REL(glob_cxt->foreignrel))
- return false;
+ /*
+ * Recurse to input args. aggdirectargs, aggorder and
+ * aggdistinct are all present in args, so no need to
+ * check their shippability explicitly.
+ */
+ foreach(lc, agg->args)
+ {
+ Node *n = (Node *) lfirst(lc);
- /* Only non-split aggregates are pushable. */
- if (agg->aggsplit != AGGSPLIT_SIMPLE)
- return false;
+ /* If TargetEntry, extract the expression from it */
+ if (IsA(n, TargetEntry))
+ {
+ TargetEntry *tle = (TargetEntry *) n;
- /* As usual, it must be shippable. */
- if (!is_shippable(agg->aggfnoid, ProcedureRelationId, fpinfo))
- return false;
+ n = (Node *) tle->expr;
+ }
- /*
- * Recurse to input args. aggdirectargs, aggorder and
- * aggdistinct are all present in args, so no need to check
- * their shippability explicitly.
- */
- foreach(lc, agg->args)
- {
- Node *n = (Node *) lfirst(lc);
+ if (!foreign_expr_walker(n,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+ }
- /* If TargetEntry, extract the expression from it */
- if (IsA(n, TargetEntry))
+ /*
+ * For aggorder elements, check whether the sort operator,
+ * if specified, is shippable or not.
+ */
+ if (agg->aggorder)
{
- TargetEntry *tle = (TargetEntry *) n;
-
- n = (Node *) tle->expr;
+ ListCell *lc;
+
+ foreach(lc, agg->aggorder)
+ {
+ SortGroupClause *srt = (SortGroupClause *) lfirst(lc);
+ Oid sortcoltype;
+ TypeCacheEntry *typentry;
+ TargetEntry *tle;
+
+ tle = get_sortgroupref_tle(srt->tleSortGroupRef,
+ agg->args);
+ sortcoltype = exprType((Node *) tle->expr);
+ typentry = lookup_type_cache(sortcoltype,
+ TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
+
+ /*
+ * Check shippability of non-default sort
+ * operator.
+ */
+ if (srt->sortop != typentry->lt_opr &&
+ srt->sortop != typentry->gt_opr &&
+ !is_shippable(srt->sortop, OperatorRelationId,
+ fpinfo))
+ return false;
+ }
}
- if (!foreign_expr_walker(n,
+ /* Check aggregate filter */
+ if (!foreign_expr_walker((Node *) agg->aggfilter,
glob_cxt, &inner_cxt, case_arg_cxt))
return false;
- }
- /*
- * For aggorder elements, check whether the sort operator, if
- * specified, is shippable or not.
- */
- if (agg->aggorder)
- {
- ListCell *lc;
+ /*
+ * If aggregate's input collation is not derived from a
+ * foreign Var, it can't be sent to remote.
+ */
+ if (agg->inputcollid == InvalidOid)
+ /* OK, inputs are all noncollatable */ ;
+ else if (inner_cxt.state != FDW_COLLATE_SAFE ||
+ agg->inputcollid != inner_cxt.collation)
+ return false;
- foreach(lc, agg->aggorder)
- {
- SortGroupClause *srt = (SortGroupClause *) lfirst(lc);
- Oid sortcoltype;
- TypeCacheEntry *typentry;
- TargetEntry *tle;
-
- tle = get_sortgroupref_tle(srt->tleSortGroupRef,
- agg->args);
- sortcoltype = exprType((Node *) tle->expr);
- typentry = lookup_type_cache(sortcoltype,
- TYPECACHE_LT_OPR | TYPECACHE_GT_OPR);
- /* Check shippability of non-default sort operator. */
- if (srt->sortop != typentry->lt_opr &&
- srt->sortop != typentry->gt_opr &&
- !is_shippable(srt->sortop, OperatorRelationId,
- fpinfo))
- return false;
- }
+ /*
+ * Detect whether node is introducing a collation not
+ * derived from a foreign Var. (If so, we just mark it
+ * unsafe for now rather than immediately returning false,
+ * since the parent node might not care.)
+ */
+ collation = agg->aggcollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
}
-
- /* Check aggregate filter */
- if (!foreign_expr_walker((Node *) agg->aggfilter,
- glob_cxt, &inner_cxt, case_arg_cxt))
- return false;
-
- /*
- * If aggregate's input collation is not derived from a
- * foreign Var, it can't be sent to remote.
- */
- if (agg->inputcollid == InvalidOid)
- /* OK, inputs are all noncollatable */ ;
- else if (inner_cxt.state != FDW_COLLATE_SAFE ||
- agg->inputcollid != inner_cxt.collation)
- return false;
+ break;
+ default:
/*
- * Detect whether node is introducing a collation not derived
- * from a foreign Var. (If so, we just mark it unsafe for now
- * rather than immediately returning false, since the parent
- * node might not care.)
+ * If it's anything else, assume it's unsafe. This list can
+ * be expanded later, but don't forget to add deparse support
+ * below.
*/
- collation = agg->aggcollid;
- if (collation == InvalidOid)
- state = FDW_COLLATE_NONE;
- else if (inner_cxt.state == FDW_COLLATE_SAFE &&
- collation == inner_cxt.collation)
- state = FDW_COLLATE_SAFE;
- else if (collation == DEFAULT_COLLATION_OID)
- state = FDW_COLLATE_NONE;
- else
- state = FDW_COLLATE_UNSAFE;
- }
- break;
- default:
-
- /*
- * If it's anything else, assume it's unsafe. This list can be
- * expanded later, but don't forget to add deparse support below.
- */
- return false;
+ return false;
+ }
}
/*
@@ -1009,6 +1041,15 @@ is_foreign_param(PlannerInfo *root,
if (expr == NULL)
return false;
+ if (is_stable_expr((Node *) expr))
+ {
+ /*
+ * We can get here only if is_foreign_expr(expr) returned true, so
+ * it's a supported stable expression.
+ */
+ return true;
+ }
+
switch (nodeTag(expr))
{
case T_Var:
@@ -2575,6 +2616,12 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
if (node == NULL)
return;
+ if (is_stable_expr((Node *) node))
+ {
+ deparseStableExpr(node, context);
+ return;
+ }
+
switch (nodeTag(node))
{
case T_Var:
@@ -3192,6 +3239,45 @@ deparseRelabelType(RelabelType *node, deparse_expr_cxt *context)
node->resulttypmod));
}
+/*
+ * Deparse a stable expression as param
+ */
+static void
+deparseStableExpr(Expr *node, deparse_expr_cxt *context)
+{
+ int32 type = exprType((Node *) node);
+ int32 typmod = exprTypmod((Node *) node);
+
+ Assert(type != InvalidOid);
+
+ /* Treat like a Param */
+ if (context->params_list)
+ {
+ 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, type, typmod, context);
+ }
+ else
+ {
+ printRemotePlaceholder(type, typmod, context);
+ }
+}
+
/*
* Deparse a BoolExpr node.
*/
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7d6f7d9e3df..5defc2d3758 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1073,6 +1073,93 @@ SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
(1 row)
+-- Test stable expressions pushdown
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c4
+ Remote SQL: SELECT "C 1", c2, c4 FROM "S 1"."T 1" WHERE ((c4 = $1::timestamp with time zone))
+(3 rows)
+
+SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp;
+ c1 | c2 | c4
+----+----+----
+(0 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c5
+ Remote SQL: SELECT "C 1", c2, c5 FROM "S 1"."T 1" WHERE (("C 1" > 990)) AND ((c5 > $1::timestamp without time zone)) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
+SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+ c1 | c2 | c5
+------+----+--------------------------
+ 991 | 1 | Thu Apr 02 00:00:00 1970
+ 992 | 2 | Fri Apr 03 00:00:00 1970
+ 993 | 3 | Sat Apr 04 00:00:00 1970
+ 994 | 4 | Sun Apr 05 00:00:00 1970
+ 995 | 5 | Mon Apr 06 00:00:00 1970
+ 996 | 6 | Tue Apr 07 00:00:00 1970
+ 997 | 7 | Wed Apr 08 00:00:00 1970
+ 998 | 8 | Thu Apr 09 00:00:00 1970
+ 999 | 9 | Fri Apr 10 00:00:00 1970
+ 1000 | 0 | Thu Jan 01 00:00:00 1970
+(10 rows)
+
+-- also shippable as whole expression is executed locally
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c4
+ Remote SQL: SELECT "C 1", c2, c4 FROM "S 1"."T 1" WHERE (("C 1" > 990)) AND ((c4 > $1::timestamp with time zone)) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+ c1 | c2 | c4
+------+----+------------------------------
+ 991 | 1 | Thu Apr 02 00:00:00 1970 PST
+ 992 | 2 | Fri Apr 03 00:00:00 1970 PST
+ 993 | 3 | Sat Apr 04 00:00:00 1970 PST
+ 994 | 4 | Sun Apr 05 00:00:00 1970 PST
+ 995 | 5 | Mon Apr 06 00:00:00 1970 PST
+ 996 | 6 | Tue Apr 07 00:00:00 1970 PST
+ 997 | 7 | Wed Apr 08 00:00:00 1970 PST
+ 998 | 8 | Thu Apr 09 00:00:00 1970 PST
+ 999 | 9 | Fri Apr 10 00:00:00 1970 PST
+ 1000 | 0 | Thu Jan 01 00:00:00 1970 PST
+(10 rows)
+
+-- direct modify
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 SET c4 = current_timestamp WHERE c4 < current_timestamp;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c4 = $1::timestamp with time zone WHERE ((c4 < $1::timestamp with time zone))
+(3 rows)
+
+-- check that we don't try to push down parameter in group by
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1), current_timestamp FROM ft2 WHERE c1 > 990 GROUP BY current_timestamp;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ GroupAggregate
+ Output: sum(c1), (CURRENT_TIMESTAMP)
+ Group Key: CURRENT_TIMESTAMP
+ -> Foreign Scan on public.ft2
+ Output: CURRENT_TIMESTAMP, c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE (("C 1" > 990))
+(6 rows)
+
-- Test CASE pushdown
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c1 > 990 THEN c1 END < 1000 ORDER BY c1;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 09a3f5e23cb..6aa3a20f20c 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -4834,12 +4834,9 @@ prepare_query_params(PlanState *node,
}
/*
- * Prepare remote-parameter expressions for evaluation. (Note: in
- * practice, we expect that all these expressions will be just Params, so
- * we could possibly do something more efficient than using the full
- * expression-eval machinery for this. But probably there would be little
- * benefit, and it'd require postgres_fdw to know more than is desirable
- * about Param evaluation.)
+ * Prepare remote-parameter expressions for evaluation. (Note: we cannot
+ * expect that all these expressions will be just Params, so we should use
+ * the full expression-eval machinery for this).
*/
*param_exprs = ExecInitExprList(fdw_exprs, node);
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 8ae79e97e44..acb36394fc6 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -231,5 +231,6 @@ extern const char *get_jointype_name(JoinType jointype);
/* in shippable.c */
extern bool is_builtin(Oid objectId);
extern bool is_shippable(Oid objectId, Oid classId, PgFdwRelationInfo *fpinfo);
+extern bool is_stable_expr(Node *node);
#endif /* POSTGRES_FDW_H */
diff --git a/contrib/postgres_fdw/shippable.c b/contrib/postgres_fdw/shippable.c
index 8e759da00d5..5173d750035 100644
--- a/contrib/postgres_fdw/shippable.c
+++ b/contrib/postgres_fdw/shippable.c
@@ -25,9 +25,14 @@
#include "access/transam.h"
#include "catalog/dependency.h"
+#include "catalog/pg_proc.h"
+#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
+#include "optimizer/optimizer.h"
#include "postgres_fdw.h"
#include "utils/hsearch.h"
#include "utils/inval.h"
+#include "utils/lsyscache.h"
#include "utils/syscache.h"
/* Hash table for caching the results of shippability lookups */
@@ -52,6 +57,7 @@ typedef struct
bool shippable;
} ShippableCacheEntry;
+static bool contain_params_walker(Node *node, bool *context);
/*
* Flush cache entries when pg_foreign_server is updated.
@@ -209,3 +215,71 @@ is_shippable(Oid objectId, Oid classId, PgFdwRelationInfo *fpinfo)
return entry->shippable;
}
+
+/*
+ * contain_params
+ * Does this node has params?
+ */
+static bool
+contain_params(Node *node)
+{
+ bool contains = false;
+
+ contain_params_walker(node, (void *) &contains);
+
+ return contains;
+}
+
+static bool
+contain_params_walker(Node *node, bool *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Param))
+ {
+ *context = true;
+
+ return false;
+ }
+ return expression_tree_walker(node, contain_params_walker,
+ (void *) context);
+}
+
+/*
+ * Check if expression is stable
+ */
+bool
+is_stable_expr(Node *node)
+{
+ if (node == NULL)
+ return false;
+
+ /* No need to turn on 'ship stable expression' machinery in these cases */
+ if (IsA(node, Const) || IsA(node, List))
+ return false;
+
+ /* Expression shouldn't reference any table */
+ if (contain_var_clause(node))
+ return false;
+
+ if (contain_volatile_functions(node))
+ return false;
+
+ if (contain_subplans(node))
+ return false;
+
+ if (contain_params(node))
+ return false;
+
+ if (contain_agg_clause(node))
+ return false;
+
+ if (contain_mutable_functions(node))
+ {
+ /* These are not volatile functions, so they are stable */
+ if (exprType(node) != InvalidOid)
+ return true;
+ }
+
+ return false;
+}
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 9eb673e3693..de084bc4ece 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -413,6 +413,28 @@ EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
+-- Test stable expressions pushdown
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp;
+SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+
+-- also shippable as whole expression is executed locally
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+
+-- direct modify
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 SET c4 = current_timestamp WHERE c4 < current_timestamp;
+
+-- check that we don't try to push down parameter in group by
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(c1), current_timestamp FROM ft2 WHERE c1 > 990 GROUP BY current_timestamp;
+
-- Test CASE pushdown
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c1 > 990 THEN c1 END < 1000 ORDER BY c1;
--
2.25.1
