On 2016/11/30 17:29, Etsuro Fujita wrote:
On 2016/11/23 20:28, Rushabh Lathia wrote:
I wrote:
How about inserting that before the
out param **retrieved_attrs, like this?
static void
deparseExplicitTargetList(List *tlist,
bool is_returning,
List **retrieved_attrs,
deparse_expr_cxt *context);
Yes, adding it before retrieved_attrs would be good.
OK, will do.
Done.
You wrote:
5) make_explicit_returning_list() pull the var list from the
returningList and
build the targetentry for the returning list and at the end
rewrites the
fdw_scan_tlist.
AFAIK, in case of DML - which is going to pushdown to the remote
server
ideally fdw_scan_tlist should be same as returning list, as
final output
for the query is query will be RETUNING list only. isn't that
true?
I wrote:
That would be true. But the fdw_scan_tlist passed from the core
would contain junk columns inserted by the rewriter and planner
work, such as CTID for the target table and whole-row Vars for other
tables specified in the FROM clause of an UPDATE or the USING clause
of a DELETE. So, I created the patch so that the pushed-down
UPDATE/DELETE retrieves only the data needed for the RETURNING
calculation from the remote server, not the whole fdw_scan_tlist
data.
Yes, I noticed that fdw_scan_tlist have CTID for the target table and
whole-raw vars for
other tables specified in the FROM clause of the DML. But I was thinking
isn't it possible
to create new fdw_scan_tlist once we found that DML is direct pushable
to foreign server?
I tried quickly doing that - but later its was throwing "variable not
found in subplan target list"
from set_foreignscan_references().
We could probably avoid that error by replacing the targetlist of the
subplan with fdw_scan_tlist, but that wouldn't be enough ...
You wrote:
If yes, then why can't we directly replace the fdw_scan_tlist
with the
returning
list, rather then make_explicit_returning_list()?
I wrote:
I think we could do that if we modified the core (maybe the executor
part). I'm not sure that's a good idea, though.
Yes modifying core is not good idea. But just want to understand why you
think that this need need to modify core?
Sorry, I don't remember that. Will check.
The reason why I think so is that the ModifyTable node on top of the
ForeignScan node requires that the targetlist of the ForeignScan has (1)
junk whole-row Vars for secondary relations in UPDATE/DELETE and (2) all
attributes of the target relation to produce the new tuple for UPDATE.
(So, it wouldn't be enough to just replace the ForeignScan's targetlist
with fdw_scan_tlist!) For #1, see this (and the following code) in
ExecInitModifyTable:
/*
* If we have any secondary relations in an UPDATE or DELETE, they
need to
* be treated like non-locked relations in SELECT FOR UPDATE, ie, the
* EvalPlanQual mechanism needs to be told about them. Locate the
* relevant ExecRowMarks.
*/
And for #2, see this (and the following code, especially where calling
ExecCheckPlanOutput) in the same function:
* This section of code is also a convenient place to verify that the
* output of an INSERT or UPDATE matches the target table(s).
What you proposed would be a good idea because the FDW could calculate
the user-query RETURNING list more efficiently in some cases, but I'd
like to leave that for future work.
Attached is the new version of the patch. I also addressed other
comments from you: moved rewriting the fdw_scan_tlist to postgres_fdw.c,
added/revised comments, and added regression tests for the case where a
pushed down UPDATE/DELETE on a join has RETURNING.
My apologies for having been late to work on this.
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 should be an inner join and hence 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,
***************
*** 2205,2216 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2229,2249 ----
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.
*/
switch (operation)
{
case CMD_UPDATE:
deparseDirectUpdateSql(&sql, root, resultRelation, rel,
+ foreignrel,
((Plan *) fscan)->targetlist,
targetAttrs,
remote_conds, ¶ms_list,
***************
*** 2218,2223 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2251,2257 ----
break;
case CMD_DELETE:
deparseDirectDeleteSql(&sql, root, resultRelation, rel,
+ foreignrel,
remote_conds, ¶ms_list,
returningList, &retrieved_attrs);
break;
***************
*** 2245,2250 **** postgresPlanDirectModify(PlannerInfo *root,
--- 2279,2299 ----
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)
--- 2308,2314 ----
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);
--- 2331,2345 ----
* Identify which user to do the remote access as. This should match what
* ExecCheckRTEPerms() does.
*/
! rtindex = estate->es_result_relation_info->ri_RangeTableIndex;
! rte = rt_fetch(rtindex, estate->es_range_table);
userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
/* Get info about foreign table. */
! if (fsplan->scan.scanrelid > 0)
! dmstate->rel = node->ss.ss_currentRelation;
! else
! dmstate->rel = ExecOpenScanRelation(estate, rtindex, eflags);
table = GetForeignTable(RelationGetRelid(dmstate->rel));
user = GetUserMapping(userid, table->serverid);
***************
*** 2295,2300 **** postgresBeginDirectModify(ForeignScanState *node, int eflags)
--- 2349,2364 ----
*/
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.
--- 2379,2402 ----
/* Prepare for input conversion of RETURNING results. */
if (dmstate->has_returning)
! {
! TupleDesc tupdesc;
!
! if (fsplan->scan.scanrelid > 0)
! tupdesc = RelationGetDescr(dmstate->rel);
! else
! tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
!
! dmstate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
!
! /*
! * 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)
--- 2477,2486 ----
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,
--- 3349,3476 ----
}
/*
+ * 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)
--- 3530,3536 ----
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)
--- 3548,3557 ----
* "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);
}
--- 3567,3573 ----
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
--- 3578,3772 ----
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);
+ HeapTuple resultTup;
+ Datum *values;
+ bool *isnull;
+ Datum *old_values;
+ bool *old_isnull;
+ int i;
+
+ /*
+ * Extract all the values of the scan tuple.
+ */
+ slot_getallattrs(slot);
+ old_values = slot->tts_values;
+ old_isnull = slot->tts_isnull;
+
+ /*
+ * Prepare to build 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)
+ {
+ 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);
+ }
+
+ /*
+ * remaining columns (note that system columns should not go to NULL
+ * because we currently don't allow the result relation to appear on
+ * on the nullable side of an outer join.)
+ */
+ HeapTupleHeaderSetXmin(resultTup->t_data, InvalidTransactionId);
+ HeapTupleHeaderSetXmax(resultTup->t_data, InvalidTransactionId);
+ HeapTupleHeaderSetCmin(resultTup->t_data, InvalidTransactionId);
+
+ resultTup->t_tableOid = RelationGetRelid(dmstate->resultRel);
+ }
+
+ /*
+ * And return the result tuple.
+ */
+ return resultSlot;
+ }
+
+ /*
* Prepare for processing of parameters used in remote query.
*/
static void
***************
*** 4796,4806 **** make_tuple_from_result_row(PGresult *res,
tupdesc = RelationGetDescr(rel);
else
{
- PgFdwScanState *fdw_sstate;
-
Assert(fsstate);
! fdw_sstate = (PgFdwScanState *) fsstate->fdw_state;
! tupdesc = fdw_sstate->tupdesc;
}
values = (Datum *) palloc0(tupdesc->natts * sizeof(Datum));
--- 5186,5193 ----
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
