(2018/09/21 20:03), Etsuro Fujita wrote:
(2018/09/18 21:14), Kyotaro HORIGUCHI wrote:
At Fri, 14 Sep 2018 22:01:39 +0900, Etsuro
Fujita<fujita.ets...@lab.ntt.co.jp> wrote
in<5b9bb133.1060...@lab.ntt.co.jp>
I wrote a patch using
the Param-based approach, and compared the two approaches.
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.)
I spent quite a bit of time looking into that, but I couldn't find any
issues, including ones discussed in [1]:
* In contrib/postgres_fdw, the patch does the special handling of the
Param representing the remote table OID in deparsing a remote SELECT
query and building fdw_scan_tlist, but it wouldn't need the
pull_var_clause change as proposed in [1]. And ISTM that that handling
would be sufficient to avoid errors like 'variable not found in subplan
target lists' as in [1].
* Params as extra target expressions can never be used as Pathkeys or
something like that, so it seems unlikely that that approach would cause
'could not find pathkey item to sort' errors in
prepare_sort_from_pathkeys() as in [1].
* I checked other parts of the planner such as subselect.c and
setrefs.c, but I couldn't find any issues.
What do you think about that?
I confirmed that a FOREIGN_PARAM_EXEC is evaluated and stored
into the parent node. For the mentioned Merge/Sort/ForeignScan
case, Sort node takes the parameter value via projection. I
didn't know PARAM_EXEC works that way. I consulted nodeNestLoop
but not fully understood.
So I think it works. I still don't think expanded tupledesc is
not wart but this is smarter than that. Addition to that, it
seems back-patchable. I must admit that yours is better.
I also think that approach would be back-patchable to PG9.3, where
contrib/postgres_fdw landed with the writable functionality, so I'm
inclined to vote for the Param-based approach. Attached is an updated
version of the patch. Changes:
* Added this to use_physical_tlist():
One thing I noticed is: in any approach, I think use_physical_tlist
needs to be modified so that it disables doing build_physical_tlist for
a foreign scan in the case where the FDW added resjunk columns for
UPDATE/DELETE that are different from user/system columns of the foreign
table; else such columns would not be emitted from the foreign scan.
* Fixed a bug in conversion_error_callback() in contrib/postgres_fdw.c
* Simplified your contrib/postgres_fdw.c tests as discussed
* Revise code/comments a bit
* Added docs to fdwhandler.sgml
* Rebased the patch against the latest HEAD
Best regards,
Etsuro Fujita
[1]
https://www.postgresql.org/message-id/flat/cakcux6ktu-8teflwtquuzbyfaza83vuzurd7c1yhc-yewyy...@mail.gmail.com
*** 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,926 ----
PVC_RECURSE_PLACEHOLDERS));
}
+ /* Also, add the Param representing the remote table OID, if it exists. */
+ if (fpinfo->tableoid_param)
+ {
+ TargetEntry *tle;
+
+ /*
+ * Core code should have contained the Param in the given relation's
+ * reltarget.
+ */
+ Assert(list_member(foreignrel->reltarget->exprs,
+ fpinfo->tableoid_param));
+
+ 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);
}
}
--- 1072,1080 ----
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)
--- 1115,1122 ----
* 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,
--- 1129,1135 ----
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))
--- 1171,1177 ----
/*
* 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,
--- 1205,1224 ----
*retrieved_attrs = lappend_int(*retrieved_attrs,
ObjectIdAttributeNumber);
}
+ if (tableoid_needed)
+ {
+ Assert(bms_is_member(SelfItemPointerAttributeNumber - FirstLowInvalidHeapAttributeNumber,
+ attrs_used));
+ Assert(!first);
+ Assert(!is_returning);
+ Assert(!qualify_col);
+
+ appendStringInfoString(buf, ", ");
+ 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)
{
--- 1767,1774 ----
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,
--- 1782,1788 ----
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,
--- 1898,1904 ----
{
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;
}
--- 2014,2020 ----
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. */
--- 2187,2194 ----
}
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)
--- 2554,2575 ----
static void
deparseParam(Param *node, deparse_expr_cxt *context)
{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
+
+ /*
+ * If the Param is the one representing the remote table OID of a target
+ * relation, the value needs to be produced; fetch that OID, instead.
+ */
+ if (equal(node, (Node *) fpinfo->tableoid_param))
+ {
+ 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"
***************
*** 6088,6093 **** SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
--- 6088,6174 ----
40 | 42 | 00040_trig_update | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
(10 rows)
+ -- Test UPDATE/DELETE in the case where the remote target is
+ -- an inheritance tree
+ 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::regclass, ctid, * FROM fp1;
+ tableoid | ctid | a | b
+ ----------+-------+---+---
+ fp1 | (0,1) | 0 | 1
+ fp1 | (0,1) | 1 | 1
+ (2 rows)
+
+ -- Update statement should not be pushed down to the remote side
+ 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::regclass, ctid, * FROM fp1;
+ tableoid | ctid | a | b
+ ----------+-------+---+---
+ fp1 | (0,2) | 0 | 2
+ fp1 | (0,1) | 1 | 1
+ (2 rows)
+
+ -- Recreate the table data
+ TRUNCATE c1;
+ TRUNCATE c2;
+ INSERT INTO c1 VALUES (0, 1);
+ INSERT INTO c2 VALUES (1, 1);
+ SELECT tableoid::regclass, ctid, * FROM fp1;
+ tableoid | ctid | a | b
+ ----------+-------+---+---
+ fp1 | (0,1) | 0 | 1
+ fp1 | (0,1) | 1 | 1
+ (2 rows)
+
+ -- Delete statement should not be pushed down to the remote side
+ 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::regclass, ctid, * FROM fp1;
+ tableoid | ctid | a | b
+ ----------+-------+---+---
+ fp1 | (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 check constraints
-- ===================================================================
***************
*** 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
--- 6310,6322 ----
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
--- 6324,6336 ----
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
--- 6397,6410 ----
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
--- 6412,6425 ----
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)
--- 6889,6901 ----
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)
--- 6913,6925 ----
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;
--- 6947,6959 ----
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;
--- 6971,6983 ----
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
--- 7228,7239 ----
-- 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
--- 7252,7263 ----
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)
--- 7289,7300 ----
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
--- 7314,7327 ----
-> 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;
--- 7519,7535 ----
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;
--- 7547,7564 ----
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;
*** 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"
***************
*** 67,72 **** enum FdwScanPrivateIndex
--- 68,75 ----
FdwScanPrivateSelectSql,
/* Integer list of attribute numbers retrieved by the SELECT */
FdwScanPrivateRetrievedAttrs,
+ /* Param ID for remote table OID for target rel (-1 if none) */
+ FdwScanPrivateTableOidParamId,
/* Integer representing the desired fetch_size */
FdwScanPrivateFetchSize,
***************
*** 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 table OID */
/* 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 need to set the 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,632 ----
}
/*
+ * If the table is an UPDATE/DELETE target, the table's reltarget would
+ * contain a PARAM_EXEC Param representing the remote table OID for the
+ * target. Get the Param, and save a copy of it for use later.
+ */
+ if (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,
--- 1169,1175 ----
List *fdw_scan_tlist = NIL;
List *fdw_recheck_quals = NIL;
List *retrieved_attrs;
+ int tableoid_param_id;
StringInfoData sql;
ListCell *lc;
***************
*** 1278,1289 **** 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,
--- 1309,1327 ----
/* Remember remote_exprs for possible use by postgresPlanDirectModify */
fpinfo->final_remote_exprs = remote_exprs;
+ /* Get the Param ID for the remote table OID, if it exists */
+ 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(tableoid_param_id),
makeInteger(fpinfo->fetch_size));
if (IS_JOIN_REL(foreignrel) || IS_UPPER_REL(foreignrel))
fdw_private = lappend(fdw_private,
***************
*** 1367,1372 **** postgresBeginForeignScan(ForeignScanState *node, int eflags)
--- 1405,1412 ----
FdwScanPrivateSelectSql));
fsstate->retrieved_attrs = (List *) list_nth(fsplan->fdw_private,
FdwScanPrivateRetrievedAttrs);
+ fsstate->tableoid_param_id = intVal(list_nth(fsplan->fdw_private,
+ FdwScanPrivateTableOidParamId));
fsstate->fetch_size = intVal(list_nth(fsplan->fdw_private,
FdwScanPrivateFetchSize));
***************
*** 1381,1396 **** postgresBeginForeignScan(ForeignScanState *node, int eflags)
--- 1421,1447 ----
/*
* 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 Param for the remote table
+ * OID.
*/
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, even if it exists.
+ */
+ fsstate->set_tableoid_param = false;
}
fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
***************
*** 1419,1424 **** postgresIterateForeignScan(ForeignScanState *node)
--- 1470,1476 ----
{
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,1451 **** postgresIterateForeignScan(ForeignScanState *node)
}
/*
! * Return the next tuple.
*/
! ExecStoreHeapTuple(fsstate->tuples[fsstate->next_tuple++],
! slot,
! false);
return slot;
}
--- 1493,1520 ----
}
/*
! * Get the next tuple.
*/
! tuple = fsstate->tuples[fsstate->next_tuple++];
!
! /*
! * Set the 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.
! */
! ExecStoreHeapTuple(tuple, slot, false);
return slot;
}
***************
*** 1540,1553 **** 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,
--- 1609,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, which is needed since the remote table might
! * be an inheritance tree.
*/
! /* Make a Var representing the ctid value */
var = makeVar(parsetree->resultRelation,
SelfItemPointerAttributeNumber,
TIDOID,
***************
*** 1565,1570 **** 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);
}
/*
***************
*** 1768,1774 **** 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.
***************
*** 1824,1829 **** postgresExecForeignUpdate(EState *estate,
--- 1910,1916 ----
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
Datum datum;
+ Datum datum2;
bool isNull;
const char **p_values;
PGresult *res;
***************
*** 1841,1849 **** 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);
/*
***************
*** 1900,1905 **** postgresExecForeignDelete(EState *estate,
--- 1996,2002 ----
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
Datum datum;
+ Datum datum2;
bool isNull;
const char **p_values;
PGresult *res;
***************
*** 1917,1925 **** 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);
/*
***************
*** 3344,3350 **** 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;
***************
*** 3362,3367 **** 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)
***************
*** 3435,3440 **** 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.
***************
*** 3442,3447 **** 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;
***************
*** 3461,3466 **** 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)
{
***************
*** 3850,3855 **** init_returning_filter(PgFdwDirectModifyState *dmstate,
--- 3980,3996 ----
TargetEntry *tle = (TargetEntry *) lfirst(lc);
Var *var = (Var *) tle->expr;
+ /*
+ * No need to set the Param for the remote table OID; ignore it.
+ */
+ if (IsA(var, Param))
+ {
+ /* We would not retrieve the remote table OID anymore. */
+ Assert(!list_member_int(dmstate->retrieved_attrs, i));
+ i++;
+ continue;
+ }
+
Assert(IsA(var, Var));
/*
***************
*** 4887,4892 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 5028,5064 ----
/* Mark that this join can be pushed down safely */
fpinfo->pushdown_safe = true;
+ /*
+ * If the join relation contains an UPDATE/DELETE target, get the Param
+ * representing the remote table OID for the target, and remember it in
+ * this fpinfo for use later.
+ */
+ if ((root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE) &&
+ bms_is_member(root->parse->resultRelation, joinrel->relids))
+ {
+ if (bms_is_member(root->parse->resultRelation,
+ outerrel->relids))
+ {
+ Assert(fpinfo_o->tableoid_param);
+ 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;
+ }
+ /*
+ * Core code should have contained the Param in the join relation's
+ * reltarget.
+ */
+ Assert(list_member(joinrel->reltarget->exprs, fpinfo->tableoid_param));
+ }
+ else
+ fpinfo->tableoid_param = NULL;
+
/* Get user mapping */
if (fpinfo->use_remote_estimate)
{
***************
*** 5560,5565 **** make_tuple_from_result_row(PGresult *res,
--- 5732,5738 ----
bool *nulls;
ItemPointer ctid = NULL;
Oid oid = InvalidOid;
+ Oid tableoid = InvalidOid;
ConversionLocation errpos;
ErrorContextCallback errcallback;
MemoryContext oldcontext;
***************
*** 5653,5658 **** make_tuple_from_result_row(PGresult *res,
--- 5826,5842 ----
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++;
***************
*** 5702,5707 **** make_tuple_from_result_row(PGresult *res,
--- 5886,5901 ----
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);
***************
*** 5718,5723 **** conversion_error_callback(void *arg)
--- 5912,5918 ----
const char *attname = NULL;
const char *relname = NULL;
bool is_wholerow = false;
+ bool is_tableoid = false;
ConversionLocation *errpos = (ConversionLocation *) arg;
if (errpos->rel)
***************
*** 5732,5737 **** conversion_error_callback(void *arg)
--- 5927,5934 ----
attname = "ctid";
else if (errpos->cur_attno == ObjectIdAttributeNumber)
attname = "oid";
+ else if (errpos->cur_attno == TableOidAttributeNumber)
+ is_tableoid = true;
relname = RelationGetRelationName(errpos->rel);
}
***************
*** 5748,5755 **** conversion_error_callback(void *arg)
/*
* Target list can have Vars and expressions. For Vars, we can get
! * its relation, however for expressions we can't. Thus for
! * expressions, just show generic context message.
*/
if (IsA(tle->expr, Var))
{
--- 5945,5954 ----
/*
* Target list can have Vars and expressions. For Vars, we can get
! * its relation, however for expressions we can't, except for the
! * Param representing the remote table OID of a target relation, in
! * which case we can get that relation. Thus for expressions other
! * than the Param, just show generic context message.
*/
if (IsA(tle->expr, Var))
{
***************
*** 5765,5770 **** conversion_error_callback(void *arg)
--- 5964,5984 ----
relname = get_rel_name(rte->relid);
}
+ else if (IsA(tle->expr, Param))
+ {
+ RangeTblEntry *rte;
+ ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
+
+ Assert(resultRelInfo);
+ Assert(((Param *) tle->expr)->paramid == ((PgFdwScanState *) fsstate->fdw_state)->tableoid_param_id);
+
+ is_tableoid = true;
+
+ rte = rt_fetch(resultRelInfo->ri_RangeTableIndex,
+ estate->es_range_table);
+
+ relname = get_rel_name(rte->relid);
+ }
else
errcontext("processing expression at position %d in select list",
errpos->cur_attno);
***************
*** 5774,5779 **** conversion_error_callback(void *arg)
--- 5988,5995 ----
{
if (is_wholerow)
errcontext("whole-row reference to foreign table \"%s\"", relname);
+ else if (is_tableoid)
+ errcontext("remote tableoid of 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
***************
*** 1228,1233 **** SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
--- 1228,1266 ----
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+ -- Test UPDATE/DELETE in the case where the remote target is
+ -- an inheritance tree
+ 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::regclass, ctid, * FROM fp1;
+ -- Update statement should not be pushed down to the remote side
+ 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::regclass, ctid, * FROM fp1;
+ -- Recreate the table data
+ TRUNCATE c1;
+ TRUNCATE c2;
+ INSERT INTO c1 VALUES (0, 1);
+ INSERT INTO c2 VALUES (1, 1);
+ SELECT tableoid::regclass, ctid, * FROM fp1;
+ -- Delete statement should not be pushed down to the remote side
+ 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::regclass, ctid, * FROM fp1;
+
+ -- cleanup
+ DROP FOREIGN TABLE fp1;
+ DROP TABLE p1 CASCADE;
+
-- ===================================================================
-- test check constraints
-- ===================================================================
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
***************
*** 440,445 **** AddForeignUpdateTargets(Query *parsetree,
--- 440,454 ----
</para>
<para>
+ This function may also add to the targetlist <structname>Param</structname>
+ nodes representing extra target information, in which case
+ <function>IterateForeignScan</function> must set the values of these parameters
+ for each row fetched from the foreign source; it's recommended to use
+ <function>generate_foreign_param</function> to build the
+ <structname>Param</structname> nodes.
+ </para>
+
+ <para>
Although this function is called during planning, the
information provided is a bit different from that available to other
planning routines.
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2272,2277 **** _outPlannerGlobal(StringInfo str, const PlannerGlobal *node)
--- 2272,2278 ----
WRITE_BOOL_FIELD(parallelModeOK);
WRITE_BOOL_FIELD(parallelModeNeeded);
WRITE_CHAR_FIELD(maxParallelHazard);
+ WRITE_BITMAPSET_FIELD(foreignParamIDs);
}
static void
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 892,897 **** use_physical_tlist(PlannerInfo *root, Path *path, int flags)
--- 892,917 ----
}
}
+ /*
+ * Also, can't do it to a ForeignPath if the path is requested to emit
+ * Params generated by the FDW.
+ */
+ if (IsA(path, ForeignPath) &&
+ path->parent->relid == root->parse->resultRelation &&
+ !bms_is_empty(root->glob->foreignParamIDs))
+ {
+ foreach(lc, path->pathtarget->exprs)
+ {
+ Param *param = (Param *) lfirst(lc);
+
+ if (param && IsA(param, Param))
+ {
+ Assert(IS_FOREIGN_PARAM(root, param));
+ return false;
+ }
+ }
+ }
+
return true;
}
*** 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. Note that it's 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)
***************
*** 178,183 **** build_base_rel_tlists(PlannerInfo *root, List *final_tlist)
--- 188,206 ----
list_free(having_vars);
}
}
+
+ /*
+ * If this is an UPDATE/DELETE on a foreign table, add targetlist entries
+ * for Params the FDW generated (if any) to the result relation.
+ */
+ if (root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE)
+ {
+ int result_relation = root->parse->resultRelation;
+
+ if (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,
--- 264,296 ----
}
}
+ /*
+ * add_params_to_result_rel
+ * If the query's final tlist contains Params the FDW generated, add
+ * targetlist entries for each such Param to the result relation.
+ */
+ 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 && IsA(param, Param) &&
+ IS_FOREIGN_PARAM(root, param))
+ {
+ /* 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
***************
*** 313,318 **** standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
--- 313,319 ----
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
***************
*** 481,492 **** 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)
--- 482,497 ----
}
/*
! * 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 generated 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 generated 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 generated 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
***************
*** 2536,2542 **** finalize_plan(PlannerInfo *root, Plan *plan,
finalize_primnode((Node *) fscan->fdw_recheck_quals,
&context);
! /* We assume fdw_scan_tlist cannot contain Params */
context.paramids = bms_add_members(context.paramids,
scan_params);
}
--- 2536,2546 ----
finalize_primnode((Node *) fscan->fdw_recheck_quals,
&context);
! /*
! * We assume fdw_scan_tlist cannot contain Params other than
! * ones generated by the FDW, which are never used for
! * changed-param signaling.
! */
context.paramids = bms_add_members(context.paramids,
scan_params);
}
***************
*** 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 */
}
--- 2901,2912 ----
{
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,479 ----
/*
+ * Generate a new Param node needed for an UPDATE/DELETE on a foreign table
+ *
+ * This is used by the FDW to build PARAM_EXEC Params representing extra
+ * information to ensure that it can identify the exact row to update or
+ * delete.
+ */
+ 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,936 ----
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 (Note: keep this in sync with
+ * set_rel_width())
+ */
+ 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 generated 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 is a PARAM_EXEC Param generated 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);
/*
