On 2016/02/22 20:13, Rushabh Lathia wrote:
PFA update patch, which includes changes into postgresPlanDMLPushdown()
to check for join
condition before target columns and also fixed couple of whitespace issues.
For pushing down an UPDATE/DELETE on a foreign join to the remote, I
created a WIP patch on top of the latest version of the DML pushdown
patch. Attached is the WIP patch. I'd like to propose this as part of
(I'd like to discuss this as a separate patch, though):
https://commitfest.postgresql.org/9/453/
The patch doesn't correctly evaluate the values of system columns of
joined relations in RETURNING, other than ctid. I'll fix that ASAP.
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 130,135 **** static void deparseReturningList(StringInfo buf, PlannerInfo *root,
--- 130,143 ----
bool trig_after_row,
List *returningList,
List **retrieved_attrs);
+ static void deparseJoinedReturningList(List *fdw_scan_tlist,
+ Index rtindex,
+ Bitmapset *attrs_used,
+ List *returningList,
+ List **retrieved_attrs,
+ List **result_attrs,
+ List **result_attrnos,
+ deparse_expr_cxt *context);
static void deparseColumnRef(StringInfo buf, int varno, int varattno,
PlannerInfo *root, bool qualify_col);
static void deparseRelation(StringInfo buf, Relation rel);
***************
*** 158,164 **** 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);
/*
--- 166,175 ----
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,
! Index ignore_rel,
! bool use_alias,
! List **params_list);
/*
***************
*** 850,856 **** deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
* Construct FROM clause
*/
appendStringInfoString(buf, " FROM ");
! deparseFromExprForRel(buf, root, foreignrel,
(foreignrel->reloptkind == RELOPT_JOINREL),
context->params_list);
}
--- 861,867 ----
* Construct FROM clause
*/
appendStringInfoString(buf, " FROM ");
! deparseFromExprForRel(buf, root, foreignrel, (Index) 0,
(foreignrel->reloptkind == RELOPT_JOINREL),
context->params_list);
}
***************
*** 1135,1144 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
* The function constructs ... JOIN ... ON ... for join relation. For a base
* relation it just returns schema-qualified tablename, with the appropriate
* alias if so requested.
*/
static void
! deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
--- 1146,1166 ----
* The function constructs ... JOIN ... ON ... for join relation. For a base
* relation it just returns schema-qualified tablename, with the appropriate
* alias if so requested.
+ *
+ * If constructing FROM clause of UPDATE statement or USING clause of DELETE
+ * statement, we simply ignore the ignore_rel target relation when deparsing
+ * the join to the target relation. Note that the join is safely interchanged
+ * with higher-level outer joins (if any) by outer-join identity 1 since that
+ * the join won't appear on the nullable side of such outer joins (we currently
+ * don't allow the result relation to appear on the nullable side of an outer
+ * join) and that the target relation won't be outer-joined to other relations.
*/
static void
! deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
! RelOptInfo *foreignrel,
! Index ignore_rel,
! bool use_alias,
! List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
***************
*** 1148,1169 **** 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
*
* ((outer relation) <join type> (inner relation) ON (joinclauses)
*/
! appendStringInfo(buf, "(%s %s JOIN %s ON ", join_sql_o.data,
! get_jointype_name(fpinfo->jointype), join_sql_i.data);
/* Append join clause; (TRUE) if no join clause */
if (fpinfo->joinclauses)
--- 1170,1260 ----
RelOptInfo *rel_i = fpinfo->innerrel;
StringInfoData join_sql_o;
StringInfoData join_sql_i;
+ bool do_deparse_o = true;
+ bool do_deparse_i = true;
+ Relids result = NULL;
+ Relids target = NULL;
+
+ if (ignore_rel > 0)
+ {
+ int varno_o = -1;
+ int varno_i = -1;
+
+ do_deparse_o =
+ !(bms_get_singleton_member(rel_o->relids, &varno_o) &&
+ (varno_o == ignore_rel));
+ do_deparse_i =
+ !(bms_get_singleton_member(rel_i->relids, &varno_i) &&
+ (varno_i == ignore_rel));
+ }
/* Deparse outer relation */
! if (do_deparse_o)
! {
! initStringInfo(&join_sql_o);
! deparseFromExprForRel(&join_sql_o, root, rel_o, ignore_rel, true,
! params_list);
! }
/* Deparse inner relation */
! if (do_deparse_i)
! {
! initStringInfo(&join_sql_i);
! deparseFromExprForRel(&join_sql_i, root, rel_i, ignore_rel, true,
! params_list);
! }
!
! if (!do_deparse_o || !do_deparse_i)
! {
! /* This should be for UPDATE/DELETE */
! Assert(ignore_rel > 0);
! /* The join should be an inner join */
! Assert(fpinfo->joinclauses == NIL);
!
! /* Don't parenthesize the expression */
! if (!do_deparse_o)
! appendStringInfo(buf, "%s", join_sql_i.data);
! else
! appendStringInfo(buf, "%s", join_sql_o.data);
! return;
! }
/*
* For a join relation FROM clause entry is deparsed as
*
* ((outer relation) <join type> (inner relation) ON (joinclauses)
+ *
+ * Note: if constructing FROM clause of UPDATE or USING clause of DELETE,
+ * don't parenthesize the topmost expression.
*/
!
! /* Begin the FROM clause entry. */
! if (ignore_rel == 0 && bms_equal(foreignrel->relids, root->all_baserels))
! appendStringInfoChar(buf, '(');
!
! target = bms_make_singleton(ignore_rel);
!
! if (ignore_rel == 0)
! result = rel_o->relids;
! else
! result = bms_difference(rel_o->relids, target);
! if (bms_num_members(result) > 1)
! appendStringInfo(buf, "(%s)", join_sql_o.data);
! else
! appendStringInfo(buf, "%s", join_sql_o.data);
!
! appendStringInfo(buf, " %s JOIN ", get_jointype_name(fpinfo->jointype));
!
! if (ignore_rel == 0)
! result = rel_i->relids;
! else
! result = bms_difference(rel_i->relids, target);
! if (bms_num_members(result) > 1)
! appendStringInfo(buf, "(%s)", join_sql_i.data);
! else
! appendStringInfo(buf, "%s", join_sql_i.data);
!
! appendStringInfoString(buf, " ON ");
/* Append join clause; (TRUE) if no join clause */
if (fpinfo->joinclauses)
***************
*** 1183,1189 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
appendStringInfoString(buf, "(TRUE)");
/* End the FROM clause entry. */
! appendStringInfo(buf, ")");
}
else
{
--- 1274,1281 ----
appendStringInfoString(buf, "(TRUE)");
/* End the FROM clause entry. */
! if (ignore_rel == 0 && bms_equal(foreignrel->relids, root->all_baserels))
! appendStringInfoChar(buf, ')');
}
else
{
***************
*** 1207,1213 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
heap_close(rel, NoLock);
}
- return;
}
/*
--- 1299,1304 ----
***************
*** 1325,1338 **** deparseUpdateSql(StringInfo buf, PlannerInfo *root,
void
deparsePushedDownUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetlist,
List *targetAttrs,
List *remote_conds,
List **params_list,
List *returningList,
! List **retrieved_attrs)
{
- RelOptInfo *baserel = root->simple_rel_array[rtindex];
deparse_expr_cxt context;
int nestlevel;
bool first;
--- 1416,1433 ----
void
deparsePushedDownUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List *fdw_scan_tlist,
+ Bitmapset *attrs_used,
List *targetlist,
List *targetAttrs,
List *remote_conds,
List **params_list,
List *returningList,
! List **retrieved_attrs,
! List **result_attrs,
! List **result_attrnos)
{
deparse_expr_cxt context;
int nestlevel;
bool first;
***************
*** 1340,1351 **** deparsePushedDownUpdateSql(StringInfo buf, PlannerInfo *root,
/* Set up context struct for recursion */
context.root = root;
! context.foreignrel = 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 */
--- 1435,1448 ----
/* Set up context struct for recursion */
context.root = root;
! context.foreignrel = 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 */
***************
*** 1368,1381 **** deparsePushedDownUpdateSql(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);
}
/*
--- 1465,1490 ----
reset_transmission_modes(nestlevel);
+ if (foreignrel->reloptkind == RELOPT_JOINREL)
+ {
+ appendStringInfoString(buf, " FROM ");
+ deparseFromExprForRel(buf, root, foreignrel, rtindex, true,
+ params_list);
+ }
+
if (remote_conds)
{
appendStringInfo(buf, " WHERE ");
appendConditions(remote_conds, &context);
}
! if (foreignrel->reloptkind == RELOPT_JOINREL)
! deparseJoinedReturningList(fdw_scan_tlist, rtindex, attrs_used,
! returningList, retrieved_attrs,
! result_attrs, result_attrnos, &context);
! else
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
***************
*** 1410,1431 **** deparseDeleteSql(StringInfo buf, PlannerInfo *root,
void
deparsePushedDownDeleteSql(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.buf = buf;
context.params_list = params_list;
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
if (remote_conds)
{
--- 1519,1553 ----
void
deparsePushedDownDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List *fdw_scan_tlist,
+ Bitmapset *attrs_used,
List *remote_conds,
List **params_list,
List *returningList,
! List **retrieved_attrs,
! List **result_attrs,
! List **result_attrnos)
{
deparse_expr_cxt context;
/* Set up context struct for recursion */
context.root = root;
! context.foreignrel = 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)
+ {
+ appendStringInfoString(buf, " USING ");
+ deparseFromExprForRel(buf, root, foreignrel, rtindex, true,
+ params_list);
+ }
if (remote_conds)
{
***************
*** 1433,1440 **** deparsePushedDownDeleteSql(StringInfo buf, PlannerInfo *root,
appendConditions(remote_conds, &context);
}
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
--- 1555,1567 ----
appendConditions(remote_conds, &context);
}
! if (foreignrel->reloptkind == RELOPT_JOINREL)
! deparseJoinedReturningList(fdw_scan_tlist, rtindex, attrs_used,
! returningList, retrieved_attrs,
! result_attrs, result_attrnos, &context);
! else
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
***************
*** 1474,1479 **** deparseReturningList(StringInfo buf, PlannerInfo *root,
--- 1601,1682 ----
}
/*
+ * Add a RETURNING clause, if needed, to an UPDATE/DELETE on a foreign join.
+ */
+ static void
+ deparseJoinedReturningList(List *fdw_scan_tlist,
+ Index rtindex,
+ Bitmapset *attrs_used,
+ List *returningList,
+ List **retrieved_attrs,
+ List **result_attrs,
+ List **result_attrnos,
+ deparse_expr_cxt *context)
+ {
+ StringInfo buf = context->buf;
+ List *returning_vars;
+ bool have_wholerow;
+ bool first;
+ ListCell *lc;
+ int i;
+
+ *retrieved_attrs = NIL;
+ *result_attrs = NIL;
+ *result_attrnos = NIL;
+
+ if (returningList == NIL)
+ return;
+
+ /* Pull out all the Vars mentioned in returningList */
+ returning_vars = pull_var_clause((Node *) returningList,
+ PVC_REJECT_AGGREGATES,
+ PVC_RECURSE_PLACEHOLDERS);
+
+ /*
+ * If there's a whole-row reference of the result relation, we'll need all
+ * the columns of the relation.
+ */
+ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ attrs_used);
+
+ first = true;
+ i = 1;
+ foreach(lc, fdw_scan_tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Var *var = (Var *) tle->expr;
+
+ if (((var->varno != rtindex) &&
+ (var->varattno >= 0 ||
+ var->varattno == SelfItemPointerAttributeNumber) &&
+ list_member(returning_vars, var)) ||
+ ((var->varno == rtindex) &&
+ (have_wholerow ||
+ bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
+ attrs_used))))
+ {
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ else
+ appendStringInfoString(buf, " RETURNING ");
+ first = false;
+
+ deparseVar(var, context);
+
+ *retrieved_attrs = lappend_int(*retrieved_attrs, i);
+
+ if (var->varno == rtindex)
+ {
+ *result_attrs = lappend_int(*result_attrs, i);
+ *result_attrnos = lappend_int(*result_attrnos, var->varattno);
+ }
+ }
+
+ i++;
+ }
+ }
+
+ /*
* 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
***************
*** 2387,2413 **** 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, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((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;
--- 2387,2399 ----
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;
***************
*** 2530,2556 **** 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, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((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;
--- 2516,2528 ----
(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
***************
*** 108,113 **** enum FdwModifyPrivateIndex
--- 108,115 ----
* 2) Boolean flag showing if the remote query has a RETURNING clause
* 3) Integer list of attribute numbers retrieved by RETURNING, if any
* 4) Boolean flag showing if we set the command es_processed
+ * 5) Integer list of the attribute numbers of result rel's columns
+ * 6) Integer list of result rel's attribute numbers of the columns
*/
enum FdwDmlPushdownPrivateIndex
{
***************
*** 118,124 **** enum FdwDmlPushdownPrivateIndex
/* Integer list of attribute numbers retrieved by RETURNING */
FdwDmlPushdownPrivateRetrievedAttrs,
/* set-processed flag (as an integer Value node) */
! FdwDmlPushdownPrivateSetProcessed
};
/*
--- 120,130 ----
/* Integer list of attribute numbers retrieved by RETURNING */
FdwDmlPushdownPrivateRetrievedAttrs,
/* set-processed flag (as an integer Value node) */
! FdwDmlPushdownPrivateSetProcessed,
! /* Integer list of the attribute numbers of result rel's columns */
! FdwDmlPushdownPrivateResultAttrs,
! /* Integer list of result rel's attribute numbers of the columns */
! FdwDmlPushdownPrivateResultAttrnos
};
/*
***************
*** 201,206 **** typedef struct PgFdwDmlPushdownState
--- 207,214 ----
bool has_returning; /* is there a RETURNING clause? */
List *retrieved_attrs; /* attr numbers retrieved by RETURNING */
bool set_processed; /* do we set the command es_processed? */
+ List *result_attrs;
+ List *result_attrnos;
/* for remote query execution */
PGconn *conn; /* connection for the update */
***************
*** 213,218 **** typedef struct PgFdwDmlPushdownState
--- 221,230 ----
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;
+ AttrNumber ctidAttno;
/* working memory context */
MemoryContext temp_cxt; /* context for per-tuple temporary data */
***************
*** 378,385 **** static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
--- 390,404 ----
TupleTableSlot *slot);
static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
+ static void expand_scan_tlist(Index resultRelation,
+ Relation rel,
+ Bitmapset *attrs_used,
+ List **fdw_scan_tlist);
static void execute_dml_stmt(ForeignScanState *node);
static TupleTableSlot *get_returning_data(ForeignScanState *node);
+ static void init_returning_filter(PgFdwDmlPushdownState *dpstate);
+ static TupleTableSlot *execute_returning_filter(PgFdwDmlPushdownState *dpstate,
+ TupleTableSlot *slot);
static void prepare_query_params(PlanState *node,
List *fdw_exprs,
int numParams,
***************
*** 2069,2079 **** postgresPlanDMLPushdown(PlannerInfo *root,
--- 2088,2102 ----
Relation rel;
StringInfoData sql;
ForeignScan *fscan;
+ RelOptInfo *foreignrel;
+ Bitmapset *attrs_used = NULL;
List *targetAttrs = NIL;
List *remote_conds;
List *params_list = NIL;
List *returningList = NIL;
List *retrieved_attrs = NIL;
+ List *result_attrs = NIL;
+ List *result_attrnos = NIL;
/*
* Decide whether the table modification is pushdown-safe.
***************
*** 2100,2113 **** postgresPlanDMLPushdown(PlannerInfo *root,
return false;
/*
! * 4. We can't push down an UPDATE or DELETE on a foreign join for now.
! */
! fscan = (ForeignScan *) subplan;
! if (fscan->scan.scanrelid == 0)
! return false;
!
! /*
! * 5. We can't push down an UPDATE, if any expressions to assign to the
* target columns are unsafe to evaluate on the remote end.
*/
if (operation == CMD_UPDATE)
--- 2123,2129 ----
return false;
/*
! * 4. We can't push down an UPDATE, if any expressions to assign to the
* target columns are unsafe to evaluate on the remote end.
*/
if (operation == CMD_UPDATE)
***************
*** 2141,2146 **** postgresPlanDMLPushdown(PlannerInfo *root,
--- 2157,2164 ----
/*
* Ok, modify subplan so as to push down the command to the remote server.
*/
+ fscan = (ForeignScan *) subplan;
+
initStringInfo(&sql);
/*
***************
*** 2150,2155 **** postgresPlanDMLPushdown(PlannerInfo *root,
--- 2168,2185 ----
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,
***************
*** 2159,2166 **** postgresPlanDMLPushdown(PlannerInfo *root,
--- 2189,2219 ----
* Extract the relevant RETURNING list if any.
*/
if (plan->returningLists)
+ {
returningList = (List *) list_nth(plan->returningLists, subplan_index);
+ if (foreignrel->reloptkind == RELOPT_JOINREL)
+ {
+ /*
+ * We need the attrs, non-system and system, mentioned in the local
+ * query's RETURNING list.
+ */
+ pull_varattnos((Node *) returningList, resultRelation,
+ &attrs_used);
+
+ /*
+ * For UPDATE queries, fdw_scan_tlist contains tlist entries for
+ * all attributes of the result relation through the work of the
+ * rewriter and planner, but for DELETE queries, it doesn't contain
+ * any such entriers except for ctid. So, add to it tlist entries
+ * needed for the local query's RETURNING calculation.
+ */
+ if (operation == CMD_DELETE)
+ expand_scan_tlist(resultRelation, rel, attrs_used,
+ &fscan->fdw_scan_tlist);
+ }
+ }
+
/*
* Construct the SQL command string.
*/
***************
*** 2168,2182 **** postgresPlanDMLPushdown(PlannerInfo *root,
{
case CMD_UPDATE:
deparsePushedDownUpdateSql(&sql, root, resultRelation, rel,
((Plan *) fscan)->targetlist,
targetAttrs,
remote_conds, ¶ms_list,
! returningList, &retrieved_attrs);
break;
case CMD_DELETE:
deparsePushedDownDeleteSql(&sql, root, resultRelation, rel,
remote_conds, ¶ms_list,
! returningList, &retrieved_attrs);
break;
default:
elog(ERROR, "unexpected operation: %d", (int) operation);
--- 2221,2241 ----
{
case CMD_UPDATE:
deparsePushedDownUpdateSql(&sql, root, resultRelation, rel,
+ foreignrel, fscan->fdw_scan_tlist,
+ attrs_used,
((Plan *) fscan)->targetlist,
targetAttrs,
remote_conds, ¶ms_list,
! returningList, &retrieved_attrs,
! &result_attrs, &result_attrnos);
break;
case CMD_DELETE:
deparsePushedDownDeleteSql(&sql, root, resultRelation, rel,
+ foreignrel, fscan->fdw_scan_tlist,
+ attrs_used,
remote_conds, ¶ms_list,
! returningList, &retrieved_attrs,
! &result_attrs, &result_attrnos);
break;
default:
elog(ERROR, "unexpected operation: %d", (int) operation);
***************
*** 2202,2207 **** postgresPlanDMLPushdown(PlannerInfo *root,
--- 2261,2272 ----
retrieved_attrs,
makeInteger(plan->canSetTag));
+ fscan->fdw_private = lappend(fscan->fdw_private, result_attrs);
+ fscan->fdw_private = lappend(fscan->fdw_private, result_attrnos);
+
+ if (fscan->scan.scanrelid == 0)
+ fscan->scan.plan.lefttree = NULL;
+
heap_close(rel, NoLock);
return true;
}
***************
*** 2216,2221 **** postgresBeginDMLPushdown(ForeignScanState *node, int eflags)
--- 2281,2287 ----
ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan;
EState *estate = node->ss.ps.state;
PgFdwDmlPushdownState *dpstate;
+ Index rtindex;
RangeTblEntry *rte;
Oid userid;
ForeignTable *table;
***************
*** 2238,2248 **** postgresBeginDMLPushdown(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. */
! dpstate->rel = node->ss.ss_currentRelation;
table = GetForeignTable(RelationGetRelid(dpstate->rel));
user = GetUserMapping(userid, table->serverid);
--- 2304,2323 ----
* Identify which user to do the remote access as. This should match what
* ExecCheckRTEPerms() does.
*/
! if (fsplan->scan.scanrelid == 0)
! rtindex = estate->es_result_relation_info->ri_RangeTableIndex;
! else
! rtindex = fsplan->scan.scanrelid;
!
! rte = rt_fetch(rtindex, estate->es_range_table);
userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
/* Get info about foreign table. */
! if (fsplan->scan.scanrelid == 0)
! dpstate->rel = ExecOpenScanRelation(estate, rtindex, eflags);
! else
! dpstate->rel = node->ss.ss_currentRelation;
!
table = GetForeignTable(RelationGetRelid(dpstate->rel));
user = GetUserMapping(userid, table->serverid);
***************
*** 2252,2257 **** postgresBeginDMLPushdown(ForeignScanState *node, int eflags)
--- 2327,2341 ----
*/
dpstate->conn = GetConnection(user, false);
+ if (fsplan->scan.scanrelid == 0)
+ {
+ /* Save info about target table. */
+ dpstate->resultRel = dpstate->rel;
+
+ /* rel should be NULL if foreign join. */
+ dpstate->rel = NULL;
+ }
+
/* Initialize state variable */
dpstate->num_tuples = -1; /* -1 means not set yet */
***************
*** 2264,2269 **** postgresBeginDMLPushdown(ForeignScanState *node, int eflags)
--- 2348,2357 ----
FdwDmlPushdownPrivateRetrievedAttrs);
dpstate->set_processed = intVal(list_nth(fsplan->fdw_private,
FdwDmlPushdownPrivateSetProcessed));
+ dpstate->result_attrs = (List *) list_nth(fsplan->fdw_private,
+ FdwDmlPushdownPrivateResultAttrs);
+ dpstate->result_attrnos = (List *) list_nth(fsplan->fdw_private,
+ FdwDmlPushdownPrivateResultAttrnos);
/* Create context for per-tuple temp workspace. */
dpstate->temp_cxt = AllocSetContextCreate(estate->es_query_cxt,
***************
*** 2274,2280 **** postgresBeginDMLPushdown(ForeignScanState *node, int eflags)
/* Prepare for input conversion of RETURNING results. */
if (dpstate->has_returning)
! dpstate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(dpstate->rel));
/*
* Prepare for processing of parameters used in remote query, if any.
--- 2362,2386 ----
/* Prepare for input conversion of RETURNING results. */
if (dpstate->has_returning)
! {
! TupleDesc tupdesc;
!
! if (fsplan->scan.scanrelid == 0)
! tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
! else
! tupdesc = RelationGetDescr(dpstate->rel);
!
! dpstate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
!
! if (fsplan->scan.scanrelid == 0)
! {
! /* Make a new slot for storing an output tuple. */
! dpstate->resultSlot = ExecInitExtraTupleSlot(estate);
!
! /* Initialize a filter to extract an updated/deleted tuple. */
! init_returning_filter(dpstate);
! }
! }
/*
* Prepare for processing of parameters used in remote query, if any.
***************
*** 2355,2360 **** postgresEndDMLPushdown(ForeignScanState *node)
--- 2461,2470 ----
ReleaseConnection(dpstate->conn);
dpstate->conn = NULL;
+ /* close the result relation. */
+ if (dpstate->resultRel)
+ ExecCloseScanRelation(dpstate->resultRel);
+
/* MemoryContext will be deleted automatically. */
}
***************
*** 3116,3121 **** store_returning_result(PgFdwModifyState *fmstate,
--- 3226,3282 ----
}
/*
+ * Add to *fdw_scan_tlist tlist entries for the columns specified in attrs_used.
+ */
+ static void
+ expand_scan_tlist(Index resultRelation,
+ Relation rel,
+ Bitmapset *attrs_used,
+ List **fdw_scan_tlist)
+ {
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ bool have_wholerow;
+ int next_resno;
+ int i;
+
+ /* If there's a whole-row reference, we'll need all the columns. */
+ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ attrs_used);
+
+ next_resno = list_length(*fdw_scan_tlist) + 1;
+ for (i = 1; i <= tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = tupdesc->attrs[i - 1];
+
+ /* Ignore dropped attributes. */
+ if (attr->attisdropped)
+ continue;
+
+ if (have_wholerow ||
+ bms_is_member(i - FirstLowInvalidHeapAttributeNumber,
+ attrs_used))
+ {
+ Node *expr;
+ TargetEntry *tle;
+
+ expr = (Node *) makeVar(resultRelation,
+ i,
+ attr->atttypid,
+ attr->atttypmod,
+ attr->attcollation,
+ 0);
+
+ tle = makeTargetEntry((Expr *) expr,
+ next_resno++,
+ NULL,
+ false);
+
+ *fdw_scan_tlist = lappend(*fdw_scan_tlist, tle);
+ }
+ }
+ }
+
+ /*
* Execute a pushed-down UPDATE/DELETE statement.
*/
static void
***************
*** 3169,3174 **** get_returning_data(ForeignScanState *node)
--- 3330,3336 ----
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);
***************
*** 3186,3192 **** get_returning_data(ForeignScanState *node)
--- 3348,3357 ----
* UPDATE/DELETE .. RETURNING 1 for example.)
*/
if (!dpstate->has_returning)
+ {
ExecStoreAllNullTuple(slot);
+ resultSlot = slot;
+ }
else
{
/*
***************
*** 3202,3208 **** get_returning_data(ForeignScanState *node)
dpstate->rel,
dpstate->attinmeta,
dpstate->retrieved_attrs,
! NULL,
dpstate->temp_cxt);
ExecStoreTuple(newtup, slot, InvalidBuffer, false);
}
--- 3367,3373 ----
dpstate->rel,
dpstate->attinmeta,
dpstate->retrieved_attrs,
! node,
dpstate->temp_cxt);
ExecStoreTuple(newtup, slot, InvalidBuffer, false);
}
***************
*** 3213,3228 **** get_returning_data(ForeignScanState *node)
PG_RE_THROW();
}
PG_END_TRY();
}
dpstate->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
--- 3378,3521 ----
PG_RE_THROW();
}
PG_END_TRY();
+
+ /* Get the updated/deleted tuple. */
+ if (dpstate->rel)
+ resultSlot = slot;
+ else
+ resultSlot = execute_returning_filter(dpstate, slot);
}
dpstate->next_tuple++;
! /*
! * Make resultSlot available to the ModifyTable node for evaluation of
! * the local query's RETURNING list.
! */
! resultRelInfo->ri_projectReturning->pi_exprContext->ecxt_scantuple = resultSlot;
return slot;
}
/*
+ * Initialize a filter to extract an updated/deleted tuple from a given
+ * fdw_scan_tlist tuple.
+ */
+ static void
+ init_returning_filter(PgFdwDmlPushdownState *dpstate)
+ {
+ TupleDesc resultTupType = RelationGetDescr(dpstate->resultRel);
+ ListCell *lc;
+ ListCell *lc2;
+
+ ExecSetSlotDescriptor(dpstate->resultSlot, resultTupType);
+
+ /*
+ * Calculate the mapping between the fdw_scan_tlist tuple's attributes 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 attribute number of the corresponding attribute of
+ * the fdw_scan_tlist tuple. We store zero for any attributes that won't
+ * be retrieved from the remote server, marking that a NULL is needed in
+ * the output tuple. We also get the attribute number of the ctid of the
+ * updated/deleted tuple, if any.
+ */
+ dpstate->attnoMap = (AttrNumber *) palloc0(resultTupType->natts * sizeof(AttrNumber));
+
+ dpstate->ctidAttno = 0;
+
+ forboth(lc, dpstate->result_attrs, lc2, dpstate->result_attrnos)
+ {
+ int attr = lfirst_int(lc);
+ int attrno = lfirst_int(lc2);
+
+ if (attrno == SelfItemPointerAttributeNumber)
+ dpstate->ctidAttno = attr;
+ else
+ {
+ Assert(attrno > 0);
+ dpstate->attnoMap[attrno - 1] = attr;
+ }
+ }
+ }
+
+ /*
+ * Extract and return an updated/deleted tuple from a given fdw_scan_tlist
+ * tuple.
+ */
+ static TupleTableSlot *
+ execute_returning_filter(PgFdwDmlPushdownState *dpstate,
+ TupleTableSlot *slot)
+ {
+ TupleTableSlot *resultSlot = dpstate->resultSlot;
+ TupleDesc resultTupType = RelationGetDescr(dpstate->resultRel);
+ int i;
+ Datum *values;
+ bool *isnull;
+ Datum *old_values;
+ bool *old_isnull;
+
+ /*
+ * Extract all the values of the old 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 new tuple.
+ */
+ for (i = 0; i < resultTupType->natts; i++)
+ {
+ int j = dpstate->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 a CTID to return, install it in t_self.
+ */
+ if (dpstate->ctidAttno)
+ {
+ ItemPointer ctid = NULL;
+ HeapTuple resultTup;
+
+ ctid = (ItemPointer) DatumGetPointer(old_values[dpstate->ctidAttno - 1]);
+
+ resultTup = ExecMaterializeSlot(resultSlot);
+
+ resultTup->t_self = *ctid;
+ }
+
+ /*
+ * And return the result tuple.
+ */
+ return resultSlot;
+ }
+
+ /*
* Prepare for processing of parameters used in remote query.
*/
static void
***************
*** 4258,4268 **** 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));
--- 4551,4558 ----
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
***************
*** 132,153 **** extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
List **retrieved_attrs);
extern void deparsePushedDownUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetlist,
List *targetAttrs,
List *remote_conds,
List **params_list,
List *returningList,
! List **retrieved_attrs);
extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *returningList,
List **retrieved_attrs);
extern void deparsePushedDownDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *remote_conds,
List **params_list,
List *returningList,
! List **retrieved_attrs);
extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel);
extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
List **retrieved_attrs);
--- 132,163 ----
List **retrieved_attrs);
extern void deparsePushedDownUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List *fdw_scan_tlist,
+ Bitmapset *attrs_used,
List *targetlist,
List *targetAttrs,
List *remote_conds,
List **params_list,
List *returningList,
! List **retrieved_attrs,
! List **result_attrs,
! List **result_attrnos);
extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *returningList,
List **retrieved_attrs);
extern void deparsePushedDownDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
+ List *fdw_scan_tlist,
+ Bitmapset *attrs_used,
List *remote_conds,
List **params_list,
List *returningList,
! List **retrieved_attrs,
! List **result_attrs,
! List **result_attrnos);
extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel);
extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
List **retrieved_attrs);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 607,620 **** 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)
--- 607,620 ----
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
