On 2017/02/13 18:24, Rushabh Lathia wrote:
I started reviewing the patch again. Patch applied cleanly on latest source
as well as regression pass through with the patch. I also performed
few manual testing and haven't found any regression. Patch look
much cleaner the earlier version, and don't have any major concern as
such.
Thanks for the review!
Here are few comments:
1)
@@ -211,6 +211,12 @@ typedef struct PgFdwDirectModifyState
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 */
Why we need resultRel? Can't we directly use dmstate->rel ?
The reason why we need that is because in get_returning_data, we pass
dmstate->rel to make_tuple_from_result_row, which requires that
dmstate->rel be NULL when the scan tuple is described by fdw_scan_tlist.
So in that case we set dmstate->rel to NULL and have
dmstate->resultRel that is the relcache entry for the target relation in
postgresBeginDirectModify.
2) In the patch somewhere scanrelid condition being used as
fscan->scan.scanrelid == 0 where as some place its been used as
fsplan->scan.scanrelid > 0. Infact in the same function its been used
differently example postgresBeginDirectModify. Can make this consistent.
Ok, done.
3)
+ * If UPDATE/DELETE on a join, create a RETURINING list used in the
remote
+ * query.
+ */
+ if (fscan->scan.scanrelid == 0)
+ returningList = make_explicit_returning_list(resultRelation, rel,
+ returningList);
+
Above block can be moved inside the if (plan->returningLists) condition
above
the block. Like this:
/*
* Extract the relevant RETURNING list if any.
*/
if (plan->returningLists)
{
returningList = (List *) list_nth(plan->returningLists,
subplan_index);
/*
* If UPDATE/DELETE on a join, create a RETURINING list used in
the remote
* query.
*/
if (fscan->scan.scanrelid == 0)
returningList = make_explicit_returning_list(resultRelation,
rel,
returningList);
}
Done that way.
Another thing I noticed is duplicate work in apply_returning_filter; it
initializes tableoid of an updated/deleted tuple if needed, but the core
will do that (see ExecProcessReturning). I removed that work from
apply_returning_filter.
I am still doing few more testing with the patch, if I will found
anything apart from
this I will raise that into another mail.
Thanks again!
Attached is an updated version of the patch.
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,151 ----
Bitmapset *attrs_used,
bool qualify_col,
List **retrieved_attrs);
! static void deparseExplicitTargetList(List *tlist,
! bool is_returning,
! 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 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);
***************
*** 165,171 **** 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);
--- 174,181 ----
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
{
--- 1004,1010 ----
foreignrel->reloptkind == RELOPT_UPPER_REL)
{
/* For a join relation use the input tlist */
! deparseExplicitTargetList(tlist, false, 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)
--- 1047,1053 ----
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)
***************
*** 1302,1310 **** get_jointype_name(JoinType jointype)
*
* retrieved_attrs is the list of continuously increasing integers starting
* from 1. It has same number of entries as tlist.
*/
static void
! deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
deparse_expr_cxt *context)
{
ListCell *lc;
--- 1312,1325 ----
*
* retrieved_attrs is the list of continuously increasing integers starting
* from 1. It has same number of entries as tlist.
+ *
+ * This is used for both SELECT and RETURNING targetlists; the is_returning
+ * parameter is true only for a RETURNING targetlist.
*/
static void
! deparseExplicitTargetList(List *tlist,
! bool is_returning,
! 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");
}
--- 1337,1352 ----
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");
}
***************
*** 1338,1347 **** 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;
--- 1356,1372 ----
* 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.
+ *
+ * 'target_rel' is either zero or the rangetable index of a target relation.
+ * In the latter case this construncts FROM clause of UPDATE or USING clause
+ * of DELETE by simply ignoring the target relation while deparsing the given
+ * join tree. Note that it's safe to do that because the join of the target
+ * relation with any other relation is an inner join and can be interchanged
+ * with higher-level joins.
*/
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
--- 1376,1427 ----
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;
! else 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,
--- 1591,1597 ----
void
deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
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;
--- 1599,1604 ----
***************
*** 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 */
--- 1606,1620 ----
/* 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);
}
/*
--- 1641,1670 ----
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)
! deparseExplicitReturningList(returningList, 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)
{
--- 1699,1736 ----
void
deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
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);
}
/*
--- 1738,1748 ----
appendConditions(remote_conds, &context);
}
! if (foreignrel->reloptkind == RELOPT_JOINREL)
! deparseExplicitReturningList(returningList, retrieved_attrs, &context);
! else
! deparseReturningList(buf, root, rtindex, rel, false,
! returningList, retrieved_attrs);
}
/*
***************
*** 1683,1688 **** deparseReturningList(StringInfo buf, PlannerInfo *root,
--- 1782,1887 ----
}
/*
+ * 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(rlist, true, retrieved_attrs, context);
+ }
+
+ /*
+ * Look for conditions mentioning the target relation in the given join tree,
+ * which will be pulled up into the WHERE clause. Note that this is safe due
+ * to the same reason stated in comments in deparseFromExprForRel.
+ */
+ 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 (see foreign_join_ok). */
+ Assert(fpinfo->remote_conds == NIL);
+
+ /*
+ * If either input is the target relation, get all the joinclauses.
+ * Otherwise extract conditions mentioning the target relation 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);
+ else 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;
***************
*** 5046,5051 **** DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
--- 5018,5192 ----
ft2
(1 row)
+ INSERT INTO ft2 (c1,c2,c3)
+ SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(10001, 10100) id;
+ CREATE TABLE j1_tbl (c1 int NOT NULL, c2 text);
+ CREATE TABLE j2_tbl (c1 int NOT NULL, c2 text);
+ INSERT INTO j1_tbl SELECT id, to_char(id, 'FM00000') FROM generate_series(1, 100) id;
+ INSERT INTO j2_tbl SELECT id, to_char(id, 'FM00000') FROM generate_series(1, 100) id;
+ DELETE FROM j2_tbl WHERE c1 % 2 != 0;
+ CREATE FOREIGN TABLE j1_ftbl (c1 int NOT NULL, c2 text)
+ SERVER loopback OPTIONS (table_name 'j1_tbl');
+ CREATE FOREIGN TABLE j2_ftbl (c1 int NOT NULL, c2 text)
+ SERVER loopback OPTIONS (table_name 'j2_tbl');
+ ANALYZE j1_ftbl;
+ ANALYZE j2_ftbl;
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c2 = ft2.c2 + 200, c3 = ft2.c3 || '_update2'
+ FROM j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING ft2.*;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8
+ -> Foreign Update
+ Remote SQL: UPDATE "S 1"."T 1" r1 SET c2 = (r1.c2 + 200), c3 = (r1.c3 || '_update2'::text) FROM (public.j1_tbl r2 INNER JOIN public.j2_tbl r3 ON (((r2.c1 = r3.c1)))) WHERE ((r1."C 1" = (r2.c1 + 10000))) AND (((r1."C 1" % 10) = 2)) RETURNING r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8
+ (4 rows)
+
+ UPDATE ft2 SET c2 = ft2.c2 + 200, c3 = ft2.c3 || '_update2'
+ FROM j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING ft2.*;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+ -------+-----+---------------+----+----+----+------------+----
+ 10002 | 202 | 10002_update2 | | | | ft2 |
+ 10012 | 202 | 10012_update2 | | | | ft2 |
+ 10022 | 202 | 10022_update2 | | | | ft2 |
+ 10032 | 202 | 10032_update2 | | | | ft2 |
+ 10042 | 202 | 10042_update2 | | | | ft2 |
+ 10052 | 202 | 10052_update2 | | | | ft2 |
+ 10062 | 202 | 10062_update2 | | | | ft2 |
+ 10072 | 202 | 10072_update2 | | | | ft2 |
+ 10082 | 202 | 10082_update2 | | | | ft2 |
+ 10092 | 202 | 10092_update2 | | | | ft2 |
+ (10 rows)
+
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c2 = ft2.c2 + 800, c3 = ft2.c3 || '_update8'
+ FROM ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2.*, j1_ftbl.*, j2_ftbl.*;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, j1_ftbl.c1, j1_ftbl.c2, j2_ftbl.c1, j2_ftbl.c2
+ -> Foreign Update
+ Remote SQL: UPDATE "S 1"."T 1" r1 SET c2 = (r1.c2 + 800), c3 = (r1.c3 || '_update8'::text) FROM (("S 1"."T 1" r2 INNER JOIN public.j1_tbl r3 ON (((r2."C 1" = (r3.c1 + 10000))))) LEFT JOIN public.j2_tbl r4 ON (((r3.c1 = r4.c1)))) WHERE ((r1."C 1" = r2."C 1")) AND (((r1."C 1" % 10) = 8)) RETURNING r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r3.c1, r3.c2, r4.c1, r4.c2
+ (4 rows)
+
+ UPDATE ft2 SET c2 = ft2.c2 + 800, c3 = ft2.c3 || '_update8'
+ FROM ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2.*, j1_ftbl.*, j2_ftbl.*;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c1 | c2
+ -------+-----+---------------+----+----+----+------------+----+----+-------+----+-------
+ 10008 | 808 | 10008_update8 | | | | ft2 | | 8 | 00008 | 8 | 00008
+ 10018 | 808 | 10018_update8 | | | | ft2 | | 18 | 00018 | 18 | 00018
+ 10028 | 808 | 10028_update8 | | | | ft2 | | 28 | 00028 | 28 | 00028
+ 10038 | 808 | 10038_update8 | | | | ft2 | | 38 | 00038 | 38 | 00038
+ 10048 | 808 | 10048_update8 | | | | ft2 | | 48 | 00048 | 48 | 00048
+ 10058 | 808 | 10058_update8 | | | | ft2 | | 58 | 00058 | 58 | 00058
+ 10068 | 808 | 10068_update8 | | | | ft2 | | 68 | 00068 | 68 | 00068
+ 10078 | 808 | 10078_update8 | | | | ft2 | | 78 | 00078 | 78 | 00078
+ 10088 | 808 | 10088_update8 | | | | ft2 | | 88 | 00088 | 88 | 00088
+ 10098 | 808 | 10098_update8 | | | | ft2 | | 98 | 00098 | 98 | 00098
+ (10 rows)
+
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2
+ USING j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING 100;
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Delete on public.ft2
+ Output: 100
+ -> Foreign Delete
+ Remote SQL: DELETE FROM "S 1"."T 1" r1 USING (public.j1_tbl r2 INNER JOIN public.j2_tbl r3 ON (((r2.c1 = r3.c1)))) WHERE ((r1."C 1" = (r2.c1 + 10000))) AND (((r1."C 1" % 10) = 2))
+ (4 rows)
+
+ DELETE FROM ft2
+ USING j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING 100;
+ ?column?
+ ----------
+ 100
+ 100
+ 100
+ 100
+ 100
+ 100
+ 100
+ 100
+ 100
+ 100
+ (10 rows)
+
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2
+ USING j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 0
+ RETURNING ft2.ctid, ft2.c1, ft2.c2, ft2.c3, j1_ftbl.ctid, j1_ftbl.*, j2_ftbl.ctid, j2_ftbl.*;
+ QUERY PLAN
+ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Delete on public.ft2
+ Output: ft2.ctid, ft2.c1, ft2.c2, ft2.c3, j1_ftbl.ctid, j1_ftbl.c1, j1_ftbl.c2, j2_ftbl.ctid, j2_ftbl.c1, j2_ftbl.c2
+ -> Foreign Delete
+ Remote SQL: DELETE FROM "S 1"."T 1" r1 USING (public.j1_tbl r2 LEFT JOIN public.j2_tbl r3 ON (((r2.c1 = r3.c1)))) WHERE ((r1."C 1" = (r2.c1 + 10000))) AND (((r1."C 1" % 10) = 0)) RETURNING r1.ctid, r1."C 1", r1.c2, r1.c3, r2.ctid, r2.c1, r2.c2, r3.ctid, r3.c1, r3.c2
+ (4 rows)
+
+ DELETE FROM ft2
+ USING j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 0
+ RETURNING ft2.ctid, ft2.c1, ft2.c2, ft2.c3, j1_ftbl.ctid, j1_ftbl.*, j2_ftbl.ctid, j2_ftbl.*;
+ ctid | c1 | c2 | c3 | ctid | c1 | c2 | ctid | c1 | c2
+ ----------+-------+----+-------+---------+-----+-------+---------+-----+-------
+ (12,11) | 10010 | 0 | 10010 | (0,10) | 10 | 00010 | (0,10) | 10 | 00010
+ (12,21) | 10020 | 0 | 10020 | (0,20) | 20 | 00020 | (0,20) | 20 | 00020
+ (12,31) | 10030 | 0 | 10030 | (0,30) | 30 | 00030 | (0,30) | 30 | 00030
+ (12,41) | 10040 | 0 | 10040 | (0,40) | 40 | 00040 | (0,40) | 40 | 00040
+ (12,51) | 10050 | 0 | 10050 | (0,50) | 50 | 00050 | (0,50) | 50 | 00050
+ (12,61) | 10060 | 0 | 10060 | (0,60) | 60 | 00060 | (0,60) | 60 | 00060
+ (12,71) | 10070 | 0 | 10070 | (0,70) | 70 | 00070 | (0,70) | 70 | 00070
+ (12,81) | 10080 | 0 | 10080 | (0,80) | 80 | 00080 | (0,80) | 80 | 00080
+ (12,91) | 10090 | 0 | 10090 | (0,90) | 90 | 00090 | (0,90) | 90 | 00090
+ (12,101) | 10100 | 0 | 10100 | (0,100) | 100 | 00100 | (0,100) | 100 | 00100
+ (10 rows)
+
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2
+ USING ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2, ft2.*, j1_ftbl, j1_ftbl.*, j2_ftbl, j2_ftbl.*;
+ QUERY PLAN
+ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Delete on public.ft2
+ Output: ft2.*, ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, j1_ftbl.*, j1_ftbl.c1, j1_ftbl.c2, j2_ftbl.*, j2_ftbl.c1, j2_ftbl.c2
+ -> Foreign Delete
+ Remote SQL: DELETE FROM "S 1"."T 1" r1 USING (("S 1"."T 1" r2 INNER JOIN public.j1_tbl r3 ON (((r2."C 1" = (r3.c1 + 10000))))) LEFT JOIN public.j2_tbl r4 ON (((r3.c1 = r4.c1)))) WHERE ((r1."C 1" = r2."C 1")) AND (((r1."C 1" % 10) = 8)) RETURNING r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2) END, r3.c1, r3.c2, CASE WHEN (r4.*)::text IS NOT NULL THEN ROW(r4.c1, r4.c2) END, r4.c1, r4.c2
+ (4 rows)
+
+ DELETE FROM ft2
+ USING ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2, ft2.*, j1_ftbl, j1_ftbl.*, j2_ftbl, j2_ftbl.*;
+ ft2 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | j1_ftbl | c1 | c2 | j2_ftbl | c1 | c2
+ --------------------------------------------+-------+-----+---------------+----+----+----+------------+----+------------+----+-------+------------+----+-------
+ (10008,808,10008_update8,,,,"ft2 ",) | 10008 | 808 | 10008_update8 | | | | ft2 | | (8,00008) | 8 | 00008 | (8,00008) | 8 | 00008
+ (10018,808,10018_update8,,,,"ft2 ",) | 10018 | 808 | 10018_update8 | | | | ft2 | | (18,00018) | 18 | 00018 | (18,00018) | 18 | 00018
+ (10028,808,10028_update8,,,,"ft2 ",) | 10028 | 808 | 10028_update8 | | | | ft2 | | (28,00028) | 28 | 00028 | (28,00028) | 28 | 00028
+ (10038,808,10038_update8,,,,"ft2 ",) | 10038 | 808 | 10038_update8 | | | | ft2 | | (38,00038) | 38 | 00038 | (38,00038) | 38 | 00038
+ (10048,808,10048_update8,,,,"ft2 ",) | 10048 | 808 | 10048_update8 | | | | ft2 | | (48,00048) | 48 | 00048 | (48,00048) | 48 | 00048
+ (10058,808,10058_update8,,,,"ft2 ",) | 10058 | 808 | 10058_update8 | | | | ft2 | | (58,00058) | 58 | 00058 | (58,00058) | 58 | 00058
+ (10068,808,10068_update8,,,,"ft2 ",) | 10068 | 808 | 10068_update8 | | | | ft2 | | (68,00068) | 68 | 00068 | (68,00068) | 68 | 00068
+ (10078,808,10078_update8,,,,"ft2 ",) | 10078 | 808 | 10078_update8 | | | | ft2 | | (78,00078) | 78 | 00078 | (78,00078) | 78 | 00078
+ (10088,808,10088_update8,,,,"ft2 ",) | 10088 | 808 | 10088_update8 | | | | ft2 | | (88,00088) | 88 | 00088 | (88,00088) | 88 | 00088
+ (10098,808,10098_update8,,,,"ft2 ",) | 10098 | 808 | 10098_update8 | | | | ft2 | | (98,00098) | 98 | 00098 | (98,00098) | 98 | 00098
+ (10 rows)
+
+ DELETE FROM ft2 WHERE ft2.c1 > 10000;
-- Test that trigger on remote table works as expected
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
BEGIN
*** 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 tuple */
+ AttrNumber *attnoMap; /* array of attnums of input user columns */
+ AttrNumber ctidAttno; /* attnum of input ctid column */
+ AttrNumber oidAttno; /* attnum of input oid column */
+ bool hasSystemCols; /* are there system columns of resultRel? */
/* working memory context */
MemoryContext temp_cxt; /* context for per-tuple temporary data */
***************
*** 377,384 **** static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
--- 383,400 ----
TupleTableSlot *slot);
static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
+ static List *make_explicit_returning_list(Index rtindex, Relation rel,
+ List *returningList);
+ static List *rewrite_fdw_scan_tlist(List *fdw_scan_tlist, Index rtindex,
+ List *returningList);
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,
--- 2124,2130 ----
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.
*/
--- 2156,2161 ----
***************
*** 2184,2189 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2194,2201 ----
/*
* Ok, rewrite subplan so as to modify the foreign table directly.
*/
+ fscan = (ForeignScan *) subplan;
+
initStringInfo(&sql);
/*
***************
*** 2193,2198 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2205,2222 ----
rel = heap_open(rte->relid, NoLock);
/*
+ * Get a rel for this foreign table or join.
+ */
+ if (fscan->scan.scanrelid == 0)
+ {
+ foreignrel = find_join_rel(root, fscan->fs_relids);
+ /* We should have a rel for this foreign join. */
+ 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,
***************
*** 2202,2209 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2226,2244 ----
* Extract the relevant RETURNING list if any.
*/
if (plan->returningLists)
+ {
returningList = (List *) list_nth(plan->returningLists, subplan_index);
+ /*
+ * If UPDATE/DELETE on a join, create a RETURINING list used in the
+ * remote query.
+ */
+ if (fscan->scan.scanrelid == 0)
+ returningList = make_explicit_returning_list(resultRelation,
+ rel,
+ returningList);
+ }
+
/*
* Construct the SQL command string.
*/
***************
*** 2211,2216 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2246,2252 ----
{
case CMD_UPDATE:
deparseDirectUpdateSql(&sql, root, resultRelation, rel,
+ foreignrel,
((Plan *) fscan)->targetlist,
targetAttrs,
remote_conds, ¶ms_list,
***************
*** 2218,2223 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2254,2260 ----
break;
case CMD_DELETE:
deparseDirectDeleteSql(&sql, root, resultRelation, rel,
+ foreignrel,
remote_conds, ¶ms_list,
returningList, &retrieved_attrs);
break;
***************
*** 2245,2250 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2282,2302 ----
retrieved_attrs,
makeInteger(plan->canSetTag));
+ /*
+ * Update the foreign-join-related fields.
+ */
+ if (fscan->scan.scanrelid == 0)
+ {
+ /* No need for the outer subplan. */
+ fscan->scan.plan.lefttree = NULL;
+
+ /* If having RETURNING, rewrite fdw_scan_tlist to include it. */
+ if (returningList)
+ fscan->fdw_scan_tlist =
+ rewrite_fdw_scan_tlist(fscan->fdw_scan_tlist, resultRelation,
+ returningList);
+ }
+
heap_close(rel, NoLock);
return true;
}
***************
*** 2259,2264 **** postgresBeginDirectModify(ForeignScanState *node, int eflags)
--- 2311,2317 ----
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);
--- 2334,2348 ----
* 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 = ExecOpenScanRelation(estate, rtindex, eflags);
! else
! dmstate->rel = node->ss.ss_currentRelation;
table = GetForeignTable(RelationGetRelid(dmstate->rel));
user = GetUserMapping(userid, table->serverid);
***************
*** 2295,2300 **** postgresBeginDirectModify(ForeignScanState *node, int eflags)
--- 2352,2367 ----
*/
dmstate->conn = GetConnection(user, false);
+ /* Update the foreign-join-related fields. */
+ if (fsplan->scan.scanrelid == 0)
+ {
+ /* Save info about target table. */
+ dmstate->resultRel = dmstate->rel;
+
+ /* rel should be NULL. */
+ 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.
--- 2382,2405 ----
/* Prepare for input conversion of RETURNING results. */
if (dmstate->has_returning)
! {
! TupleDesc tupdesc;
!
! if (fsplan->scan.scanrelid == 0)
! tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
! else
! tupdesc = RelationGetDescr(dmstate->rel);
!
! dmstate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
!
! /*
! * If UPDATE/DELETE on a join, initialize a filter to extract a result
! * tuple from a scan 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)
--- 2480,2489 ----
ReleaseConnection(dmstate->conn);
dmstate->conn = NULL;
+ /* close the result relation. */
+ if (dmstate->resultRel)
+ ExecCloseScanRelation(dmstate->resultRel);
+
/* MemoryContext will be deleted automatically. */
}
***************
*** 3264,3269 **** store_returning_result(PgFdwModifyState *fmstate,
--- 3352,3479 ----
}
/*
+ * Create an explicit RETURNING list used in the remote query.
+ */
+ static List *
+ make_explicit_returning_list(Index rtindex, Relation rel, List *returningList)
+ {
+ 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 whole-row references to the target relation. We don't
+ * need system columns other than ctid and oid either, 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));
+ }
+
+ list_free(vars);
+
+ return rlist;
+ }
+
+ /*
+ * Rewrite the given fdw_scan_tlist so it contains all the expressions
+ * specified in the RETURNING list.
+ */
+ static List *
+ rewrite_fdw_scan_tlist(List *fdw_scan_tlist, Index rtindex,
+ List *returningList)
+ {
+ List *tlist = list_copy(returningList);
+ ListCell *lc;
+
+ 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));
+ }
+
+ return tlist;
+ }
+
+ /*
* Execute a direct UPDATE/DELETE statement.
*/
static void
***************
*** 3323,3328 **** get_returning_data(ForeignScanState *node)
--- 3533,3539 ----
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)
--- 3551,3560 ----
* "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);
}
--- 3570,3576 ----
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
--- 3581,3777 ----
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 the result tuple. */
+ dmstate->resultSlot = ExecInitExtraTupleSlot(estate);
+
+ ExecSetSlotDescriptor(dmstate->resultSlot, resultTupType);
+
+ /*
+ * Calculate the mapping between the fdw_scan_tlist's entries and the
+ * result tuple's attributes.
+ *
+ * The "map" is an array of indexes of the result tuple's attributes in
+ * fdw_scan_tlist, i.e., one entry for every attribute of the result
+ * tuple. We store zero for any attributes that don't have the
+ * corresponding entries in that list, marking that a NULL is needed in
+ * the result tuple.
+ *
+ * Also get the indexes of the entries for ctid and oid 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)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Var *var = (Var *) tle->expr;
+
+ if (list_member_int(dmstate->retrieved_attrs, i))
+ {
+ if (IsA(var, Var) && var->varno == rtindex)
+ {
+ int attrno = var->varattno;
+
+ if (attrno < 0)
+ {
+ /*
+ * We don't retrieve system columns other than ctid and
+ * oid.
+ */
+ if (attrno == SelfItemPointerAttributeNumber)
+ dmstate->ctidAttno = i;
+ else if (attrno == ObjectIdAttributeNumber)
+ dmstate->oidAttno = i;
+ else
+ Assert(false);
+ dmstate->hasSystemCols = true;
+ }
+ else
+ {
+ /*
+ * We don't retrieve whole-row references to the result
+ * relation either.
+ */
+ 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);
+ 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 the 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 tuple.
+ */
+ ExecStoreVirtualTuple(resultSlot);
+
+ /*
+ * If we have any system columns to return, install them.
+ */
+ if (dmstate->hasSystemCols)
+ {
+ HeapTuple 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);
+ }
+
+ /*
+ * And remaining columns
+ *
+ * Note: since we currently don't allow the result relation to appear
+ * on the nullable side of an outer join, any system columns wouldn't
+ * go to NULL.
+ *
+ * Note: no need to care about tableoid here because it will be
+ * initialized in ExecProcessReturning().
+ */
+ HeapTupleHeaderSetXmin(resultTup->t_data, InvalidTransactionId);
+ HeapTupleHeaderSetXmax(resultTup->t_data, InvalidTransactionId);
+ HeapTupleHeaderSetCmin(resultTup->t_data, InvalidTransactionId);
+ }
+
+ /*
+ * 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));
--- 5191,5198 ----
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,143 ----
List **retrieved_attrs);
extern void deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
List *targetlist,
List *targetAttrs,
List *remote_conds,
***************
*** 149,154 **** extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root,
--- 150,156 ----
List **retrieved_attrs);
extern void deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
+ RelOptInfo *foreignrel,
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)
***************
*** 1007,1012 **** EXPLAIN (verbose, costs off)
--- 1007,1072 ----
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
+ INSERT INTO ft2 (c1,c2,c3)
+ SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(10001, 10100) id;
+ CREATE TABLE j1_tbl (c1 int NOT NULL, c2 text);
+ CREATE TABLE j2_tbl (c1 int NOT NULL, c2 text);
+ INSERT INTO j1_tbl SELECT id, to_char(id, 'FM00000') FROM generate_series(1, 100) id;
+ INSERT INTO j2_tbl SELECT id, to_char(id, 'FM00000') FROM generate_series(1, 100) id;
+ DELETE FROM j2_tbl WHERE c1 % 2 != 0;
+ CREATE FOREIGN TABLE j1_ftbl (c1 int NOT NULL, c2 text)
+ SERVER loopback OPTIONS (table_name 'j1_tbl');
+ CREATE FOREIGN TABLE j2_ftbl (c1 int NOT NULL, c2 text)
+ SERVER loopback OPTIONS (table_name 'j2_tbl');
+ ANALYZE j1_ftbl;
+ ANALYZE j2_ftbl;
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c2 = ft2.c2 + 200, c3 = ft2.c3 || '_update2'
+ FROM j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING ft2.*;
+ UPDATE ft2 SET c2 = ft2.c2 + 200, c3 = ft2.c3 || '_update2'
+ FROM j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING ft2.*;
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c2 = ft2.c2 + 800, c3 = ft2.c3 || '_update8'
+ FROM ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2.*, j1_ftbl.*, j2_ftbl.*;
+ UPDATE ft2 SET c2 = ft2.c2 + 800, c3 = ft2.c3 || '_update8'
+ FROM ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2.*, j1_ftbl.*, j2_ftbl.*;
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2
+ USING j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING 100;
+ DELETE FROM ft2
+ USING j1_ftbl INNER JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 2
+ RETURNING 100;
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2
+ USING j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 0
+ RETURNING ft2.ctid, ft2.c1, ft2.c2, ft2.c3, j1_ftbl.ctid, j1_ftbl.*, j2_ftbl.ctid, j2_ftbl.*;
+ DELETE FROM ft2
+ USING j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)
+ WHERE ft2.c1 = j1_ftbl.c1 + 10000 AND ft2.c1 % 10 = 0
+ RETURNING ft2.ctid, ft2.c1, ft2.c2, ft2.c3, j1_ftbl.ctid, j1_ftbl.*, j2_ftbl.ctid, j2_ftbl.*;
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2
+ USING ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2, ft2.*, j1_ftbl, j1_ftbl.*, j2_ftbl, j2_ftbl.*;
+ DELETE FROM ft2
+ USING ft1 INNER JOIN (j1_ftbl LEFT JOIN j2_ftbl ON (j1_ftbl.c1 = j2_ftbl.c1)) ON (ft1.c1 = j1_ftbl.c1 + 10000)
+ WHERE ft2.c1 = ft1.c1 AND ft2.c1 % 10 = 8
+ RETURNING ft2, ft2.*, j1_ftbl, j1_ftbl.*, j2_ftbl, j2_ftbl.*;
+ DELETE FROM ft2 WHERE ft2.c1 > 10000;
+
-- Test that trigger on remote table works as expected
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
BEGIN
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
