On 2016/11/16 16:38, Etsuro Fujita wrote:
On 2016/11/16 13:10, Ashutosh Bapat wrote:
I don't see any reason why DML/UPDATE pushdown should depend upon
subquery deparsing or least PHV patch. Combined together they can help
in more cases, but without those patches, we will be able to push-down
more stuff. Probably, we should just restrict push-down only for the
cases when above patches are not needed. That makes reviews easy. Once
those patches get committed, we may add more functionality depending
upon the status of this patch. Does that make sense?
OK, I'll extract from the patch the minimal part that wouldn't depend on
the two patches.
Here is a patch for that. Todo items are: (1) add more comments and (2)
add more regression tests. I'll do that in the next version.
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 130,142 **** static void deparseTargetList(StringInfo buf,
Bitmapset *attrs_used,
bool qualify_col,
List **retrieved_attrs);
! static void deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseReturningList(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
bool trig_after_row,
List *returningList,
List **retrieved_attrs);
static void deparseColumnRef(StringInfo buf, int varno, int varattno,
PlannerInfo *root, bool qualify_col);
static void deparseRelation(StringInfo buf, Relation rel);
--- 130,154 ----
Bitmapset *attrs_used,
bool qualify_col,
List **retrieved_attrs);
! static void deparseExplicitTargetList(bool is_returning,
! List *tlist,
! List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseReturningList(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
bool trig_after_row,
List *returningList,
List **retrieved_attrs);
+ static void deparseExplicitReturningList(List *rlist,
+ List **retrieved_attrs,
+ deparse_expr_cxt *context);
+ static List *make_explicit_returning_list(Index rtindex, Relation rel,
+ List *returningList,
+ List **fdw_scan_tlist);
+ static void pull_up_target_conditions(PlannerInfo *root, RelOptInfo *foreignrel,
+ Index target_rel, List **target_conds);
+ static void extract_target_conditions(List **joinclauses, Index target_rel,
+ List **target_conds);
static void deparseColumnRef(StringInfo buf, int varno, int varattno,
PlannerInfo *root, bool qualify_col);
static void deparseRelation(StringInfo buf, Relation rel);
***************
*** 164,171 **** static void deparseSelectSql(List *tlist, List **retrieved_attrs,
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
! static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
! RelOptInfo *joinrel, bool use_alias, List **params_list);
static void deparseFromExpr(List *quals, deparse_expr_cxt *context);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
--- 176,183 ----
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
! static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, Index target_rel, List **params_list);
static void deparseFromExpr(List *quals, deparse_expr_cxt *context);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
***************
*** 994,1000 **** deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
foreignrel->reloptkind == RELOPT_UPPER_REL)
{
/* For a join relation use the input tlist */
! deparseExplicitTargetList(tlist, retrieved_attrs, context);
}
else
{
--- 1006,1012 ----
foreignrel->reloptkind == RELOPT_UPPER_REL)
{
/* For a join relation use the input tlist */
! deparseExplicitTargetList(false, tlist, retrieved_attrs, context);
}
else
{
***************
*** 1037,1043 **** deparseFromExpr(List *quals, deparse_expr_cxt *context)
appendStringInfoString(buf, " FROM ");
deparseFromExprForRel(buf, context->root, scanrel,
(bms_num_members(scanrel->relids) > 1),
! context->params_list);
/* Construct WHERE clause */
if (quals != NIL)
--- 1049,1055 ----
appendStringInfoString(buf, " FROM ");
deparseFromExprForRel(buf, context->root, scanrel,
(bms_num_members(scanrel->relids) > 1),
! (Index) 0, context->params_list);
/* Construct WHERE clause */
if (quals != NIL)
***************
*** 1304,1310 **** get_jointype_name(JoinType jointype)
* from 1. It has same number of entries as tlist.
*/
static void
! deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
deparse_expr_cxt *context)
{
ListCell *lc;
--- 1316,1324 ----
* from 1. It has same number of entries as tlist.
*/
static void
! deparseExplicitTargetList(bool is_returning,
! List *tlist,
! List **retrieved_attrs,
deparse_expr_cxt *context)
{
ListCell *lc;
***************
*** 1322,1334 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
if (i > 0)
appendStringInfoString(buf, ", ");
deparseExpr((Expr *) tle->expr, context);
*retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
i++;
}
! if (i == 0)
appendStringInfoString(buf, "NULL");
}
--- 1336,1351 ----
if (i > 0)
appendStringInfoString(buf, ", ");
+ else if (is_returning)
+ appendStringInfoString(buf, " RETURNING ");
+
deparseExpr((Expr *) tle->expr, context);
*retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
i++;
}
! if (i == 0 && !is_returning)
appendStringInfoString(buf, "NULL");
}
***************
*** 1341,1347 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
*/
static void
deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
--- 1358,1364 ----
*/
static void
deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, Index target_rel, List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
***************
*** 1351,1364 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
RelOptInfo *rel_i = fpinfo->innerrel;
StringInfoData join_sql_o;
StringInfoData join_sql_i;
! /* Deparse outer relation */
! initStringInfo(&join_sql_o);
! deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list);
! /* Deparse inner relation */
! initStringInfo(&join_sql_i);
! deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list);
/*
* For a join relation FROM clause entry is deparsed as
--- 1368,1419 ----
RelOptInfo *rel_i = fpinfo->innerrel;
StringInfoData join_sql_o;
StringInfoData join_sql_i;
+ bool outer_is_targetrel = false;
+ bool inner_is_targetrel = false;
! /* Check to see if either input relation is the target relation. */
! if (target_rel > 0 && bms_is_member(target_rel, foreignrel->relids))
! {
! if (fpinfo->outerrel->reloptkind == RELOPT_BASEREL &&
! fpinfo->outerrel->relid == target_rel)
! outer_is_targetrel = true;
! if (fpinfo->innerrel->reloptkind == RELOPT_BASEREL &&
! fpinfo->innerrel->relid == target_rel)
! inner_is_targetrel = true;
! }
!
! /* Deparse outer relation if not the target relation. */
! if (!outer_is_targetrel)
! {
! initStringInfo(&join_sql_o);
! deparseFromExprForRel(&join_sql_o, root, rel_o, true, target_rel,
! params_list);
!
! /* If inner relation is the target relation, we are done. */
! if (inner_is_targetrel)
! {
! appendStringInfo(buf, "%s", join_sql_o.data);
! return;
! }
! }
!
! /* Deparse inner relation if not the target relation. */
! if (!inner_is_targetrel)
! {
! initStringInfo(&join_sql_i);
! deparseFromExprForRel(&join_sql_i, root, rel_i, true, target_rel,
! params_list);
!
! /* If outer relation is the target relation, we are done. */
! if (outer_is_targetrel)
! {
! appendStringInfo(buf, "%s", join_sql_i.data);
! return;
! }
! }
! /* Neither of the relations is the target relation. */
! Assert(!outer_is_targetrel && !inner_is_targetrel);
/*
* For a join relation FROM clause entry is deparsed as
***************
*** 1528,1533 **** deparseUpdateSql(StringInfo buf, PlannerInfo *root,
--- 1583,1590 ----
void
deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List **fdw_scan_tlist,
List *targetlist,
List *targetAttrs,
List *remote_conds,
***************
*** 1535,1541 **** deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
List *returningList,
List **retrieved_attrs)
{
- RelOptInfo *baserel = root->simple_rel_array[rtindex];
deparse_expr_cxt context;
int nestlevel;
bool first;
--- 1592,1597 ----
***************
*** 1543,1555 **** deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
/* Set up context struct for recursion */
context.root = root;
! context.foreignrel = baserel;
! context.scanrel = baserel;
context.buf = buf;
context.params_list = params_list;
appendStringInfoString(buf, "UPDATE ");
deparseRelation(buf, rel);
appendStringInfoString(buf, " SET ");
/* Make sure any constants in the exprs are printed portably */
--- 1599,1613 ----
/* Set up context struct for recursion */
context.root = root;
! context.foreignrel = foreignrel;
! context.scanrel = foreignrel;
context.buf = buf;
context.params_list = params_list;
appendStringInfoString(buf, "UPDATE ");
deparseRelation(buf, rel);
+ if (foreignrel->reloptkind == RELOPT_JOINREL)
+ appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, rtindex);
appendStringInfoString(buf, " SET ");
/* Make sure any constants in the exprs are printed portably */
***************
*** 1576,1589 **** deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
reset_transmission_modes(nestlevel);
if (remote_conds)
{
appendStringInfo(buf, " WHERE ");
appendConditions(remote_conds, &context);
}
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
--- 1634,1672 ----
reset_transmission_modes(nestlevel);
+ if (foreignrel->reloptkind == RELOPT_JOINREL)
+ {
+ List *target_conds = NIL;
+
+ /* Pull up the target relation's conditions into the WHERE clause */
+ pull_up_target_conditions(root, foreignrel, rtindex, &target_conds);
+ remote_conds = list_concat(target_conds, remote_conds);
+
+ appendStringInfo(buf, " FROM ");
+ deparseFromExprForRel(buf, root, foreignrel, true, rtindex,
+ params_list);
+ }
+
if (remote_conds)
{
appendStringInfo(buf, " WHERE ");
appendConditions(remote_conds, &context);
}
! if (foreignrel->reloptkind == RELOPT_JOINREL)
! {
! List *rlist = NIL;
!
! /* Create a RETURNING list */
! rlist = make_explicit_returning_list(rtindex, rel,
! returningList,
! fdw_scan_tlist);
!
! deparseExplicitReturningList(rlist, retrieved_attrs, &context);
! }
! else
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
***************
*** 1618,1640 **** deparseDeleteSql(StringInfo buf, PlannerInfo *root,
void
deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *remote_conds,
List **params_list,
List *returningList,
List **retrieved_attrs)
{
- RelOptInfo *baserel = root->simple_rel_array[rtindex];
deparse_expr_cxt context;
/* Set up context struct for recursion */
context.root = root;
! context.foreignrel = baserel;
! context.scanrel = baserel;
context.buf = buf;
context.params_list = params_list;
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
if (remote_conds)
{
--- 1701,1739 ----
void
deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List **fdw_scan_tlist,
List *remote_conds,
List **params_list,
List *returningList,
List **retrieved_attrs)
{
deparse_expr_cxt context;
/* Set up context struct for recursion */
context.root = root;
! context.foreignrel = foreignrel;
! context.scanrel = foreignrel;
context.buf = buf;
context.params_list = params_list;
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
+ if (foreignrel->reloptkind == RELOPT_JOINREL)
+ appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, rtindex);
+
+ if (foreignrel->reloptkind == RELOPT_JOINREL)
+ {
+ List *target_conds = NIL;
+
+ /* Pull up the target relation's conditions into the WHERE clause */
+ pull_up_target_conditions(root, foreignrel, rtindex, &target_conds);
+ remote_conds = list_concat(target_conds, remote_conds);
+
+ appendStringInfo(buf, " USING ");
+ deparseFromExprForRel(buf, root, foreignrel, true, rtindex,
+ params_list);
+ }
if (remote_conds)
{
***************
*** 1642,1649 **** deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
appendConditions(remote_conds, &context);
}
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
--- 1741,1760 ----
appendConditions(remote_conds, &context);
}
! if (foreignrel->reloptkind == RELOPT_JOINREL)
! {
! List *rlist = NIL;
!
! /* Create a RETURNING list */
! rlist = make_explicit_returning_list(rtindex, rel,
! returningList,
! fdw_scan_tlist);
!
! deparseExplicitReturningList(rlist, retrieved_attrs, &context);
! }
! else
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
***************
*** 1683,1688 **** deparseReturningList(StringInfo buf, PlannerInfo *root,
--- 1794,2011 ----
}
/*
+ * Add a RETURNING clause, if needed, to an UPDATE/DELETE on a join.
+ */
+ static void
+ deparseExplicitReturningList(List *rlist,
+ List **retrieved_attrs,
+ deparse_expr_cxt *context)
+ {
+ deparseExplicitTargetList(true, rlist, retrieved_attrs, context);
+ }
+
+ /*
+ * Create a RETURNING list for executing an UPDATE/DELETE on a join remotely,
+ * if needed.
+ */
+ static List *
+ make_explicit_returning_list(Index rtindex, Relation rel,
+ List *returningList,
+ List **fdw_scan_tlist)
+ {
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ bool have_wholerow = false;
+ List *rlist = NIL;
+ List *vars;
+ ListCell *lc;
+
+ if (returningList == NIL)
+ return NIL;
+
+ vars = pull_var_clause((Node *) returningList, PVC_INCLUDE_PLACEHOLDERS);
+
+ /*
+ * If there's a whole-row reference to the target relation, then we'll need
+ * all the columns of the relation.
+ */
+ foreach(lc, vars)
+ {
+ Var *var = (Var *) lfirst(lc);
+
+ if (IsA(var, Var) &&
+ var->varno == rtindex &&
+ var->varattno == InvalidAttrNumber)
+ {
+ have_wholerow = true;
+ break;
+ }
+ }
+
+ if (have_wholerow)
+ {
+ int i;
+
+ for (i = 1; i <= tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = tupdesc->attrs[i - 1];
+ Var *var;
+
+ /* Ignore dropped attributes. */
+ if (attr->attisdropped)
+ continue;
+
+ var = makeVar(rtindex,
+ i,
+ attr->atttypid,
+ attr->atttypmod,
+ attr->attcollation,
+ 0);
+
+ rlist = lappend(rlist,
+ makeTargetEntry((Expr *) var,
+ list_length(rlist) + 1,
+ NULL,
+ false));
+ }
+ }
+
+ /* Now add any remaining columns to rlist. */
+ foreach(lc, vars)
+ {
+ Var *var = (Var *) lfirst(lc);
+
+ /*
+ * No need for system columns other than ctid and oid or whole-row
+ * Vars of the target relation, since those are set locally.
+ */
+ if (IsA(var, Var) &&
+ var->varno == rtindex &&
+ var->varattno <= InvalidAttrNumber &&
+ var->varattno != SelfItemPointerAttributeNumber &&
+ var->varattno != ObjectIdAttributeNumber)
+ continue; /* don't need it */
+
+ if (tlist_member((Node *) var, rlist))
+ continue; /* already got it */
+
+ rlist = lappend(rlist,
+ makeTargetEntry((Expr *) var,
+ list_length(rlist) + 1,
+ NULL,
+ false));
+ }
+
+ /* Finally, rewrite *fdw_scan_tlist, if needed. */
+ if (rlist != NIL)
+ {
+ List *tlist = list_copy(rlist);
+
+ foreach(lc, *fdw_scan_tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+
+ if (tlist_member((Node *) tle->expr, tlist))
+ continue; /* already got it */
+
+ tlist = lappend(tlist,
+ makeTargetEntry(tle->expr,
+ list_length(tlist) + 1,
+ NULL,
+ false));
+ }
+ *fdw_scan_tlist = tlist;
+ }
+
+ list_free(vars);
+
+ return rlist;
+ }
+
+ /*
+ * Look for conditions mentioning the target relation in the given join tree,
+ * which will be pulled up into the WHERE clause.
+ */
+ static void
+ pull_up_target_conditions(PlannerInfo *root, RelOptInfo *foreignrel,
+ Index target_rel, List **target_conds)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ RelOptInfo *outerrel = fpinfo->outerrel;
+ RelOptInfo *innerrel = fpinfo->innerrel;
+
+ Assert(foreignrel->reloptkind == RELOPT_JOINREL);
+ Assert(bms_is_member(target_rel, foreignrel->relids));
+
+ /* No work if not an inner join. */
+ if (fpinfo->jointype == JOIN_INNER)
+ {
+ /* The remote_conds should be empty. */
+ Assert(fpinfo->remote_conds == NIL);
+
+ /* Extract conditions from the joinclauses. */
+ if ((outerrel->reloptkind == RELOPT_BASEREL &&
+ outerrel->relid == target_rel) ||
+ (innerrel->reloptkind == RELOPT_BASEREL &&
+ innerrel->relid == target_rel))
+ {
+ *target_conds = list_concat(*target_conds,
+ list_copy(fpinfo->joinclauses));
+ fpinfo->joinclauses = NIL;
+ }
+ else
+ extract_target_conditions(&fpinfo->joinclauses,
+ target_rel, target_conds);
+ }
+
+ /* Recurse into either input relation. */
+ if (outerrel->reloptkind == RELOPT_JOINREL &&
+ bms_is_member(target_rel, outerrel->relids))
+ pull_up_target_conditions(root, outerrel, target_rel, target_conds);
+ if (innerrel->reloptkind == RELOPT_JOINREL &&
+ bms_is_member(target_rel, innerrel->relids))
+ pull_up_target_conditions(root, innerrel, target_rel, target_conds);
+ }
+
+ /*
+ * Extract conditions from *joinclauses, separating those that mention
+ * the given relation from those that don't.
+ */
+ static void
+ extract_target_conditions(List **joinclauses, /* in/out parameters */
+ Index target_rel,
+ List **target_conds /* output parameters */)
+ {
+ List *other_conds = NIL;
+ ListCell *lc;
+
+ foreach(lc, *joinclauses)
+ {
+ Node *clause = (Node *) lfirst(lc);
+ Relids relids;
+
+ /* Extract clause from RestrictInfo, if needed. */
+ if (IsA(clause, RestrictInfo))
+ {
+ RestrictInfo *ri = (RestrictInfo *) clause;
+
+ clause = (Node *) ri->clause;
+ }
+
+ /* Retrieve all relids mentioned within the clause. */
+ relids = pull_varnos(clause);
+
+ /* Classify the clause as mentioning the given relation or not. */
+ if (bms_is_member(target_rel, relids))
+ *target_conds = lappend(*target_conds, clause);
+ else
+ other_conds = lappend(other_conds, clause);
+ }
+
+ /* Replace *joinclauses. */
+ *joinclauses = other_conds;
+ }
+
+ /*
* Construct SELECT statement to acquire size in blocks of given relation.
*
* Note: we use local definition of block size, not remote definition.
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 4007,4033 **** UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
! FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
! Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3, c7 = $4 WHERE ctid = $1
! -> Foreign Scan
! Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 || '_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8, ft2.ctid, ft1.*
! Relations: (public.ft2) INNER JOIN (public.ft1)
! Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c8, r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)))) FOR UPDATE OF r1
! -> Hash Join
! Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid, ft1.*
! Hash Cond: (ft2.c2 = ft1.c1)
! -> Foreign Scan on public.ft2
! Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" FOR UPDATE
! -> Hash
! Output: ft1.*, ft1.c1
! -> Foreign Scan on public.ft1
! Output: ft1.*, ft1.c1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 9))
! (17 rows)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
--- 4007,4019 ----
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
! FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can be pushed down
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
! -> Foreign Update
! Remote SQL: UPDATE "S 1"."T 1" r1 SET c2 = (r1.c2 + 500), c3 = (r1.c3 || '_update9'::text), c7 = 'ft2 '::character(10) FROM "S 1"."T 1" r2 WHERE ((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9))
! (3 rows)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
***************
*** 4150,4176 **** DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
(103 rows)
EXPLAIN (verbose, costs off)
! DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
! Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
! -> Foreign Scan
! Output: ft2.ctid, ft1.*
! Relations: (public.ft2) INNER JOIN (public.ft1)
! Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 2)))) FOR UPDATE OF r1
! -> Hash Join
! Output: ft2.ctid, ft1.*
! Hash Cond: (ft2.c2 = ft1.c1)
! -> Foreign Scan on public.ft2
! Output: ft2.ctid, ft2.c2
! Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" FOR UPDATE
! -> Hash
! Output: ft1.*, ft1.c1
! -> Foreign Scan on public.ft1
! Output: ft1.*, ft1.c1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 2))
! (17 rows)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
--- 4136,4148 ----
(103 rows)
EXPLAIN (verbose, costs off)
! DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can be pushed down
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
! -> Foreign Delete
! Remote SQL: DELETE FROM "S 1"."T 1" r1 USING "S 1"."T 1" r2 WHERE ((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 2))
! (3 rows)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 211,216 **** typedef struct PgFdwDirectModifyState
--- 211,222 ----
PGresult *result; /* result for query */
int num_tuples; /* # of result tuples */
int next_tuple; /* index of next one to return */
+ Relation resultRel; /* relcache entry for the target table */
+ TupleTableSlot *resultSlot; /* slot for updated/deleted tuples */
+ AttrNumber *attnoMap; /* array of resultRel attr numbers */
+ AttrNumber ctidAttno; /* attnum of ctid of result tuple */
+ AttrNumber oidAttno; /* attnum of oid of result tuple */
+ bool hasSystemCols; /* are there system columns of resultRel? */
/* working memory context */
MemoryContext temp_cxt; /* context for per-tuple temporary data */
***************
*** 379,384 **** static void store_returning_result(PgFdwModifyState *fmstate,
--- 385,396 ----
TupleTableSlot *slot, PGresult *res);
static void execute_dml_stmt(ForeignScanState *node);
static TupleTableSlot *get_returning_data(ForeignScanState *node);
+ static void init_returning_filter(PgFdwDirectModifyState *dmstate,
+ List *fdw_scan_tlist,
+ Index rtindex,
+ EState *estate);
+ static TupleTableSlot *apply_returning_filter(PgFdwDirectModifyState *dmstate,
+ TupleTableSlot *slot);
static void prepare_query_params(PlanState *node,
List *fdw_exprs,
int numParams,
***************
*** 2108,2113 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2120,2126 ----
Relation rel;
StringInfoData sql;
ForeignScan *fscan;
+ RelOptInfo *foreignrel;
List *targetAttrs = NIL;
List *remote_conds;
List *params_list = NIL;
***************
*** 2139,2151 **** postgresPlanDirectModify(PlannerInfo *root,
return false;
/*
- * We can't handle an UPDATE or DELETE on a foreign join for now.
- */
- fscan = (ForeignScan *) subplan;
- if (fscan->scan.scanrelid == 0)
- return false;
-
- /*
* It's unsafe to update a foreign table directly, if any expressions to
* assign to the target columns are unsafe to evaluate remotely.
*/
--- 2152,2157 ----
***************
*** 2184,2189 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2190,2197 ----
/*
* Ok, rewrite subplan so as to modify the foreign table directly.
*/
+ fscan = (ForeignScan *) subplan;
+
initStringInfo(&sql);
/*
***************
*** 2193,2198 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2201,2218 ----
rel = heap_open(rte->relid, NoLock);
/*
+ * Get a rel for this foreign table or join.
+ */
+ if (fscan->scan.scanrelid == 0)
+ {
+ /* We should have a rel for this foreign join. */
+ foreignrel = find_join_rel(root, fscan->fs_relids);
+ Assert(foreignrel);
+ }
+ else
+ foreignrel = find_base_rel(root, resultRelation);
+
+ /*
* Extract the baserestrictinfo clauses that can be evaluated remotely.
*/
remote_conds = (List *) list_nth(fscan->fdw_private,
***************
*** 2211,2216 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2231,2238 ----
{
case CMD_UPDATE:
deparseDirectUpdateSql(&sql, root, resultRelation, rel,
+ foreignrel,
+ &fscan->fdw_scan_tlist,
((Plan *) fscan)->targetlist,
targetAttrs,
remote_conds, ¶ms_list,
***************
*** 2218,2223 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2240,2247 ----
break;
case CMD_DELETE:
deparseDirectDeleteSql(&sql, root, resultRelation, rel,
+ foreignrel,
+ &fscan->fdw_scan_tlist,
remote_conds, ¶ms_list,
returningList, &retrieved_attrs);
break;
***************
*** 2245,2250 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2269,2280 ----
retrieved_attrs,
makeInteger(plan->canSetTag));
+ /*
+ * We don't need the outer subplan.
+ */
+ if (fscan->scan.scanrelid == 0)
+ fscan->scan.plan.lefttree = NULL;
+
heap_close(rel, NoLock);
return true;
}
***************
*** 2259,2264 **** postgresBeginDirectModify(ForeignScanState *node, int eflags)
--- 2289,2295 ----
ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan;
EState *estate = node->ss.ps.state;
PgFdwDirectModifyState *dmstate;
+ Index rtindex;
RangeTblEntry *rte;
Oid userid;
ForeignTable *table;
***************
*** 2281,2291 **** postgresBeginDirectModify(ForeignScanState *node, int eflags)
* Identify which user to do the remote access as. This should match what
* ExecCheckRTEPerms() does.
*/
! rte = rt_fetch(fsplan->scan.scanrelid, estate->es_range_table);
userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
/* Get info about foreign table. */
! dmstate->rel = node->ss.ss_currentRelation;
table = GetForeignTable(RelationGetRelid(dmstate->rel));
user = GetUserMapping(userid, table->serverid);
--- 2312,2326 ----
* Identify which user to do the remote access as. This should match what
* ExecCheckRTEPerms() does.
*/
! rtindex = estate->es_result_relation_info->ri_RangeTableIndex;
! rte = rt_fetch(rtindex, estate->es_range_table);
userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
/* Get info about foreign table. */
! if (fsplan->scan.scanrelid > 0)
! dmstate->rel = node->ss.ss_currentRelation;
! else
! dmstate->rel = ExecOpenScanRelation(estate, rtindex, eflags);
table = GetForeignTable(RelationGetRelid(dmstate->rel));
user = GetUserMapping(userid, table->serverid);
***************
*** 2295,2300 **** postgresBeginDirectModify(ForeignScanState *node, int eflags)
--- 2330,2344 ----
*/
dmstate->conn = GetConnection(user, false);
+ if (fsplan->scan.scanrelid == 0)
+ {
+ /* Save info about target table. */
+ dmstate->resultRel = dmstate->rel;
+
+ /* rel should be NULL if foreign join. */
+ dmstate->rel = NULL;
+ }
+
/* Initialize state variable */
dmstate->num_tuples = -1; /* -1 means not set yet */
***************
*** 2315,2321 **** postgresBeginDirectModify(ForeignScanState *node, int eflags)
/* Prepare for input conversion of RETURNING results. */
if (dmstate->has_returning)
! dmstate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(dmstate->rel));
/*
* Prepare for processing of parameters used in remote query, if any.
--- 2359,2379 ----
/* Prepare for input conversion of RETURNING results. */
if (dmstate->has_returning)
! {
! TupleDesc tupdesc;
!
! if (fsplan->scan.scanrelid > 0)
! tupdesc = RelationGetDescr(dmstate->rel);
! else
! tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
!
! dmstate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
!
! /* Initialize a filter to extract an updated/deleted tuple. */
! if (fsplan->scan.scanrelid == 0)
! init_returning_filter(dmstate, fsplan->fdw_scan_tlist,
! rtindex, estate);
! }
/*
* Prepare for processing of parameters used in remote query, if any.
***************
*** 2396,2401 **** postgresEndDirectModify(ForeignScanState *node)
--- 2454,2463 ----
ReleaseConnection(dmstate->conn);
dmstate->conn = NULL;
+ /* close the result relation. */
+ if (dmstate->resultRel)
+ ExecCloseScanRelation(dmstate->resultRel);
+
/* MemoryContext will be deleted automatically. */
}
***************
*** 3323,3328 **** get_returning_data(ForeignScanState *node)
--- 3385,3391 ----
EState *estate = node->ss.ps.state;
ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
+ TupleTableSlot *resultSlot;
Assert(resultRelInfo->ri_projectReturning);
***************
*** 3340,3346 **** get_returning_data(ForeignScanState *node)
--- 3403,3412 ----
* "UPDATE/DELETE .. RETURNING 1" for example.)
*/
if (!dmstate->has_returning)
+ {
ExecStoreAllNullTuple(slot);
+ resultSlot = slot;
+ }
else
{
/*
***************
*** 3356,3362 **** get_returning_data(ForeignScanState *node)
dmstate->rel,
dmstate->attinmeta,
dmstate->retrieved_attrs,
! NULL,
dmstate->temp_cxt);
ExecStoreTuple(newtup, slot, InvalidBuffer, false);
}
--- 3422,3428 ----
dmstate->rel,
dmstate->attinmeta,
dmstate->retrieved_attrs,
! node,
dmstate->temp_cxt);
ExecStoreTuple(newtup, slot, InvalidBuffer, false);
}
***************
*** 3367,3382 **** get_returning_data(ForeignScanState *node)
PG_RE_THROW();
}
PG_END_TRY();
}
dmstate->next_tuple++;
/* Make slot available for evaluation of the local query RETURNING list. */
! resultRelInfo->ri_projectReturning->pi_exprContext->ecxt_scantuple = slot;
return slot;
}
/*
* Prepare for processing of parameters used in remote query.
*/
static void
--- 3433,3620 ----
PG_RE_THROW();
}
PG_END_TRY();
+
+ /* Get the updated/deleted tuple. */
+ if (dmstate->rel)
+ resultSlot = slot;
+ else
+ resultSlot = apply_returning_filter(dmstate, slot);
}
dmstate->next_tuple++;
/* Make slot available for evaluation of the local query RETURNING list. */
! resultRelInfo->ri_projectReturning->pi_exprContext->ecxt_scantuple = resultSlot;
return slot;
}
/*
+ * Initialize a filter to extract an updated/deleted tuple from a scan tuple.
+ */
+ static void
+ init_returning_filter(PgFdwDirectModifyState *dmstate,
+ List *fdw_scan_tlist,
+ Index rtindex,
+ EState *estate)
+ {
+ TupleDesc resultTupType = RelationGetDescr(dmstate->resultRel);
+ ListCell *lc;
+ int i;
+
+ /* Make a new slot for storing an output tuple. */
+ dmstate->resultSlot = ExecInitExtraTupleSlot(estate);
+
+ ExecSetSlotDescriptor(dmstate->resultSlot, resultTupType);
+
+ /*
+ * Calculate the mapping between the fdw_scan_tlist list's entries and
+ * the updated/deleted tuple's attributes.
+ *
+ * The "map" is an array of the resultRel attribute numbers, i.e. one
+ * entry for every attribute of the updated/deleted tuple. The value of
+ * this entry is the index of the corresponding entry in fdw_scan_tlist.
+ * We store zero for any attributes that don't have the corresponding
+ * entries in fdw_scan_tlist (i.e. attributes that won't be retrieved
+ * from the remote server), marking that a NULL is needed in the output
+ * tuple.
+ *
+ * Also get the index of the entry for ctid/oid of the updated/deleted
+ * tuple if any.
+ */
+ dmstate->attnoMap = (AttrNumber *) palloc0(resultTupType->natts * sizeof(AttrNumber));
+
+ dmstate->ctidAttno = dmstate->oidAttno = 0;
+
+ i = 1;
+ dmstate->hasSystemCols = false;
+ foreach(lc, fdw_scan_tlist)
+ {
+ if (list_member_int(dmstate->retrieved_attrs, i))
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Var *var = (Var *) tle->expr;
+
+ if (IsA(var, Var) && var->varno == rtindex)
+ {
+ int attrno = var->varattno;
+
+ if (attrno < 0)
+ {
+ if (attrno == SelfItemPointerAttributeNumber)
+ dmstate->ctidAttno = i;
+ else if (attrno == ObjectIdAttributeNumber)
+ dmstate->oidAttno = i;
+ dmstate->hasSystemCols = true;
+ }
+ else
+ {
+ Assert(attrno > 0);
+ dmstate->attnoMap[attrno - 1] = i;
+ }
+ }
+ }
+ i++;
+ }
+ }
+
+ /*
+ * Extract and return an updated/deleted tuple from a scan tuple.
+ */
+ static TupleTableSlot *
+ apply_returning_filter(PgFdwDirectModifyState *dmstate,
+ TupleTableSlot *slot)
+ {
+ TupleTableSlot *resultSlot = dmstate->resultSlot;
+ TupleDesc resultTupType = RelationGetDescr(dmstate->resultRel);
+ HeapTuple resultTup;
+ Datum *values;
+ bool *isnull;
+ Datum *old_values;
+ bool *old_isnull;
+ int i;
+
+ /*
+ * Extract all the values of the scan tuple.
+ */
+ slot_getallattrs(slot);
+ old_values = slot->tts_values;
+ old_isnull = slot->tts_isnull;
+
+ /*
+ * Prepare to build a result tuple.
+ */
+ ExecClearTuple(resultSlot);
+ values = resultSlot->tts_values;
+ isnull = resultSlot->tts_isnull;
+
+ /*
+ * Transpose data into proper fields of the result tuple.
+ */
+ for (i = 0; i < resultTupType->natts; i++)
+ {
+ int j = dmstate->attnoMap[i];
+
+ if (j == 0)
+ {
+ values[i] = (Datum) 0;
+ isnull[i] = true;
+ }
+ else
+ {
+ values[i] = old_values[j - 1];
+ isnull[i] = old_isnull[j - 1];
+ }
+ }
+
+ /*
+ * Build the virtual result tuple.
+ */
+ ExecStoreVirtualTuple(resultSlot);
+
+ /*
+ * If we have any system columns to return, install it in t_self.
+ */
+ if (dmstate->hasSystemCols)
+ {
+ resultTup = ExecMaterializeSlot(resultSlot);
+
+ /* ctid */
+ if (dmstate->ctidAttno)
+ {
+ ItemPointer ctid = NULL;
+
+ ctid = (ItemPointer) DatumGetPointer(old_values[dmstate->ctidAttno - 1]);
+ resultTup->t_self = *ctid;
+ }
+
+ /* oid */
+ if (dmstate->oidAttno)
+ {
+ Oid oid = InvalidOid;
+
+ oid = DatumGetObjectId(old_values[dmstate->oidAttno - 1]);
+ HeapTupleSetOid(resultTup, oid);
+ }
+
+ /*
+ * xmin, xmax, cmin, and tableoid (Note that since we currently
+ * don't allow the result relation to appear on the nullable side
+ * of an outer join, they can't go to NULL.)
+ */
+ HeapTupleHeaderSetXmin(resultTup->t_data, InvalidTransactionId);
+ HeapTupleHeaderSetXmax(resultTup->t_data, InvalidTransactionId);
+ HeapTupleHeaderSetCmin(resultTup->t_data, InvalidTransactionId);
+
+ resultTup->t_tableOid = RelationGetRelid(dmstate->resultRel);
+ }
+
+ /*
+ * And return the result tuple.
+ */
+ return resultSlot;
+ }
+
+ /*
* Prepare for processing of parameters used in remote query.
*/
static void
***************
*** 4796,4806 **** make_tuple_from_result_row(PGresult *res,
tupdesc = RelationGetDescr(rel);
else
{
- PgFdwScanState *fdw_sstate;
-
Assert(fsstate);
! fdw_sstate = (PgFdwScanState *) fsstate->fdw_state;
! tupdesc = fdw_sstate->tupdesc;
}
values = (Datum *) palloc0(tupdesc->natts * sizeof(Datum));
--- 5034,5041 ----
tupdesc = RelationGetDescr(rel);
else
{
Assert(fsstate);
! tupdesc = fsstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
}
values = (Datum *) palloc0(tupdesc->natts * sizeof(Datum));
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
***************
*** 137,142 **** extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
--- 137,144 ----
List **retrieved_attrs);
extern void deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List **fdw_scan_tlist,
List *targetlist,
List *targetAttrs,
List *remote_conds,
***************
*** 149,154 **** extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root,
--- 151,158 ----
List **retrieved_attrs);
extern void deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List **fdw_scan_tlist,
List *remote_conds,
List **params_list,
List *returningList,
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 987,1000 **** UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
! FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
EXPLAIN (verbose, costs off)
! DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
--- 987,1000 ----
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
! FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can be pushed down
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
EXPLAIN (verbose, costs off)
! DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can be pushed down
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
