(2018/08/24 16:58), Kyotaro HORIGUCHI wrote:
At Tue, 21 Aug 2018 11:01:32 +0900 (Tokyo Standard Time), Kyotaro
HORIGUCHI<horiguchi.kyot...@lab.ntt.co.jp> wrote
in<20180821.110132.261184472.horiguchi.kyot...@lab.ntt.co.jp>
You wrote:
Several places seems to be assuming that fdw_scan_tlist may be
used foreign scan on simple relation but I didn't find that
actually happens.
Yeah, currently, postgres_fdw and file_fdw don't use that list for
simple foreign table scans, but it could be used to improve the
efficiency for those scans, as explained in fdwhandler.sgml:
...
I'll put more consideration on using fdw_scan_tlist in the
documented way.
Done. postgres_fdw now generates full fdw_scan_tlist (as
documented) for foreign relations with junk columns having a
small change in core side. However it is far less invasive than
the previous version and I believe that it dones't harm
maybe-existing use of fdw_scan_tlist on non-join rels (that is,
in the case of a subset of relation columns).
Yeah, changes to the core by the new version is really small, which is
great, but I'm not sure it's a good idea to modify the catalog info on
the target table on the fly:
@@ -126,8 +173,18 @@ get_relation_info(PlannerInfo *root, Oid
relationObjectId,\
bool inhparent,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary or unlogged relations
during r\
ecovery")));
+ max_attrnum = RelationGetNumberOfAttributes(relation);
+
+ /* Foreign table may have exanded this relation with junk columns */
+ if (root->simple_rte_array[varno]->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ AttrNumber maxattno = max_varattno(root->parse->targetList, varno);
+ if (max_attrnum < maxattno)
+ max_attrnum = maxattno;
+ }
+
rel->min_attr = FirstLowInvalidHeapAttributeNumber + 1;
- rel->max_attr = RelationGetNumberOfAttributes(relation);
+ rel->max_attr = max_attrnum;
rel->reltablespace = RelationGetForm(relation)->reltablespace;
This breaks the fundamental assumption that rel->max_attr is equal to
RelationGetNumberOfAttributes of that table. My concern is: this change
would probably be a wart, so it would be bug-prone in future versions.
Another thing on the new version:
@@ -1575,6 +1632,19 @@ build_physical_tlist(PlannerInfo *root,
RelOptInfo *rel)
relation = heap_open(rte->relid, NoLock);
numattrs = RelationGetNumberOfAttributes(relation);
+
+ /*
+ * Foreign tables may have expanded with some junk columns. Punt
+ * in the case.
+ */
+ if (numattrs < rel->max_attr)
+ {
+ Assert(root->simple_rte_array[rel->relid]->relkind ==
+ RELKIND_FOREIGN_TABLE);
+ heap_close(relation, NoLock);
+ break;
+ }
I think this would disable the optimization on projection in foreign
scans, causing performance regression.
One arguable behavior change is about wholrow vars. Currently it
refferes local tuple with all columns but it is explicitly
fetched as ROW() after this patch applied. This could be fixed
but not just now.
Part of 0004-:
- Output: f1, ''::text, ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: f1, ''::text, tableoid, ctid, rem1.*
+ Remote SQL: SELECT f1, tableoid, ctid, ROW(f1, f2) FROM public.loc1 FOR
UPDATE
That would be also performance regression. If we go in this direction,
that should be fixed.
Since this uses fdw_scan_tlist so it is theoretically
back-patchable back to 9.6.
IIRC, the fdw_scan_tlist stuff was introduced in PG9.5 as part of join
pushdown infrastructure, so I think your patch can be back-patched to
PG9.5, but I don't think that's enough; IIRC, this issue was introduced
in PG9.3, so a solution for this should be back-patch-able to PG9.3, I
think.
Please find the attached three files.
Thanks for the patches!
0001-Add-test-for-postgres_fdw-foreign-parition-update.patch
This should fail for unpatched postgres_fdw. (Just for demonstration)
+CREATE TABLE p1 (a int, b int);
+CREATE TABLE c1 (LIKE p1) INHERITS (p1);
+CREATE TABLE c2 (LIKE p1) INHERITS (p1);
+CREATE FOREIGN TABLE fp1 (a int, b int)
+ SERVER loopback OPTIONS (table_name 'p1');
+INSERT INTO c1 VALUES (0, 1);
+INSERT INTO c2 VALUES (1, 1);
+SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS
toiddiff, ctid, * FROM fp1;
Does it make sense to evaluate toiddiff? I think that should always be 0.
0003-Fix-of-foreign-update-bug-of-PgFDW.patch
Fix of postgres_fdw for this problem.
Sorry, I have not looked at it closely yet, but before that I'd like to
discuss the direction we go in. I'm not convinced that your approach is
the right direction, so as promised, I wrote a patch using the
Param-based approach, and compared the two approaches. Attached is a
WIP patch for that, which includes the 0003 patch. I don't think there
would be any warts as discussed above in the Param-based approach for
now. (That approach modifies the planner so that the targetrel's tlist
would contain Params as well as Vars/PHVs, so actually, it breaks the
planner assumption that a rel's tlist would only include Vars/PHVs, but
I don't find any issues on that at least for now. Will look into that
in more detail.) And I don't think there would be any concern about
performance regression, either. Maybe I'm missing something, though.
What do you think about that?
Note about the attached: I tried to invent a utility for
generate_new_param like SS_make_initplan_output_param as mentioned in
[1], but since the FDW API doesn't pass PlannerInfo to the FDW, I think
the FDW can't call the utility the same way. Instead, I modified the
planner so that 1) the FDW adds Params without setting PARAM_EXEC Param
IDs using a new function, and then 2) the core fixes the IDs.
Sorry for the delay.
Best regards,
Etsuro Fujita
[1] https://www.postgresql.org/message-id/3919.1527775582%40sss.pgh.pa.us
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 130,135 **** static void deparseTargetList(StringInfo buf,
--- 130,136 ----
Relation rel,
bool is_returning,
Bitmapset *attrs_used,
+ bool tableoid_needed,
bool qualify_col,
List **retrieved_attrs);
static void deparseExplicitTargetList(List *tlist,
***************
*** 901,906 **** build_tlist_to_deparse(RelOptInfo *foreignrel)
--- 902,919 ----
PVC_RECURSE_PLACEHOLDERS));
}
+ /* We also require a PARAM_EXEC Param, if it exists. */
+ if (fpinfo->tableoid_param)
+ {
+ TargetEntry *tle;
+
+ tle = makeTargetEntry((Expr *) copyObject(fpinfo->tableoid_param),
+ list_length(tlist) + 1,
+ NULL,
+ false);
+ tlist = lappend(tlist, tle);
+ }
+
return tlist;
}
***************
*** 1052,1058 **** deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs,
Relation rel = heap_open(rte->relid, NoLock);
deparseTargetList(buf, rte, foreignrel->relid, rel, false,
! fpinfo->attrs_used, false, retrieved_attrs);
heap_close(rel, NoLock);
}
}
--- 1065,1073 ----
Relation rel = heap_open(rte->relid, NoLock);
deparseTargetList(buf, rte, foreignrel->relid, rel, false,
! fpinfo->attrs_used,
! fpinfo->tableoid_param ? true : false,
! false, retrieved_attrs);
heap_close(rel, NoLock);
}
}
***************
*** 1093,1098 **** deparseFromExpr(List *quals, deparse_expr_cxt *context)
--- 1108,1115 ----
* This is used for both SELECT and RETURNING targetlists; the is_returning
* parameter is true only for a RETURNING targetlist.
*
+ * For SELECT, the target list contains remote tableoid if tableoid_needed.
+ *
* The tlist text is appended to buf, and we also create an integer List
* of the columns being retrieved, which is returned to *retrieved_attrs.
*
***************
*** 1105,1110 **** deparseTargetList(StringInfo buf,
--- 1122,1128 ----
Relation rel,
bool is_returning,
Bitmapset *attrs_used,
+ bool tableoid_needed,
bool qualify_col,
List **retrieved_attrs)
{
***************
*** 1146,1152 **** deparseTargetList(StringInfo buf,
/*
* Add ctid and oid if needed. We currently don't support retrieving any
! * other system columns.
*/
if (bms_is_member(SelfItemPointerAttributeNumber - FirstLowInvalidHeapAttributeNumber,
attrs_used))
--- 1164,1170 ----
/*
* Add ctid and oid if needed. We currently don't support retrieving any
! * other system columns, except tableoid, which is retrieved if required.
*/
if (bms_is_member(SelfItemPointerAttributeNumber - FirstLowInvalidHeapAttributeNumber,
attrs_used))
***************
*** 1180,1185 **** deparseTargetList(StringInfo buf,
--- 1198,1218 ----
*retrieved_attrs = lappend_int(*retrieved_attrs,
ObjectIdAttributeNumber);
}
+ if (tableoid_needed)
+ {
+ Assert(!is_returning);
+ Assert(!qualify_col);
+ Assert(bms_is_member(SelfItemPointerAttributeNumber - FirstLowInvalidHeapAttributeNumber,
+ attrs_used));
+
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+ appendStringInfoString(buf, "tableoid");
+
+ *retrieved_attrs = lappend_int(*retrieved_attrs,
+ TableOidAttributeNumber);
+ }
/* Don't generate bad syntax if no undropped columns */
if (first && !is_returning)
***************
*** 1728,1734 **** deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
deparseRelation(buf, rel);
appendStringInfoString(buf, " SET ");
! pindex = 2; /* ctid is always the first param */
first = true;
foreach(lc, targetAttrs)
{
--- 1761,1768 ----
deparseRelation(buf, rel);
appendStringInfoString(buf, " SET ");
! pindex = 3; /* ctid and tableoid are always the two
! * leading params */
first = true;
foreach(lc, targetAttrs)
{
***************
*** 1742,1748 **** deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
appendStringInfo(buf, " = $%d", pindex);
pindex++;
}
! appendStringInfoString(buf, " WHERE ctid = $1");
deparseReturningList(buf, rte, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_update_after_row,
--- 1776,1782 ----
appendStringInfo(buf, " = $%d", pindex);
pindex++;
}
! appendStringInfoString(buf, " WHERE ctid = $1 AND tableoid = $2");
deparseReturningList(buf, rte, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_update_after_row,
***************
*** 1858,1864 **** deparseDeleteSql(StringInfo buf, RangeTblEntry *rte,
{
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
! appendStringInfoString(buf, " WHERE ctid = $1");
deparseReturningList(buf, rte, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_delete_after_row,
--- 1892,1898 ----
{
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
! appendStringInfoString(buf, " WHERE ctid = $1 AND tableoid = $2");
deparseReturningList(buf, rte, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_delete_after_row,
***************
*** 1974,1980 **** deparseReturningList(StringInfo buf, RangeTblEntry *rte,
if (attrs_used != NULL)
deparseTargetList(buf, rte, rtindex, rel, true, attrs_used, false,
! retrieved_attrs);
else
*retrieved_attrs = NIL;
}
--- 2008,2014 ----
if (attrs_used != NULL)
deparseTargetList(buf, rte, rtindex, rel, true, attrs_used, false,
! false, retrieved_attrs);
else
*retrieved_attrs = NIL;
}
***************
*** 2147,2154 **** deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
}
appendStringInfoString(buf, "ROW(");
! deparseTargetList(buf, rte, varno, rel, false, attrs_used, qualify_col,
! &retrieved_attrs);
appendStringInfoChar(buf, ')');
/* Complete the CASE WHEN statement started above. */
--- 2181,2188 ----
}
appendStringInfoString(buf, "ROW(");
! deparseTargetList(buf, rte, varno, rel, false, attrs_used, false,
! qualify_col, &retrieved_attrs);
appendStringInfoChar(buf, ')');
/* Complete the CASE WHEN statement started above. */
***************
*** 2514,2519 **** deparseConst(Const *node, deparse_expr_cxt *context, int showtype)
--- 2548,2571 ----
static void
deparseParam(Param *node, deparse_expr_cxt *context)
{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
+
+ /*
+ * If the Param is the one saved in the fpinfo, which represents the
+ * remote table OID for a target relation, the value needs to be produced;
+ * fetch the remote table OID, instead.
+ */
+ if (equal(node, (Node *) fpinfo->tableoid_param))
+ {
+ Assert(context->root->parse->resultRelation);
+ Assert(bms_is_member(context->root->parse->resultRelation,
+ context->foreignrel->relids));
+ Assert(bms_membership(context->foreignrel->relids) == BMS_MULTIPLE);
+ ADD_REL_QUALIFIER(context->buf, context->root->parse->resultRelation);
+ appendStringInfoString(context->buf, "tableoid");
+ return;
+ }
+
if (context->params_list)
{
int pindex = 0;
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 5497,5511 **** INSERT INTO ft2 (c1,c2,c3)
SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8
! Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Foreign Scan on public.ft2
! Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid
Filter: (postgres_fdw_abs(ft2.c1) > 2000)
! Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE
(7 rows)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *;
--- 5497,5511 ----
SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8
! Remote SQL: UPDATE "S 1"."T 1" SET c3 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Foreign Scan on public.ft2
! Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid, $0
Filter: (postgres_fdw_abs(ft2.c1) > 2000)
! Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" FOR UPDATE
(7 rows)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *;
***************
*** 5532,5544 **** UPDATE ft2 SET c3 = 'baz'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
! Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Nested Loop
! Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Join Filter: (ft2.c2 === ft4.c1)
-> Foreign Scan on public.ft2
! Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
! Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3
Relations: (public.ft4) INNER JOIN (public.ft5)
--- 5532,5544 ----
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
! Remote SQL: UPDATE "S 1"."T 1" SET c3 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Nested Loop
! Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ($0), ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Join Filter: (ft2.c2 === ft4.c1)
-> Foreign Scan on public.ft2
! Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, $0
! Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3
Relations: (public.ft4) INNER JOIN (public.ft5)
***************
*** 5570,5593 **** DELETE FROM ft2
USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1
RETURNING ft2.c1, ft2.c2, ft2.c3; -- can't be pushed down
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3
! Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c2, c3
-> Foreign Scan
! Output: ft2.ctid, ft4.*, ft5.*
Filter: (ft4.c1 === ft5.c1)
Relations: ((public.ft2) INNER JOIN (public.ft4)) INNER JOIN (public.ft5)
! Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r2.c1, r3.c1 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 2000)))) INNER JOIN "S 1"."T 4" r3 ON (TRUE)) FOR UPDATE OF r1
-> Nested Loop
! Output: ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1
-> Nested Loop
! Output: ft2.ctid, ft4.*, ft4.c1
Join Filter: (ft2.c2 = ft4.c1)
-> Foreign Scan on public.ft2
! Output: ft2.ctid, ft2.c2
! Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan on public.ft4
Output: ft4.*, ft4.c1
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
--- 5570,5593 ----
USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1
RETURNING ft2.c1, ft2.c2, ft2.c3; -- can't be pushed down
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3
! Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3
-> Foreign Scan
! Output: ft2.ctid, ($0), ft4.*, ft5.*
Filter: (ft4.c1 === ft5.c1)
Relations: ((public.ft2) INNER JOIN (public.ft4)) INNER JOIN (public.ft5)
! Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r2.c1, r3.c1, r1.tableoid FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 2000)))) INNER JOIN "S 1"."T 4" r3 ON (TRUE)) FOR UPDATE OF r1
-> Nested Loop
! Output: ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1, ($0)
-> Nested Loop
! Output: ft2.ctid, ($0), ft4.*, ft4.c1
Join Filter: (ft2.c2 = ft4.c1)
-> Foreign Scan on public.ft2
! Output: ft2.ctid, $0, ft2.c2
! Remote SQL: SELECT c2, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan on public.ft4
Output: ft4.*, ft4.c1
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
***************
*** 6229,6241 **** SELECT * FROM foreign_tbl;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
! QUERY PLAN
! ---------------------------------------------------------------------------------------
Update on public.foreign_tbl
! Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid
! Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = b + 5; -- should fail
--- 6229,6241 ----
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
! QUERY PLAN
! --------------------------------------------------------------------------------------------------
Update on public.foreign_tbl
! Remote SQL: UPDATE public.base_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid, $0
! Remote SQL: SELECT a, b, ctid, tableoid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = b + 5; -- should fail
***************
*** 6243,6255 **** ERROR: new row violates check option for view "rw_view"
DETAIL: Failing row contains (20, 20).
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
! QUERY PLAN
! ---------------------------------------------------------------------------------------
Update on public.foreign_tbl
! Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid
! Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = b + 15; -- ok
--- 6243,6255 ----
DETAIL: Failing row contains (20, 20).
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
! QUERY PLAN
! --------------------------------------------------------------------------------------------------
Update on public.foreign_tbl
! Remote SQL: UPDATE public.base_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid, $0
! Remote SQL: SELECT a, b, ctid, tableoid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = b + 15; -- ok
***************
*** 6316,6329 **** SELECT * FROM foreign_tbl;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
! QUERY PLAN
! ----------------------------------------------------------------------------------------
Update on public.parent_tbl
Foreign Update on public.foreign_tbl
! Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid
! Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
UPDATE rw_view SET b = b + 5; -- should fail
--- 6316,6329 ----
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------
Update on public.parent_tbl
Foreign Update on public.foreign_tbl
! Remote SQL: UPDATE public.child_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid, $0
! Remote SQL: SELECT a, b, ctid, tableoid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
UPDATE rw_view SET b = b + 5; -- should fail
***************
*** 6331,6344 **** ERROR: new row violates check option for view "rw_view"
DETAIL: Failing row contains (20, 20).
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
! QUERY PLAN
! ----------------------------------------------------------------------------------------
Update on public.parent_tbl
Foreign Update on public.foreign_tbl
! Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid
! Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
UPDATE rw_view SET b = b + 15; -- ok
--- 6331,6344 ----
DETAIL: Failing row contains (20, 20).
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------
Update on public.parent_tbl
Foreign Update on public.foreign_tbl
! Remote SQL: UPDATE public.child_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
! Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid, $0
! Remote SQL: SELECT a, b, ctid, tableoid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
UPDATE rw_view SET b = b + 15; -- ok
***************
*** 6808,6820 **** BEFORE UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
! QUERY PLAN
! ---------------------------------------------------------------------
Update on public.rem1
! Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1
-> Foreign Scan on public.rem1
! Output: f1, ''::text, ctid, rem1.*
! Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
--- 6808,6820 ----
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
! QUERY PLAN
! --------------------------------------------------------------------------------
Update on public.rem1
! Remote SQL: UPDATE public.loc1 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.rem1
! Output: f1, ''::text, ctid, $0, rem1.*
! Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
***************
*** 6832,6844 **** AFTER UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
! QUERY PLAN
! -------------------------------------------------------------------------------
Update on public.rem1
! Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2
-> Foreign Scan on public.rem1
! Output: f1, ''::text, ctid, rem1.*
! Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
--- 6832,6844 ----
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
! QUERY PLAN
! -------------------------------------------------------------------------------------------------
Update on public.rem1
! Remote SQL: UPDATE public.loc1 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2
-> Foreign Scan on public.rem1
! Output: f1, ''::text, ctid, $0, rem1.*
! Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
***************
*** 6866,6878 **** UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
! QUERY PLAN
! ---------------------------------------------------------------------
Delete on public.rem1
! Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1
-> Foreign Scan on public.rem1
! Output: ctid, rem1.*
! Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_before_delete ON rem1;
--- 6866,6878 ----
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
! QUERY PLAN
! -------------------------------------------------------------------------------
Delete on public.rem1
! Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.rem1
! Output: ctid, $0, rem1.*
! Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_before_delete ON rem1;
***************
*** 6890,6902 **** UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
! QUERY PLAN
! ------------------------------------------------------------------------
Delete on public.rem1
! Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 RETURNING f1, f2
-> Foreign Scan on public.rem1
! Output: ctid, rem1.*
! Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_after_delete ON rem1;
--- 6890,6902 ----
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
! QUERY PLAN
! ------------------------------------------------------------------------------------------
Delete on public.rem1
! Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2
-> Foreign Scan on public.rem1
! Output: ctid, $0, rem1.*
! Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_after_delete ON rem1;
***************
*** 7147,7158 **** select * from bar where f1 in (select f1 from foo) for share;
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
! QUERY PLAN
! ---------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
! Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
--- 7147,7158 ----
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
! Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
***************
*** 7171,7182 **** update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
Hash Cond: (bar2.f1 = foo.f1)
-> Foreign Scan on public.bar2
! Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
! Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
-> HashAggregate
--- 7171,7182 ----
Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, ($0), foo.ctid, foo.*, foo.tableoid
Inner Unique: true
Hash Cond: (bar2.f1 = foo.f1)
-> Foreign Scan on public.bar2
! Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid, $0
! Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE
-> Hash
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
-> HashAggregate
***************
*** 7208,7219 **** update bar set f2 = f2 + 100
from
( select f1 from foo union all select f1+3 from foo ) ss
where bar.f1 = ss.f1;
! QUERY PLAN
! --------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
! Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))
Hash Cond: (foo.f1 = bar.f1)
--- 7208,7219 ----
from
( select f1 from foo union all select f1+3 from foo ) ss
where bar.f1 = ss.f1;
! QUERY PLAN
! ------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
! Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))
Hash Cond: (foo.f1 = bar.f1)
***************
*** 7233,7246 **** where bar.f1 = ss.f1;
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Merge Join
! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))
Merge Cond: (bar2.f1 = foo.f1)
-> Sort
! Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Sort Key: bar2.f1
-> Foreign Scan on public.bar2
! Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
! Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
Output: (ROW(foo.f1)), foo.f1
Sort Key: foo.f1
--- 7233,7246 ----
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Merge Join
! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, ($0), (ROW(foo.f1))
Merge Cond: (bar2.f1 = foo.f1)
-> Sort
! Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid, ($0)
Sort Key: bar2.f1
-> Foreign Scan on public.bar2
! Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid, $0
! Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE
-> Sort
Output: (ROW(foo.f1)), foo.f1
Sort Key: foo.f1
***************
*** 7438,7454 **** AFTER UPDATE OR DELETE ON bar2
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
explain (verbose, costs off)
update bar set f2 = f2 + 100;
! QUERY PLAN
! --------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
! Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2, f3
-> Seq Scan on public.bar
Output: bar.f1, (bar.f2 + 100), bar.ctid
-> Foreign Scan on public.bar2
! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, bar2.*
! Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
(9 rows)
update bar set f2 = f2 + 100;
--- 7438,7454 ----
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
explain (verbose, costs off)
update bar set f2 = f2 + 100;
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
! Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2, f3
-> Seq Scan on public.bar
Output: bar.f1, (bar.f2 + 100), bar.ctid
-> Foreign Scan on public.bar2
! Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, $0, bar2.*
! Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE
(9 rows)
update bar set f2 = f2 + 100;
***************
*** 7466,7483 **** NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2
NOTICE: OLD: (7,277,77),NEW: (7,377,77)
explain (verbose, costs off)
delete from bar where f2 < 400;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------
Delete on public.bar
Delete on public.bar
Foreign Delete on public.bar2
! Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3
-> Seq Scan on public.bar
Output: bar.ctid
Filter: (bar.f2 < 400)
-> Foreign Scan on public.bar2
! Output: bar2.ctid, bar2.*
! Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
(10 rows)
delete from bar where f2 < 400;
--- 7466,7483 ----
NOTICE: OLD: (7,277,77),NEW: (7,377,77)
explain (verbose, costs off)
delete from bar where f2 < 400;
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------
Delete on public.bar
Delete on public.bar
Foreign Delete on public.bar2
! Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2, f3
-> Seq Scan on public.bar
Output: bar.ctid
Filter: (bar.f2 < 400)
-> Foreign Scan on public.bar2
! Output: bar2.ctid, $0, bar2.*
! Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
(10 rows)
delete from bar where f2 < 400;
***************
*** 7568,7573 **** drop table loct1;
--- 7568,7654 ----
drop table loct2;
drop table parent;
-- ===================================================================
+ -- test update foreign partiton table
+ -- ===================================================================
+ CREATE TABLE p1 (a int, b int);
+ CREATE TABLE c1 (LIKE p1) INHERITS (p1);
+ NOTICE: merging column "a" with inherited definition
+ NOTICE: merging column "b" with inherited definition
+ CREATE TABLE c2 (LIKE p1) INHERITS (p1);
+ NOTICE: merging column "a" with inherited definition
+ NOTICE: merging column "b" with inherited definition
+ CREATE FOREIGN TABLE fp1 (a int, b int)
+ SERVER loopback OPTIONS (table_name 'p1');
+ INSERT INTO c1 VALUES (0, 1);
+ INSERT INTO c2 VALUES (1, 1);
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ toiddiff | ctid | a | b
+ ----------+-------+---+---
+ 0 | (0,1) | 0 | 1
+ 0 | (0,1) | 1 | 1
+ (2 rows)
+
+ -- random() causes non-direct foreign update
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------
+ Update on public.fp1
+ Remote SQL: UPDATE public.p1 SET b = $3 WHERE ctid = $1 AND tableoid = $2
+ -> Foreign Scan on public.fp1
+ Output: a, (b + 1), ctid, $0
+ Filter: (random() <= '1'::double precision)
+ Remote SQL: SELECT a, b, ctid, tableoid FROM public.p1 WHERE ((a = 0)) FOR UPDATE
+ (6 rows)
+
+ UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+ -- Only one tuple should be updated
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ toiddiff | ctid | a | b
+ ----------+-------+---+---
+ 0 | (0,2) | 0 | 2
+ 0 | (0,1) | 1 | 1
+ (2 rows)
+
+ -- Reset ctid
+ TRUNCATE c1;
+ TRUNCATE c2;
+ INSERT INTO c1 VALUES (0, 1);
+ INSERT INTO c2 VALUES (1, 1);
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ toiddiff | ctid | a | b
+ ----------+-------+---+---
+ 0 | (0,1) | 0 | 1
+ 0 | (0,1) | 1 | 1
+ (2 rows)
+
+ EXPLAIN (VERBOSE, COSTS OFF)
+ DELETE FROM fp1 WHERE a = 1 and random() <= 1;
+ QUERY PLAN
+ -------------------------------------------------------------------------------------
+ Delete on public.fp1
+ Remote SQL: DELETE FROM public.p1 WHERE ctid = $1 AND tableoid = $2
+ -> Foreign Scan on public.fp1
+ Output: ctid, $0
+ Filter: (random() <= '1'::double precision)
+ Remote SQL: SELECT ctid, tableoid FROM public.p1 WHERE ((a = 1)) FOR UPDATE
+ (6 rows)
+
+ DELETE FROM fp1 WHERE a = 1 and random() <= 1;
+ -- Only one tuple should be deleted
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ toiddiff | ctid | a | b
+ ----------+-------+---+---
+ 0 | (0,1) | 0 | 1
+ (1 row)
+
+ -- cleanup
+ DROP FOREIGN TABLE fp1;
+ DROP TABLE p1 CASCADE;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to table c1
+ drop cascades to table c2
+ -- ===================================================================
-- test tuple routing for foreign-table partitions
-- ===================================================================
-- Test insert tuple routing
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 30,35 ****
--- 30,36 ----
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
+ #include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/var.h"
#include "optimizer/tlist.h"
***************
*** 69,74 **** enum FdwScanPrivateIndex
--- 70,77 ----
FdwScanPrivateRetrievedAttrs,
/* Integer representing the desired fetch_size */
FdwScanPrivateFetchSize,
+ /* PARAM_EXEC Param ID for remote table OID (-1 if none) */
+ FdwScanPrivateTableOidParamId,
/*
* String describing join i.e. names of relations being joined and types
***************
*** 133,138 **** typedef struct PgFdwScanState
--- 136,142 ----
/* extracted fdw_private data */
char *query; /* text of SELECT command */
List *retrieved_attrs; /* list of retrieved attribute numbers */
+ int tableoid_param_id; /* Param ID for remote tableoid */
/* for remote query execution */
PGconn *conn; /* connection for the scan */
***************
*** 147,152 **** typedef struct PgFdwScanState
--- 151,157 ----
HeapTuple *tuples; /* array of currently-retrieved tuples */
int num_tuples; /* # of tuples in array */
int next_tuple; /* index of next one to return */
+ bool set_tableoid_param; /* Do we set remote tableoid Param? */
/* batch-level state, for optimizing rewinds and avoiding useless fetch */
int fetch_ct_2; /* Min(# of fetches done, 2) */
***************
*** 179,184 **** typedef struct PgFdwModifyState
--- 184,190 ----
/* info about parameters for prepared statement */
AttrNumber ctidAttno; /* attnum of input resjunk ctid column */
+ AttrNumber tableoidAttno; /* attnum of input resjunk tableoid column */
int p_nums; /* number of parameters to transmit */
FmgrInfo *p_flinfo; /* output conversion functions for them */
***************
*** 393,398 **** static PgFdwModifyState *create_foreign_modify(EState *estate,
--- 399,405 ----
static void prepare_foreign_modify(PgFdwModifyState *fmstate);
static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
ItemPointer tupleid,
+ Oid tableoid,
TupleTableSlot *slot);
static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
***************
*** 597,602 **** postgresGetForeignRelSize(PlannerInfo *root,
--- 604,634 ----
}
/*
+ * If the table is an UPDATE/DELETE target, the table's reltarget would
+ * contain a PARAM_EXEC Param to store in the value of the remote table
+ * OID for the target. Save a copy of the Param for later use.
+ */
+ if ((root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE) &&
+ baserel->relid == root->parse->resultRelation)
+ {
+ foreach(lc, baserel->reltarget->exprs)
+ {
+ Param *param = (Param *) lfirst(lc);
+
+ if (IsA(param, Param))
+ {
+ Assert(IS_FOREIGN_PARAM(root, param));
+ fpinfo->tableoid_param = (Param *) copyObject(param);
+ break;
+ }
+ }
+ Assert(fpinfo->tableoid_param);
+ }
+ else
+ fpinfo->tableoid_param = NULL;
+
+ /*
* Compute the selectivity and cost of the local_conds, so we don't have
* to do it over again for each path. The best we can do for these
* conditions is to estimate selectivity on the basis of local statistics.
***************
*** 1139,1144 **** postgresGetForeignPlan(PlannerInfo *root,
--- 1171,1177 ----
List *fdw_scan_tlist = NIL;
List *fdw_recheck_quals = NIL;
List *retrieved_attrs;
+ int tableoid_param_id;
StringInfoData sql;
ListCell *lc;
***************
*** 1278,1290 **** postgresGetForeignPlan(PlannerInfo *root,
/* Remember remote_exprs for possible use by postgresPlanDirectModify */
fpinfo->final_remote_exprs = remote_exprs;
/*
* Build the fdw_private list that will be available to the executor.
* Items in the list must match order in enum FdwScanPrivateIndex.
*/
! fdw_private = list_make3(makeString(sql.data),
retrieved_attrs,
! makeInteger(fpinfo->fetch_size));
if (IS_JOIN_REL(foreignrel) || IS_UPPER_REL(foreignrel))
fdw_private = lappend(fdw_private,
makeString(fpinfo->relation_name->data));
--- 1311,1330 ----
/* Remember remote_exprs for possible use by postgresPlanDirectModify */
fpinfo->final_remote_exprs = remote_exprs;
+ /* Get the PARAM_EXEC param ID for remote table OID, if any */
+ if (fpinfo->tableoid_param)
+ tableoid_param_id = fpinfo->tableoid_param->paramid;
+ else
+ tableoid_param_id = -1;
+
/*
* Build the fdw_private list that will be available to the executor.
* Items in the list must match order in enum FdwScanPrivateIndex.
*/
! fdw_private = list_make4(makeString(sql.data),
retrieved_attrs,
! makeInteger(fpinfo->fetch_size),
! makeInteger(tableoid_param_id));
if (IS_JOIN_REL(foreignrel) || IS_UPPER_REL(foreignrel))
fdw_private = lappend(fdw_private,
makeString(fpinfo->relation_name->data));
***************
*** 1369,1374 **** postgresBeginForeignScan(ForeignScanState *node, int eflags)
--- 1409,1416 ----
FdwScanPrivateRetrievedAttrs);
fsstate->fetch_size = intVal(list_nth(fsplan->fdw_private,
FdwScanPrivateFetchSize));
+ fsstate->tableoid_param_id = intVal(list_nth(fsplan->fdw_private,
+ FdwScanPrivateTableOidParamId));
/* Create contexts for batches of tuples and per-tuple temp workspace. */
fsstate->batch_cxt = AllocSetContextCreate(estate->es_query_cxt,
***************
*** 1381,1396 **** postgresBeginForeignScan(ForeignScanState *node, int eflags)
--- 1423,1448 ----
/*
* Get info we'll need for converting data fetched from the foreign server
* into local representation and error reporting during that process.
+ * Also, determine whether we need to set the remote tableoid Param.
*/
if (fsplan->scan.scanrelid > 0)
{
fsstate->rel = node->ss.ss_currentRelation;
fsstate->tupdesc = RelationGetDescr(fsstate->rel);
+
+ fsstate->set_tableoid_param =
+ fsstate->tableoid_param_id >= 0 ? true : false;
}
else
{
fsstate->rel = NULL;
fsstate->tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
+
+ /*
+ * No need to set the Param since the value will be produced as a
+ * tlist entry of fdw_scan_tlist (if it exists).
+ */
+ fsstate->set_tableoid_param = false;
}
fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
***************
*** 1419,1424 **** postgresIterateForeignScan(ForeignScanState *node)
--- 1471,1477 ----
{
PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state;
TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
+ HeapTuple tuple;
/*
* If this is the first call after Begin or ReScan, we need to create the
***************
*** 1441,1452 **** postgresIterateForeignScan(ForeignScanState *node)
}
/*
* Return the next tuple.
*/
! ExecStoreTuple(fsstate->tuples[fsstate->next_tuple++],
! slot,
! InvalidBuffer,
! false);
return slot;
}
--- 1494,1521 ----
}
/*
+ * Get the next tuple.
+ */
+ tuple = fsstate->tuples[fsstate->next_tuple++];
+
+ /*
+ * Set the PARAM_EXEC param for the remote table OID if necessary.
+ */
+ if (fsstate->set_tableoid_param)
+ {
+ ExprContext *econtext = node->ss.ps.ps_ExprContext;
+ ParamExecData *prm = &(econtext->ecxt_param_exec_vals[fsstate->tableoid_param_id]);
+
+ Assert(OidIsValid(tuple->t_tableOid));
+ prm->execPlan = NULL;
+ prm->value = ObjectIdGetDatum(tuple->t_tableOid);
+ prm->isnull = false;
+ }
+
+ /*
* Return the next tuple.
*/
! ExecStoreTuple(tuple, slot, InvalidBuffer, false);
return slot;
}
***************
*** 1541,1554 **** postgresAddForeignUpdateTargets(Query *parsetree,
Relation target_relation)
{
Var *var;
const char *attrname;
TargetEntry *tle;
/*
! * In postgres_fdw, what we need is the ctid, same as for a regular table.
*/
! /* Make a Var representing the desired value */
var = makeVar(parsetree->resultRelation,
SelfItemPointerAttributeNumber,
TIDOID,
--- 1610,1625 ----
Relation target_relation)
{
Var *var;
+ Param *param;
const char *attrname;
TargetEntry *tle;
/*
! * In postgres_fdw, what we need is the ctid, same as for a regular table,
! * and the remote table OID.
*/
! /* Make a Var representing the ctid value */
var = makeVar(parsetree->resultRelation,
SelfItemPointerAttributeNumber,
TIDOID,
***************
*** 1566,1571 **** postgresAddForeignUpdateTargets(Query *parsetree,
--- 1637,1656 ----
/* ... and add it to the query's targetlist */
parsetree->targetList = lappend(parsetree->targetList, tle);
+
+ /* Make a Param representing the tableoid value */
+ param = generate_foreign_param(OIDOID, -1, InvalidOid);
+
+ /* Wrap it in a resjunk TLE with the right name ... */
+ attrname = "remotetableoid";
+
+ tle = makeTargetEntry((Expr *) param,
+ list_length(parsetree->targetList) + 1,
+ pstrdup(attrname),
+ true);
+
+ /* ... and add it to the query's targetlist */
+ parsetree->targetList = lappend(parsetree->targetList, tle);
}
/*
***************
*** 1769,1775 **** postgresExecForeignInsert(EState *estate,
prepare_foreign_modify(fmstate);
/* Convert parameters needed by prepared statement to text form */
! p_values = convert_prep_stmt_params(fmstate, NULL, slot);
/*
* Execute the prepared statement.
--- 1854,1860 ----
prepare_foreign_modify(fmstate);
/* Convert parameters needed by prepared statement to text form */
! p_values = convert_prep_stmt_params(fmstate, NULL, InvalidOid, slot);
/*
* Execute the prepared statement.
***************
*** 1825,1830 **** postgresExecForeignUpdate(EState *estate,
--- 1910,1916 ----
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
Datum datum;
+ Datum datum2;
bool isNull;
const char **p_values;
PGresult *res;
***************
*** 1842,1850 **** postgresExecForeignUpdate(EState *estate,
--- 1928,1945 ----
if (isNull)
elog(ERROR, "ctid is NULL");
+ /* Get the tableoid that was passed up as a resjunk column */
+ datum2 = ExecGetJunkAttribute(planSlot,
+ fmstate->tableoidAttno,
+ &isNull);
+ /* shouldn't ever get a null result... */
+ if (isNull)
+ elog(ERROR, "tableoid is NULL");
+
/* Convert parameters needed by prepared statement to text form */
p_values = convert_prep_stmt_params(fmstate,
(ItemPointer) DatumGetPointer(datum),
+ DatumGetObjectId(datum2),
slot);
/*
***************
*** 1901,1906 **** postgresExecForeignDelete(EState *estate,
--- 1996,2002 ----
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
Datum datum;
+ Datum datum2;
bool isNull;
const char **p_values;
PGresult *res;
***************
*** 1918,1926 **** postgresExecForeignDelete(EState *estate,
--- 2014,2031 ----
if (isNull)
elog(ERROR, "ctid is NULL");
+ /* Get the tableoid that was passed up as a resjunk column */
+ datum2 = ExecGetJunkAttribute(planSlot,
+ fmstate->tableoidAttno,
+ &isNull);
+ /* shouldn't ever get a null result... */
+ if (isNull)
+ elog(ERROR, "tableoid is NULL");
+
/* Convert parameters needed by prepared statement to text form */
p_values = convert_prep_stmt_params(fmstate,
(ItemPointer) DatumGetPointer(datum),
+ DatumGetObjectId(datum2),
NULL);
/*
***************
*** 3345,3351 **** create_foreign_modify(EState *estate,
fmstate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
/* Prepare for output conversion of parameters used in prepared stmt. */
! n_params = list_length(fmstate->target_attrs) + 1;
fmstate->p_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * n_params);
fmstate->p_nums = 0;
--- 3450,3456 ----
fmstate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
/* Prepare for output conversion of parameters used in prepared stmt. */
! n_params = list_length(fmstate->target_attrs) + 2;
fmstate->p_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * n_params);
fmstate->p_nums = 0;
***************
*** 3363,3368 **** create_foreign_modify(EState *estate,
--- 3468,3484 ----
getTypeOutputInfo(TIDOID, &typefnoid, &isvarlena);
fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
fmstate->p_nums++;
+
+ /* Find the tableoid resjunk column in the subplan's result */
+ fmstate->tableoidAttno = ExecFindJunkAttributeInTlist(subplan->targetlist,
+ "remotetableoid");
+ if (!AttributeNumberIsValid(fmstate->tableoidAttno))
+ elog(ERROR, "could not find junk tableoid column");
+
+ /* Second transmittable parameter will be tableoid */
+ getTypeOutputInfo(OIDOID, &typefnoid, &isvarlena);
+ fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
+ fmstate->p_nums++;
}
if (operation == CMD_INSERT || operation == CMD_UPDATE)
***************
*** 3436,3441 **** prepare_foreign_modify(PgFdwModifyState *fmstate)
--- 3552,3558 ----
* Create array of text strings representing parameter values
*
* tupleid is ctid to send, or NULL if none
+ * tableoid is tableoid to send, or InvalidOid if none
* slot is slot to get remaining parameters from, or NULL if none
*
* Data is constructed in temp_cxt; caller should reset that after use.
***************
*** 3443,3448 **** prepare_foreign_modify(PgFdwModifyState *fmstate)
--- 3560,3566 ----
static const char **
convert_prep_stmt_params(PgFdwModifyState *fmstate,
ItemPointer tupleid,
+ Oid tableoid,
TupleTableSlot *slot)
{
const char **p_values;
***************
*** 3462,3467 **** convert_prep_stmt_params(PgFdwModifyState *fmstate,
--- 3580,3596 ----
pindex++;
}
+ /* 2nd parameter should be tableoid, if it's in use */
+ if (OidIsValid(tableoid))
+ {
+ Assert(tupleid != NULL);
+
+ /* don't need set_transmission_modes for OID output */
+ p_values[pindex] = OutputFunctionCall(&fmstate->p_flinfo[pindex],
+ ObjectIdGetDatum(tableoid));
+ pindex++;
+ }
+
/* get following parameters from slot */
if (slot != NULL && fmstate->target_attrs != NIL)
{
***************
*** 3851,3856 **** init_returning_filter(PgFdwDirectModifyState *dmstate,
--- 3980,3994 ----
TargetEntry *tle = (TargetEntry *) lfirst(lc);
Var *var = (Var *) tle->expr;
+ /*
+ * No need to set the Param anymore.
+ */
+ if (IsA(var, Param))
+ {
+ i++;
+ continue;
+ }
+
Assert(IsA(var, Var));
/*
***************
*** 4888,4893 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 5026,5056 ----
/* Mark that this join can be pushed down safely */
fpinfo->pushdown_safe = true;
+ /*
+ * If the join relation contains an UPDATE/DELETE target, remember the
+ * target's PARAM_EXEC Param in the fpinfo for later use.
+ */
+ if ((root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE) &&
+ bms_is_member(root->parse->resultRelation, joinrel->relids))
+ {
+ if (fpinfo_o->tableoid_param)
+ {
+ Assert(bms_is_member(root->parse->resultRelation,
+ outerrel->relids));
+ fpinfo->tableoid_param = fpinfo_o->tableoid_param;
+ }
+ else
+ {
+ Assert(bms_is_member(root->parse->resultRelation,
+ innerrel->relids));
+ Assert(fpinfo_i->tableoid_param);
+ fpinfo->tableoid_param = fpinfo_i->tableoid_param;
+ }
+ }
+ else
+ fpinfo->tableoid_param = NULL;
+
/* Get user mapping */
if (fpinfo->use_remote_estimate)
{
***************
*** 5561,5566 **** make_tuple_from_result_row(PGresult *res,
--- 5724,5730 ----
bool *nulls;
ItemPointer ctid = NULL;
Oid oid = InvalidOid;
+ Oid tableoid = InvalidOid;
ConversionLocation errpos;
ErrorContextCallback errcallback;
MemoryContext oldcontext;
***************
*** 5654,5659 **** make_tuple_from_result_row(PGresult *res,
--- 5818,5834 ----
oid = DatumGetObjectId(datum);
}
}
+ else if (i == TableOidAttributeNumber)
+ {
+ /* tableoid */
+ if (valstr != NULL)
+ {
+ Datum datum;
+
+ datum = DirectFunctionCall1(oidin, CStringGetDatum(valstr));
+ tableoid = DatumGetObjectId(datum);
+ }
+ }
errpos.cur_attno = 0;
j++;
***************
*** 5703,5708 **** make_tuple_from_result_row(PGresult *res,
--- 5878,5893 ----
if (OidIsValid(oid))
HeapTupleSetOid(tuple, oid);
+ /*
+ * If we have a table OID to return, install it. Note that this is not
+ * really right because the installed value is the value on the remote
+ * side, not the local side, but we do this for use by
+ * postgresIterateForeignScan(). The correct value will be re-installed
+ * in ForeignNext if necessary.
+ */
+ if (OidIsValid(tableoid))
+ tuple->t_tableOid = tableoid;
+
/* Clean up */
MemoryContextReset(temp_context);
***************
*** 5719,5724 **** conversion_error_callback(void *arg)
--- 5904,5910 ----
const char *attname = NULL;
const char *relname = NULL;
bool is_wholerow = false;
+ bool is_tableoid = false;
ConversionLocation *errpos = (ConversionLocation *) arg;
if (errpos->rel)
***************
*** 5733,5738 **** conversion_error_callback(void *arg)
--- 5919,5926 ----
attname = "ctid";
else if (errpos->cur_attno == ObjectIdAttributeNumber)
attname = "oid";
+ else if (errpos->cur_attno == TableOidAttributeNumber)
+ is_tableoid = true;
relname = RelationGetRelationName(errpos->rel);
}
***************
*** 5761,5766 **** conversion_error_callback(void *arg)
--- 5949,5956 ----
if (var->varattno == 0)
is_wholerow = true;
+ else if (var->varattno == TableOidAttributeNumber)
+ is_tableoid = true;
else
attname = get_attname(rte->relid, var->varattno, false);
***************
*** 5775,5780 **** conversion_error_callback(void *arg)
--- 5965,5972 ----
{
if (is_wholerow)
errcontext("whole-row reference to foreign table \"%s\"", relname);
+ if (is_tableoid)
+ errcontext("remote tableoid for foreign table \"%s\"", relname);
else if (attname)
errcontext("column \"%s\" of foreign table \"%s\"", attname, relname);
}
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
***************
*** 49,54 **** typedef struct PgFdwRelationInfo
--- 49,57 ----
/* Bitmap of attr numbers we need to fetch from the remote server. */
Bitmapset *attrs_used;
+ /* PARAM_EXEC param representing the remote table OID of a target rel */
+ Param *tableoid_param;
+
/* Cost and selectivity of local_conds. */
QualCost local_conds_cost;
Selectivity local_conds_sel;
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 1847,1852 **** drop table loct2;
--- 1847,1885 ----
drop table parent;
-- ===================================================================
+ -- test update foreign partiton table
+ -- ===================================================================
+ CREATE TABLE p1 (a int, b int);
+ CREATE TABLE c1 (LIKE p1) INHERITS (p1);
+ CREATE TABLE c2 (LIKE p1) INHERITS (p1);
+ CREATE FOREIGN TABLE fp1 (a int, b int)
+ SERVER loopback OPTIONS (table_name 'p1');
+ INSERT INTO c1 VALUES (0, 1);
+ INSERT INTO c2 VALUES (1, 1);
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ -- random() causes non-direct foreign update
+ EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+ UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+ -- Only one tuple should be updated
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ -- Reset ctid
+ TRUNCATE c1;
+ TRUNCATE c2;
+ INSERT INTO c1 VALUES (0, 1);
+ INSERT INTO c2 VALUES (1, 1);
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ EXPLAIN (VERBOSE, COSTS OFF)
+ DELETE FROM fp1 WHERE a = 1 and random() <= 1;
+ DELETE FROM fp1 WHERE a = 1 and random() <= 1;
+ -- Only one tuple should be deleted
+ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+
+ -- cleanup
+ DROP FOREIGN TABLE fp1;
+ DROP TABLE p1 CASCADE;
+
+ -- ===================================================================
-- test tuple routing for foreign-table partitions
-- ===================================================================
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2268,2273 **** _outPlannerGlobal(StringInfo str, const PlannerGlobal *node)
--- 2268,2274 ----
WRITE_BOOL_FIELD(parallelModeOK);
WRITE_BOOL_FIELD(parallelModeNeeded);
WRITE_CHAR_FIELD(maxParallelHazard);
+ WRITE_BITMAPSET_FIELD(foreignParamIDs);
}
static void
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
***************
*** 29,34 ****
--- 29,35 ----
#include "optimizer/restrictinfo.h"
#include "optimizer/var.h"
#include "parser/analyze.h"
+ #include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
***************
*** 46,51 **** typedef struct PostponedQual
--- 47,55 ----
} PostponedQual;
+ static void add_params_to_result_rel(PlannerInfo *root,
+ int result_relation,
+ List *final_tlist);
static void extract_lateral_references(PlannerInfo *root, RelOptInfo *brel,
Index rtindex);
static List *deconstruct_recurse(PlannerInfo *root, Node *jtnode,
***************
*** 146,151 **** add_base_rels_to_query(PlannerInfo *root, Node *jtnode)
--- 150,161 ----
*
* We mark such vars as needed by "relation 0" to ensure that they will
* propagate up through all join plan steps.
+ *
+ * If this is an UPDATE/DELETE on a foreign table, the FDW might have added
+ * PARAM_EXEC Params to the final tlist that are needed for identifying the
+ * rows to be updated or deleted. Add targetlist entries for each such Param
+ * to the result relation. It will be ensured by build_joinrel_tlist() that
+ * such Params will also propagate up through all join plan steps.
*/
void
build_base_rel_tlists(PlannerInfo *root, List *final_tlist)
***************
*** 154,159 **** build_base_rel_tlists(PlannerInfo *root, List *final_tlist)
--- 164,170 ----
PVC_RECURSE_AGGREGATES |
PVC_RECURSE_WINDOWFUNCS |
PVC_INCLUDE_PLACEHOLDERS);
+ int result_relation = root->parse->resultRelation;
if (tlist_vars != NIL)
{
***************
*** 178,183 **** build_base_rel_tlists(PlannerInfo *root, List *final_tlist)
--- 189,204 ----
list_free(having_vars);
}
}
+
+ /*
+ * If this is an UPDATE/DELETE on a foreign table, add targetlist entries
+ * for the PARAM_EXEC Params to the result relation.
+ */
+ if (result_relation &&
+ (root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE) &&
+ planner_rt_fetch(result_relation, root)->relkind == RELKIND_FOREIGN_TABLE)
+ add_params_to_result_rel(root, result_relation, final_tlist);
}
/*
***************
*** 241,246 **** add_vars_to_targetlist(PlannerInfo *root, List *vars,
--- 262,296 ----
}
}
+ /*
+ * add_params_to_result_rel
+ * Add targetlist entries for PARAM_EXEC Params the FDW added to the
+ * query's final tlist to the result relation (if any).
+ */
+ static void
+ add_params_to_result_rel(PlannerInfo *root, int result_relation,
+ List *final_tlist)
+ {
+ RelOptInfo *target_rel = find_base_rel(root, result_relation);
+ ListCell *lc;
+
+ foreach(lc, final_tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Param *param = (Param *) tle->expr;
+
+ if (!tle->resjunk)
+ continue;
+
+ if (!IsA(param, Param) || !IS_FOREIGN_PARAM(root, param))
+ continue;
+
+ /* XXX is copyObject necessary here? */
+ target_rel->reltarget->exprs = lappend(target_rel->reltarget->exprs,
+ copyObject(param));
+ }
+ }
+
/*****************************************************************************
*
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 301,306 **** standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
--- 301,307 ----
glob->lastPlanNodeId = 0;
glob->transientPlan = false;
glob->dependsOnRole = false;
+ glob->foreignParamIDs = NULL;
/*
* Assess whether it's feasible to use parallel mode for this query. We
***************
*** 469,480 **** standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
}
/*
! * If any Params were generated, run through the plan tree and compute
! * each plan node's extParam/allParam sets. Ideally we'd merge this into
! * set_plan_references' tree traversal, but for now it has to be separate
! * because we need to visit subplans before not after main plan.
*/
! if (glob->paramExecTypes != NIL)
{
Assert(list_length(glob->subplans) == list_length(glob->subroots));
forboth(lp, glob->subplans, lr, glob->subroots)
--- 470,485 ----
}
/*
! * If any Params were generated by the planner not by FDWs, run through
! * the plan tree and compute each plan node's extParam/allParam sets.
! * (Params added by FDWs are irrelevant for parameter change signaling.)
! * Ideally we'd merge this into set_plan_references' tree traversal, but
! * for now it has to be separate because we need to visit subplans before
! * not after main plan.
*/
! if (glob->paramExecTypes != NIL &&
! (bms_is_empty(glob->foreignParamIDs) ||
! bms_num_members(glob->foreignParamIDs) < list_length(glob->paramExecTypes)))
{
Assert(list_length(glob->subplans) == list_length(glob->subroots));
forboth(lp, glob->subplans, lr, glob->subroots)
*** a/src/backend/optimizer/plan/setrefs.c
--- b/src/backend/optimizer/plan/setrefs.c
***************
*** 168,184 **** static bool extract_query_dependencies_walker(Node *node,
* 5. PARAM_MULTIEXPR Params are replaced by regular PARAM_EXEC Params,
* now that we have finished planning all MULTIEXPR subplans.
*
! * 6. We compute regproc OIDs for operators (ie, we look up the function
* that implements each op).
*
! * 7. We create lists of specific objects that the plan depends on.
* This will be used by plancache.c to drive invalidation of cached plans.
* Relation dependencies are represented by OIDs, and everything else by
* PlanInvalItems (this distinction is motivated by the shared-inval APIs).
* Currently, relations and user-defined functions are the only types of
* objects that are explicitly tracked this way.
*
! * 8. We assign every plan node in the tree a unique ID.
*
* We also perform one final optimization step, which is to delete
* SubqueryScan plan nodes that aren't doing anything useful (ie, have
--- 168,187 ----
* 5. PARAM_MULTIEXPR Params are replaced by regular PARAM_EXEC Params,
* now that we have finished planning all MULTIEXPR subplans.
*
! * 6. PARAM_EXEC Params added by FDWs in upper plan nodes are converted into
! * simple Vars referencing the outputs of their subplans.
! *
! * 7. We compute regproc OIDs for operators (ie, we look up the function
* that implements each op).
*
! * 8. We create lists of specific objects that the plan depends on.
* This will be used by plancache.c to drive invalidation of cached plans.
* Relation dependencies are represented by OIDs, and everything else by
* PlanInvalItems (this distinction is motivated by the shared-inval APIs).
* Currently, relations and user-defined functions are the only types of
* objects that are explicitly tracked this way.
*
! * 9. We assign every plan node in the tree a unique ID.
*
* We also perform one final optimization step, which is to delete
* SubqueryScan plan nodes that aren't doing anything useful (ie, have
***************
*** 2313,2319 **** fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
--- 2316,2359 ----
return fix_join_expr_mutator((Node *) phv->phexpr, context);
}
if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+
+ /*
+ * If the param is a PARAM_EXEC param added by an FDW, it should have
+ * bubbled up from a lower plan node; convert it into a simple Var
+ * referencing the output of the subplan.
+ *
+ * Note: set_join_references() would have kept has_non_vars=true for
+ * the subplan emitting the param since it effectively belong to the
+ * result relation and that relation can never be the nullable side of
+ * an outer join.
+ */
+ if (IS_FOREIGN_PARAM(context->root, param))
+ {
+ if (context->outer_itlist && context->outer_itlist->has_non_vars)
+ {
+ newvar = search_indexed_tlist_for_non_var((Expr *) node,
+ context->outer_itlist,
+ OUTER_VAR);
+ if (newvar)
+ return (Node *) newvar;
+ }
+ if (context->inner_itlist && context->inner_itlist->has_non_vars)
+ {
+ newvar = search_indexed_tlist_for_non_var((Expr *) node,
+ context->inner_itlist,
+ INNER_VAR);
+ if (newvar)
+ return (Node *) newvar;
+ }
+ /* No referent found for foreign Param */
+ elog(ERROR, "foreign parameter not found in subplan target lists");
+ }
+
+ /* If not, do fix_param_node() */
return fix_param_node(context->root, (Param *) node);
+ }
/* Try matching more complex expressions too, if tlists have any */
if (context->outer_itlist && context->outer_itlist->has_non_vars)
{
***************
*** 2419,2425 **** fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
--- 2459,2488 ----
return fix_upper_expr_mutator((Node *) phv->phexpr, context);
}
if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+
+ /*
+ * If the param is a PARAM_EXEC param added by an FDW, it should have
+ * bubbled up from a lower plan node; convert it into a simple Var
+ * referencing the output of the subplan.
+ */
+ if (IS_FOREIGN_PARAM(context->root, param))
+ {
+ if (context->subplan_itlist->has_non_vars)
+ {
+ newvar = search_indexed_tlist_for_non_var((Expr *) node,
+ context->subplan_itlist,
+ context->newvarno);
+ if (newvar)
+ return (Node *) newvar;
+ }
+ /* No referent found for foreign Param */
+ elog(ERROR, "foreign parameter not found in subplan target list");
+ }
+ /* If not, do fix_param_node() */
return fix_param_node(context->root, (Param *) node);
+ }
if (IsA(node, Aggref))
{
Aggref *aggref = (Aggref *) node;
*** a/src/backend/optimizer/plan/subselect.c
--- b/src/backend/optimizer/plan/subselect.c
***************
*** 2897,2903 **** finalize_primnode(Node *node, finalize_primnode_context *context)
{
int paramid = ((Param *) node)->paramid;
! context->paramids = bms_add_member(context->paramids, paramid);
}
return false; /* no more to do here */
}
--- 2897,2908 ----
{
int paramid = ((Param *) node)->paramid;
! /*
! * Params added by FDWs are irrelevant for parameter change
! * signaling.
! */
! if (!bms_is_member(paramid, context->root->glob->foreignParamIDs))
! context->paramids = bms_add_member(context->paramids, paramid);
}
return false; /* no more to do here */
}
*** a/src/backend/optimizer/prep/preptlist.c
--- b/src/backend/optimizer/prep/preptlist.c
***************
*** 55,60 ****
--- 55,61 ----
static List *expand_targetlist(List *tlist, int command_type,
Index result_relation, Relation rel);
+ static void fix_foreign_params(PlannerInfo *root, List *tlist);
/*
***************
*** 106,113 **** preprocess_targetlist(PlannerInfo *root)
--- 107,120 ----
* keep it that way to avoid changing APIs used by FDWs.
*/
if (command_type == CMD_UPDATE || command_type == CMD_DELETE)
+ {
rewriteTargetListUD(parse, target_rte, target_relation);
+ /* The FDW might have added params; fix such params if any */
+ if (target_rte->relkind == RELKIND_FOREIGN_TABLE)
+ fix_foreign_params(root, parse->targetList);
+ }
+
/*
* for heap_form_tuple to work, the targetlist must match the exact order
* of the attributes. We also need to fill in any missing attributes. -ay
***************
*** 416,421 **** expand_targetlist(List *tlist, int command_type,
--- 423,478 ----
/*
+ * Generate a Param for the FDW to identify the rows to be updated/deleted.
+ *
+ * The plan is expected to return a scalar value of the given type/collation.
+ */
+ Param *
+ generate_foreign_param(Oid paramtype, int32 paramtypmod, Oid paramcollation)
+ {
+ Param *retval;
+
+ retval = makeNode(Param);
+ retval->paramkind = PARAM_EXEC;
+ /* paramid will be filled in by fix_foreign_params */
+ retval->paramid = -1;
+ retval->paramtype = paramtype;
+ retval->paramtypmod = paramtypmod;
+ retval->paramcollid = paramcollation;
+ retval->location = -1;
+
+ return retval;
+ }
+
+ /*
+ * Fix the paramids of PARAM_EXEC params the FDW added to the tlist if any.
+ */
+ static void
+ fix_foreign_params(PlannerInfo *root, List *tlist)
+ {
+ ListCell *lc;
+
+ foreach(lc, tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Param *param = (Param *) tle->expr;
+
+ if (tle->resjunk &&
+ IsA(param, Param) &&
+ param->paramkind == PARAM_EXEC &&
+ param->paramid == -1)
+ {
+ param->paramid = list_length(root->glob->paramExecTypes);
+ root->glob->paramExecTypes =
+ lappend_oid(root->glob->paramExecTypes, param->paramtype);
+ root->glob->foreignParamIDs =
+ bms_add_member(root->glob->foreignParamIDs, param->paramid);
+ }
+ }
+ }
+
+
+ /*
* Locate PlanRowMark for given RT index, or return NULL if none
*
* This probably ought to be elsewhere, but there's no very good place
*** a/src/backend/optimizer/util/relnode.c
--- b/src/backend/optimizer/util/relnode.c
***************
*** 28,33 ****
--- 28,34 ----
#include "optimizer/tlist.h"
#include "partitioning/partbounds.h"
#include "utils/hsearch.h"
+ #include "utils/lsyscache.h"
typedef struct JoinHashEntry
***************
*** 913,918 **** build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
--- 914,933 ----
RelOptInfo *baserel;
int ndx;
+ /* Params are needed for final output, so add them to the output */
+ if (IsA(var, Param))
+ {
+ Param *param = (Param *) var;
+
+ Assert(IS_FOREIGN_PARAM(root, param));
+ joinrel->reltarget->exprs =
+ lappend(joinrel->reltarget->exprs, param);
+ /* Estimate using the type info */
+ joinrel->reltarget->width +=
+ get_typavgwidth(param->paramtype, param->paramtypmod);
+ continue;
+ }
+
/*
* Ignore PlaceHolderVars in the input tlists; we'll make our own
* decisions about whether to copy them.
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
***************
*** 145,156 **** typedef struct PlannerGlobal
--- 145,162 ----
bool parallelModeNeeded; /* parallel mode actually required? */
char maxParallelHazard; /* worst PROPARALLEL hazard level */
+
+ Bitmapset *foreignParamIDs; /* PARAM_EXEC Params added by FDWs */
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
#define planner_subplan_get_plan(root, subplan) \
((Plan *) list_nth((root)->glob->subplans, (subplan)->plan_id - 1))
+ /* macro for checking if a Param node is a PARAM_EXEC Param added by an FDW */
+ #define IS_FOREIGN_PARAM(root, param) \
+ ((param)->paramkind == PARAM_EXEC && \
+ bms_is_member((param)->paramid, (root)->glob->foreignParamIDs))
/*----------
* PlannerInfo
*** a/src/include/optimizer/prep.h
--- b/src/include/optimizer/prep.h
***************
*** 40,45 **** extern Expr *canonicalize_qual(Expr *qual, bool is_check);
--- 40,48 ----
*/
extern List *preprocess_targetlist(PlannerInfo *root);
+ extern Param *generate_foreign_param(Oid paramtype, int32 paramtypmod,
+ Oid paramcollation);
+
extern PlanRowMark *get_plan_rowmark(List *rowmarks, Index rtindex);
/*
