Hi, On Fri, Oct 3, 2025 at 5:51 PM Etsuro Fujita <[email protected]> wrote: > > Sorry, I don't fully understand you here, but I think that when > updating a foreign table via ForeignModify, we 1) should retrieve > tabloid from the remote if the foreign table points to a remote > partitioned/inherited table, and 2) should not otherwise, to avoid > useless data transmission. Is that possible?
I'm very new to the optimizer/planner code, so I haven't been able to achieve this. In my previous letter I told, that we can retrieve tableoid and compare it with the oid of the remote table. If they are not matched, we will know that the remote table is partitioned. Then we can refresh some meta info (for example a new field in ForeignTable structure) in order to be sure that tableoid is needed in the future. And vice versa, we can understand that tableoid is not needed. So the answer to your question is "no" by now. Because I don't see any sane way to figure out whether the remote table is partitioned. > > > > I wonder whether we'd be better off thinking of a way to let FDWs > > > invent additional system column IDs for their tables, so that > > > something like a remote table OID could be represented in the > > > natural way as a Var with negative varattno. > > > > May I ask you to tell me more details? As far as I understand, if > > postgres_fdw doesn't use direct modify, it will hardcode query like > > this anyway : > > appendStringInfoString(buf, "DELETE FROM "); > > deparseRelation(buf, rel); > > appendStringInfoString(buf, " WHERE ctid = $1"); > > > > At the moment when we set the ctid in this way, the situation inevitably > > moves towards an error. > > > > Are you suggesting using another condition for the WHERE clause in > > this case (based on the new "remote table oid" column)? > > No, he is mentioning how we should manage tableoid retrieved from the > remote during query execution (from ForeignScan node to ModifyTable > node), and yes, if we addressed this, we could modify the deparser > code to add a "tableoid = X" constraint to the WHERE condition when > updating a remote partitioned/inherited tale. > OK, I got it. Thanks! So far, I've just been examining your patch and found that it doesn't apply on the newest master and fails the regression tests. I attach rebased + the corrected (in the sense of passing tests) version of your patch. I hope it will be usable. I had also add new test to the postgres_fdw.sql to show that the described problem doesn't occur anymore. The main problem of my version of the patch is that tableoid requests may be redundant. But since the problem is important for us, I'll continue working on it. -- Best regards, Daniil Davydov
From e95bf7a0ca3eccda80e6e0fc461803b99401f1f9 Mon Sep 17 00:00:00 2001 From: Daniil Davidov <[email protected]> Date: Thu, 23 Oct 2025 13:12:23 +0700 Subject: [PATCH] Teach postgres_fdw to retrieve tableoid from remote table It allows us to avoid bug with deleting excess tuples from remote partitioned table Tags: commitfest_hotfix --- contrib/postgres_fdw/deparse.c | 64 ++- .../postgres_fdw/expected/postgres_fdw.out | 484 ++++++++++-------- contrib/postgres_fdw/postgres_fdw.c | 179 ++++++- contrib/postgres_fdw/postgres_fdw.h | 3 + contrib/postgres_fdw/sql/postgres_fdw.sql | 44 ++ src/backend/optimizer/path/allpaths.c | 20 + src/backend/optimizer/plan/createplan.c | 19 + src/backend/optimizer/plan/initsplan.c | 45 ++ src/backend/optimizer/plan/planner.c | 14 +- src/backend/optimizer/plan/setrefs.c | 56 ++ src/backend/optimizer/plan/subselect.c | 7 +- src/backend/optimizer/util/appendinfo.c | 27 + src/backend/optimizer/util/relnode.c | 21 + src/backend/utils/adt/ruleutils.c | 2 +- src/include/nodes/pathnodes.h | 8 + src/include/nodes/primnodes.h | 1 + 16 files changed, 758 insertions(+), 236 deletions(-) diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index f2fb0051843..0e02f5e29a5 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -48,6 +48,7 @@ #include "catalog/pg_ts_dict.h" #include "catalog/pg_type.h" #include "commands/defrem.h" +#include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "nodes/plannodes.h" #include "optimizer/optimizer.h" @@ -132,6 +133,7 @@ static void deparseTargetList(StringInfo buf, Relation rel, bool is_returning, Bitmapset *attrs_used, + bool tableoid_needed, bool qualify_col, List **retrieved_attrs); static void deparseExplicitTargetList(List *tlist, @@ -1235,6 +1237,23 @@ build_tlist_to_deparse(RelOptInfo *foreignrel) 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; } @@ -1390,7 +1409,9 @@ deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs, Relation rel = table_open(rte->relid, NoLock); deparseTargetList(buf, rte, foreignrel->relid, rel, false, - fpinfo->attrs_used, false, retrieved_attrs); + fpinfo->attrs_used, + fpinfo->tableoid_param != NULL, + false, retrieved_attrs); table_close(rel, NoLock); } } @@ -1441,6 +1462,7 @@ deparseTargetList(StringInfo buf, Relation rel, bool is_returning, Bitmapset *attrs_used, + bool tableoid_needed, bool qualify_col, List **retrieved_attrs) { @@ -1497,6 +1519,20 @@ deparseTargetList(StringInfo buf, *retrieved_attrs = lappend_int(*retrieved_attrs, SelfItemPointerAttributeNumber); + + if (tableoid_needed && !qualify_col) + { + Assert(!first); + Assert(!is_returning); + + appendStringInfoString(buf, ", "); + if (qualify_col) + ADD_REL_QUALIFIER(buf, rtindex); + appendStringInfoString(buf, "tableoid"); + + *retrieved_attrs = lappend_int(*retrieved_attrs, + TableOidAttributeNumber); + } } /* Don't generate bad syntax if no undropped columns */ @@ -2259,7 +2295,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte, deparseRelation(buf, rel); appendStringInfoString(buf, " SET "); - pindex = 2; /* ctid is always the first param */ + pindex = 3; /* ctid is always the first param */ first = true; foreach(lc, targetAttrs) { @@ -2279,7 +2315,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte, pindex++; } } - appendStringInfoString(buf, " WHERE ctid = $1"); + appendStringInfoString(buf, " WHERE ctid = $1 AND tableoid = $2"); deparseReturningList(buf, rte, rtindex, rel, rel->trigdesc && rel->trigdesc->trig_update_after_row, @@ -2397,7 +2433,7 @@ deparseDeleteSql(StringInfo buf, RangeTblEntry *rte, { appendStringInfoString(buf, "DELETE FROM "); deparseRelation(buf, rel); - appendStringInfoString(buf, " WHERE ctid = $1"); + appendStringInfoString(buf, " WHERE ctid = $1 AND tableoid = $2"); deparseReturningList(buf, rte, rtindex, rel, rel->trigdesc && rel->trigdesc->trig_delete_after_row, @@ -2512,7 +2548,7 @@ deparseReturningList(StringInfo buf, RangeTblEntry *rte, } if (attrs_used != NULL) - deparseTargetList(buf, rte, rtindex, rel, true, attrs_used, false, + deparseTargetList(buf, rte, rtindex, rel, true, attrs_used, false, false, retrieved_attrs); else *retrieved_attrs = NIL; @@ -2782,7 +2818,7 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte, appendStringInfoString(buf, "ROW("); deparseTargetList(buf, rte, varno, rel, false, attrs_used, qualify_col, - &retrieved_attrs); + qualify_col, &retrieved_attrs); appendStringInfoChar(buf, ')'); /* Complete the CASE WHEN statement started above. */ @@ -3167,6 +3203,22 @@ deparseConst(Const *node, deparse_expr_cxt *context, int showtype) 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, the value + * needs to be produced; fetch the remote table 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; diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index cd28126049d..0c348cf0982 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -5245,14 +5245,14 @@ BEGIN; EXPLAIN (verbose, costs off) UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7b' WHERE c1 % 10 = 7 AND c1 < 40 RETURNING old.*, new.*; -- can't be pushed down - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------- Update on public.ft2 Output: old.c1, old.c2, old.c3, old.c4, old.c5, old.c6, old.c7, old.c8, new.c1, new.c2, new.c3, new.c4, new.c5, new.c6, new.c7, new.c8 - Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 + Remote SQL: UPDATE "S 1"."T 1" SET c2 = $3, c3 = $4 WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 -> Foreign Scan on public.ft2 - Output: (c2 + 400), (c3 || '_update7b'::text), ctid, ft2.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" < 40)) AND ((("C 1" % 10) = 7)) FOR UPDATE + Output: (c2 + 400), (c3 || '_update7b'::text), ctid, $0, ft2.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" < 40)) AND ((("C 1" % 10) = 7)) FOR UPDATE (6 rows) UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7b' WHERE c1 % 10 = 7 AND c1 < 40 @@ -5399,14 +5399,14 @@ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; BEGIN; EXPLAIN (verbose, costs off) DELETE FROM ft2 WHERE c1 % 10 = 6 AND c1 < 40 RETURNING old.c1, c4; -- can't be pushed down - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------- Delete on public.ft2 Output: old.c1, c4 - Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c4 + Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c4 -> Foreign Scan on public.ft2 - Output: ctid - Remote SQL: SELECT ctid FROM "S 1"."T 1" WHERE (("C 1" < 40)) AND ((("C 1" % 10) = 6)) FOR UPDATE + Output: ctid, $0 + Remote SQL: SELECT ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" < 40)) AND ((("C 1" % 10) = 6)) FOR UPDATE (6 rows) DELETE FROM ft2 WHERE c1 % 10 = 6 AND c1 < 40 RETURNING old.c1, c4; @@ -6349,27 +6349,27 @@ BEGIN; FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1 RETURNING old, new, ft2, ft2.*, ft4, ft4.*; -- can't be pushed down - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on public.ft2 Output: old.*, new.*, ft2.*, ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.*, ft4.c1, ft4.c2, ft4.c3 - Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", 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 - Output: 'bar'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3 + Output: 'bar'::text, ft2.ctid, ($0), ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3 Relations: ((public.ft2) INNER JOIN (public.ft4)) INNER JOIN (public.ft5) - Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, 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, r2.c2, r2.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 1200)))) INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1)))) FOR UPDATE OF r1 + Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, 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, r2.c2, r2.c3, r1.tableoid FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 1200)))) INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1)))) FOR UPDATE OF r1 -> Nested Loop - Output: ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3 + Output: ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ($0) Join Filter: (ft4.c1 = ft5.c1) -> Sort - Output: ft2.ctid, ft2.*, ft2.c2, ft4.*, ft4.c1, ft4.c2, ft4.c3 + Output: ft2.ctid, ft2.*, ($0), ft2.c2, ft4.*, ft4.c1, ft4.c2, ft4.c3 Sort Key: ft2.c2 -> Hash Join - Output: ft2.ctid, ft2.*, ft2.c2, ft4.*, ft4.c1, ft4.c2, ft4.c3 + Output: ft2.ctid, ft2.*, ($0), ft2.c2, ft4.*, ft4.c1, ft4.c2, ft4.c3 Hash Cond: (ft2.c2 = ft4.c1) -> Foreign Scan on public.ft2 - Output: ft2.ctid, ft2.*, ft2.c2 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1200)) FOR UPDATE + Output: ft2.ctid, ft2.*, $0, ft2.c2 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" > 1200)) FOR UPDATE -> Hash Output: ft4.*, ft4.c1, ft4.c2, ft4.c3 -> Foreign Scan on public.ft4 @@ -6447,13 +6447,13 @@ UPDATE ft2 AS target SET (c2, c7) = ( FROM ft2 AS src WHERE target.c1 = src.c1 ) WHERE c1 > 1100; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- Update on public.ft2 target - Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1 + Remote SQL: UPDATE "S 1"."T 1" SET c2 = $3, c7 = $4 WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan on public.ft2 target - Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), target.ctid, target.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), target.ctid, $3, target.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE SubPlan multiexpr_1 -> Foreign Scan on public.ft2 src Output: (src.c2 * 10), src.c7 @@ -6475,20 +6475,20 @@ UPDATE ft2 AS target SET (c2) = ( EXPLAIN (VERBOSE, COSTS OFF) UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1000; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Update on public.ft2 d - Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1 + Remote SQL: UPDATE "S 1"."T 1" SET c2 = $3 WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan - Output: CASE WHEN (random() >= '0'::double precision) THEN d.c2 ELSE 0 END, d.ctid, d.*, t.* + Output: CASE WHEN (random() >= '0'::double precision) THEN d.c2 ELSE 0 END, d.ctid, ($0), d.*, t.* Relations: (public.ft2 d) INNER JOIN (public.ft2 t) - Remote SQL: SELECT r1.c2, r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")) AND ((r1."C 1" > 1000)))) FOR UPDATE OF r1 + Remote SQL: SELECT r1.c2, r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END, r1.tableoid FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")) AND ((r1."C 1" > 1000)))) FOR UPDATE OF r1 -> Hash Join - Output: d.c2, d.ctid, d.*, t.* + Output: d.c2, d.ctid, d.*, t.*, ($0) Hash Cond: (d.c1 = t.c1) -> Foreign Scan on public.ft2 d - Output: d.c2, d.ctid, d.*, d.c1 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1000)) ORDER BY "C 1" ASC NULLS LAST FOR UPDATE + Output: d.c2, d.ctid, d.*, $0, d.c1 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" > 1000)) ORDER BY "C 1" ASC NULLS LAST FOR UPDATE -> Hash Output: t.*, t.c1 -> Foreign Scan on public.ft2 t @@ -6505,15 +6505,15 @@ 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 ----------------------------------------------------------------------------------------------------------- + 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 + 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: 'bar'::text, ctid, ft2.* + Output: 'bar'::text, ctid, $0, ft2.* Filter: (postgres_fdw_abs(ft2.c1) > 2000) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE + Remote SQL: SELECT "C 1", c2, c3, 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 *; @@ -6540,13 +6540,13 @@ 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 + 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: 'baz'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 + Output: 'baz'::text, ft2.ctid, ($0), ft2.*, 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.ctid, ft2.*, ft2.c2 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE + Output: ft2.ctid, ft2.*, $0, ft2.c2 + Remote SQL: SELECT "C 1", c2, c3, 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) @@ -6578,24 +6578,24 @@ 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 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 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 + Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3 -> Foreign Scan - Output: ft2.ctid, ft4.*, ft5.* + 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 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 + 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 + Output: ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1, ($0) -> Nested Loop - Output: ft2.ctid, ft4.*, ft4.c1 + Output: ft2.ctid, ($0), 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 + 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" @@ -7107,19 +7107,19 @@ SET enable_hashjoin TO false; SET enable_material TO false; EXPLAIN (VERBOSE, COSTS OFF) UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------- Update on public.remt2 Output: remt2.c1, remt2.c2 - Remote SQL: UPDATE public.loct2 SET c2 = $2 WHERE ctid = $1 RETURNING c1, c2 + Remote SQL: UPDATE public.loct2 SET c2 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING c1, c2 -> Nested Loop - Output: (remt2.c2 || remt2.c2), remt2.ctid, remt2.*, loct1.ctid + Output: (remt2.c2 || remt2.c2), remt2.ctid, ($0), remt2.*, loct1.ctid Join Filter: (remt2.c1 = loct1.c1) -> Seq Scan on public.loct1 Output: loct1.ctid, loct1.c1 -> Foreign Scan on public.remt2 - Output: remt2.c2, remt2.ctid, remt2.*, remt2.c1 - Remote SQL: SELECT c1, c2, ctid FROM public.loct2 FOR UPDATE + Output: remt2.c2, remt2.ctid, remt2.*, $0, remt2.c1 + Remote SQL: SELECT c1, c2, ctid, tableoid FROM public.loct2 FOR UPDATE (11 rows) UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*; @@ -7280,13 +7280,13 @@ SELECT * FROM foreign_tbl; EXPLAIN (VERBOSE, COSTS OFF) UPDATE rw_view SET b = b + 5; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------- Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + 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.b + 5), foreign_tbl.ctid, foreign_tbl.* - Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE + Output: (foreign_tbl.b + 5), foreign_tbl.ctid, $0, foreign_tbl.* + 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 @@ -7294,13 +7294,13 @@ 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 ---------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------- Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + 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.b + 15), foreign_tbl.ctid, foreign_tbl.* - Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE + Output: (foreign_tbl.b + 15), foreign_tbl.ctid, $0, foreign_tbl.* + 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 @@ -7393,14 +7393,14 @@ SELECT * FROM foreign_tbl; EXPLAIN (VERBOSE, COSTS OFF) UPDATE rw_view SET b = b + 5; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------- Update on public.parent_tbl Foreign Update on public.foreign_tbl parent_tbl_1 - Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + Remote SQL: UPDATE public.child_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: (parent_tbl_1.b + 5), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* - Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE + Output: (parent_tbl_1.b + 5), parent_tbl_1.tableoid, parent_tbl_1.ctid, $0, parent_tbl_1.* + 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 @@ -7408,14 +7408,14 @@ 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 -------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------- Update on public.parent_tbl Foreign Update on public.foreign_tbl parent_tbl_1 - Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + Remote SQL: UPDATE public.child_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: (parent_tbl_1.b + 15), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* - Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE + Output: (parent_tbl_1.b + 15), parent_tbl_1.tableoid, parent_tbl_1.ctid, $0, parent_tbl_1.* + 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 @@ -7464,14 +7464,14 @@ CREATE VIEW rw_view AS SELECT * FROM parent_tbl WHERE a < 5 WITH CHECK OPTION; INSERT INTO parent_tbl (a) VALUES(1),(5); EXPLAIN (VERBOSE, COSTS OFF) UPDATE rw_view SET b = 'text', c = 123.456; - QUERY PLAN -------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------ Update on public.parent_tbl Foreign Update on public.child_foreign parent_tbl_1 - Remote SQL: UPDATE public.child_local SET b = $2, c = $3 WHERE ctid = $1 RETURNING a + Remote SQL: UPDATE public.child_local SET b = $3, c = $4 WHERE ctid = $1 AND tableoid = $2 RETURNING a -> Foreign Scan on public.child_foreign parent_tbl_1 - Output: 'text'::text, 123.456, parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* - Remote SQL: SELECT b, c, a, ctid FROM public.child_local WHERE ((a < 5)) FOR UPDATE + Output: 'text'::text, 123.456, parent_tbl_1.tableoid, parent_tbl_1.ctid, $0, parent_tbl_1.* + Remote SQL: SELECT b, c, a, ctid, tableoid FROM public.child_local WHERE ((a < 5)) FOR UPDATE (6 rows) UPDATE rw_view SET b = 'text', c = 123.456; @@ -7550,13 +7550,13 @@ insert into grem1 (a) values (1), (2); insert into grem1 (a) values (1), (2); explain (verbose, costs off) update grem1 set a = 22 where a = 2; - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Update on public.grem1 - Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT, c = DEFAULT WHERE ctid = $1 + Remote SQL: UPDATE public.gloc1 SET a = $3, b = DEFAULT, c = DEFAULT WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan on public.grem1 - Output: 22, ctid, grem1.* - Remote SQL: SELECT a, b, c, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE + Output: 22, ctid, $0, grem1.* + Remote SQL: SELECT a, b, c, ctid, tableoid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE (5 rows) update grem1 set a = 22 where a = 2; @@ -7883,13 +7883,13 @@ SELECT * from loc1; EXPLAIN (verbose, costs off) UPDATE rem1 set f1 = 10; -- all columns should be transmitted - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------- Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1 + Remote SQL: UPDATE public.loc1 SET f1 = $3, f2 = $4 WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan on public.rem1 - Output: 10, ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE + Output: 10, ctid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) UPDATE rem1 set f1 = 10; @@ -8031,12 +8031,12 @@ DELETE FROM rem1; -- can be pushed down EXPLAIN (verbose, costs off) DELETE FROM rem1 WHERE false; -- currently can't be pushed down - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 + Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 AND tableoid = $2 -> Result - Output: ctid + Output: ctid, $0 Replaces: Scan on rem1 One-Time Filter: false (6 rows) @@ -8137,13 +8137,13 @@ 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 ------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------- Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1 + Remote SQL: UPDATE public.loc1 SET f1 = $3, f2 = $4 WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan on public.rem1 - Output: ''::text, ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE + Output: ''::text, ctid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) EXPLAIN (verbose, costs off) @@ -8161,13 +8161,13 @@ 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 -------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------- Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2 + Remote SQL: UPDATE public.loc1 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2 -> Foreign Scan on public.rem1 - Output: ''::text, ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE + Output: ''::text, ctid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) EXPLAIN (verbose, costs off) @@ -8195,13 +8195,13 @@ UPDATE rem1 set f2 = ''; -- can be pushed down EXPLAIN (verbose, costs off) DELETE FROM rem1; -- can't be pushed down - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------- Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 + Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan on public.rem1 - Output: ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE + 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; @@ -8219,13 +8219,13 @@ UPDATE rem1 set f2 = ''; -- can be pushed down EXPLAIN (verbose, costs off) DELETE FROM rem1; -- can't be pushed down - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------ Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 RETURNING f1, f2 + Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2 -> Foreign Scan on public.rem1 - Output: ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE + 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; @@ -8262,28 +8262,28 @@ CONTEXT: COPY parent_tbl, line 1: "AAA 42" ALTER SERVER loopback OPTIONS (DROP batch_size); EXPLAIN (VERBOSE, COSTS OFF) UPDATE parent_tbl SET b = b + 1; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------- Update on public.parent_tbl Foreign Update on public.foreign_tbl parent_tbl_1 - Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.local_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: (parent_tbl_1.b + 1), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* - Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE + Output: (parent_tbl_1.b + 1), parent_tbl_1.tableoid, parent_tbl_1.ctid, $0, parent_tbl_1.* + Remote SQL: SELECT a, b, ctid, tableoid FROM public.local_tbl FOR UPDATE (6 rows) UPDATE parent_tbl SET b = b + 1; ERROR: cannot collect transition tuples from child foreign tables EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM parent_tbl; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- Delete on public.parent_tbl Foreign Delete on public.foreign_tbl parent_tbl_1 - Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1 + Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1 AND tableoid = $2 -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: parent_tbl_1.tableoid, parent_tbl_1.ctid - Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE + Output: parent_tbl_1.tableoid, parent_tbl_1.ctid, $0 + Remote SQL: SELECT ctid, tableoid FROM public.local_tbl FOR UPDATE (6 rows) DELETE FROM parent_tbl; @@ -8301,39 +8301,41 @@ CREATE TRIGGER parent_tbl_delete_trig FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); EXPLAIN (VERBOSE, COSTS OFF) UPDATE parent_tbl SET b = b + 1; - QUERY PLAN ------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Update on public.parent_tbl Update on public.parent_tbl parent_tbl_1 Foreign Update on public.foreign_tbl parent_tbl_2 - Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.local_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2 -> Result - Output: (parent_tbl.b + 1), parent_tbl.tableoid, parent_tbl.ctid, (NULL::record) + Output: (parent_tbl.b + 1), parent_tbl.tableoid, parent_tbl.ctid, $0, (NULL::record) -> Append -> Seq Scan on public.parent_tbl parent_tbl_1 Output: parent_tbl_1.b, parent_tbl_1.tableoid, parent_tbl_1.ctid, NULL::record -> Foreign Scan on public.foreign_tbl parent_tbl_2 - Output: parent_tbl_2.b, parent_tbl_2.tableoid, parent_tbl_2.ctid, parent_tbl_2.* - Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE + Output: parent_tbl_2.b, parent_tbl_2.tableoid, parent_tbl_2.ctid, parent_tbl_2.*, $0 + Remote SQL: SELECT a, b, ctid, tableoid FROM public.local_tbl FOR UPDATE (12 rows) UPDATE parent_tbl SET b = b + 1; ERROR: cannot collect transition tuples from child foreign tables EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM parent_tbl; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------- Delete on public.parent_tbl Delete on public.parent_tbl parent_tbl_1 Foreign Delete on public.foreign_tbl parent_tbl_2 - Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1 - -> Append - -> Seq Scan on public.parent_tbl parent_tbl_1 - Output: parent_tbl_1.tableoid, parent_tbl_1.ctid - -> Foreign Scan on public.foreign_tbl parent_tbl_2 - Output: parent_tbl_2.tableoid, parent_tbl_2.ctid - Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE -(10 rows) + Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1 AND tableoid = $2 + -> Result + Output: parent_tbl.tableoid, parent_tbl.ctid, $0 + -> Append + -> Seq Scan on public.parent_tbl parent_tbl_1 + Output: parent_tbl_1.tableoid, parent_tbl_1.ctid + -> Foreign Scan on public.foreign_tbl parent_tbl_2 + Output: parent_tbl_2.tableoid, parent_tbl_2.ctid, $0 + Remote SQL: SELECT ctid, tableoid FROM public.local_tbl FOR UPDATE +(12 rows) DELETE FROM parent_tbl; ERROR: cannot collect transition tuples from child foreign tables @@ -8675,22 +8677,22 @@ drop table foo2child; -- 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 -------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------- Update on public.bar Update on public.bar bar_1 Foreign Update on public.bar2 bar_2 - Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2 -> Hash Join - Output: (bar.f2 + 100), foo.ctid, bar.tableoid, bar.ctid, (NULL::record), foo.*, foo.tableoid + Output: (bar.f2 + 100), foo.ctid, bar.tableoid, bar.ctid, $0, (NULL::record), foo.*, foo.tableoid Inner Unique: true Hash Cond: (bar.f1 = foo.f1) -> Append -> Seq Scan on public.bar bar_1 Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.*, $0 + Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE -> Hash Output: foo.ctid, foo.f1, foo.*, foo.tableoid -> HashAggregate @@ -8722,14 +8724,14 @@ 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 ------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------ Update on public.bar Update on public.bar bar_1 Foreign Update on public.bar2 bar_2 - Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2 -> Merge Join - Output: (bar.f2 + 100), (ROW(foo.f1)), bar.tableoid, bar.ctid, (NULL::record) + Output: (bar.f2 + 100), (ROW(foo.f1)), bar.tableoid, bar.ctid, $0, (NULL::record) Merge Cond: (bar.f1 = foo.f1) -> Sort Output: bar.f2, bar.f1, bar.tableoid, bar.ctid, (NULL::record) @@ -8738,8 +8740,8 @@ where bar.f1 = ss.f1; -> Seq Scan on public.bar bar_1 Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.*, $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 @@ -8880,19 +8882,21 @@ ERROR: WHERE CURRENT OF is not supported for this table type rollback; explain (verbose, costs off) delete from foo where f1 < 5 returning *; - QUERY PLAN --------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------- Delete on public.foo Output: foo_1.f1, foo_1.f2 Delete on public.foo foo_1 Foreign Delete on public.foo2 foo_2 - -> Append - -> Index Scan using i_foo_f1 on public.foo foo_1 - Output: foo_1.tableoid, foo_1.ctid - Index Cond: (foo_1.f1 < 5) - -> Foreign Delete on public.foo2 foo_2 - Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2 -(10 rows) + -> Result + Output: foo.tableoid, foo.ctid, $0 + -> Append + -> Index Scan using i_foo_f1 on public.foo foo_1 + Output: foo_1.tableoid, foo_1.ctid + Index Cond: (foo_1.f1 < 5) + -> Foreign Delete on public.foo2 foo_2 + Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2 +(12 rows) delete from foo where f1 < 5 returning *; f1 | f2 @@ -8913,7 +8917,7 @@ update bar set f2 = f2 + 100 returning *; Update on public.bar bar_1 Foreign Update on public.bar2 bar_2 -> Result - Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record) + Output: (bar.f2 + 100), bar.tableoid, bar.ctid, $0, (NULL::record) -> Append -> Seq Scan on public.bar bar_1 Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record @@ -8941,20 +8945,20 @@ 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 --------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- Update on public.bar Update on public.bar bar_1 Foreign Update on public.bar2 bar_2 - Remote SQL: UPDATE public.loct2 SET f1 = $2, f2 = $3, f3 = $4 WHERE ctid = $1 RETURNING f1, f2, f3 + Remote SQL: UPDATE public.loct2 SET f1 = $3, f2 = $4, f3 = $5 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2, f3 -> Result - Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record) + Output: (bar.f2 + 100), bar.tableoid, bar.ctid, $0, (NULL::record) -> Append -> Seq Scan on public.bar bar_1 Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f2, bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + Output: bar_2.f2, bar_2.tableoid, bar_2.ctid, bar_2.*, $0 + Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE (12 rows) update bar set f2 = f2 + 100; @@ -8972,20 +8976,22 @@ 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 ---------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- Delete on public.bar Delete on public.bar bar_1 Foreign Delete on public.bar2 bar_2 - Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3 - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.tableoid, bar_1.ctid, NULL::record - Filter: (bar_1.f2 < 400) - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE -(11 rows) + Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2, f3 + -> Result + Output: bar.tableoid, bar.ctid, $0, (NULL::record) + -> Append + -> Seq Scan on public.bar bar_1 + Output: bar_1.tableoid, bar_1.ctid, NULL::record + Filter: (bar_1.f2 < 400) + -> Foreign Scan on public.bar2 bar_2 + Output: bar_2.tableoid, bar_2.ctid, bar_2.*, $0 + Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE +(13 rows) delete from bar where f2 < 400; NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON bar2 @@ -9016,22 +9022,22 @@ analyze remt1; analyze remt2; explain (verbose, costs off) update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------- Update on public.parent Output: parent_1.a, parent_1.b, remt2.a, remt2.b Update on public.parent parent_1 Foreign Update on public.remt1 parent_2 - Remote SQL: UPDATE public.loct1 SET b = $2 WHERE ctid = $1 RETURNING a, b + Remote SQL: UPDATE public.loct1 SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b -> Nested Loop - Output: (parent.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.tableoid, parent.ctid, (NULL::record) + Output: (parent.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.tableoid, parent.ctid, $0, (NULL::record) Join Filter: (parent.a = remt2.a) -> Append -> Seq Scan on public.parent parent_1 Output: parent_1.b, parent_1.a, parent_1.tableoid, parent_1.ctid, NULL::record -> Foreign Scan on public.remt1 parent_2 - Output: parent_2.b, parent_2.a, parent_2.tableoid, parent_2.ctid, parent_2.* - Remote SQL: SELECT a, b, ctid FROM public.loct1 FOR UPDATE + Output: parent_2.b, parent_2.a, parent_2.tableoid, parent_2.ctid, parent_2.*, $0 + Remote SQL: SELECT a, b, ctid, tableoid FROM public.loct1 FOR UPDATE -> Materialize Output: remt2.b, remt2.*, remt2.a -> Foreign Scan on public.remt2 @@ -9048,22 +9054,22 @@ update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a re explain (verbose, costs off) delete from parent using remt2 where parent.a = remt2.a returning parent; - QUERY PLAN ------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------- Delete on public.parent Output: parent_1.* Delete on public.parent parent_1 Foreign Delete on public.remt1 parent_2 - Remote SQL: DELETE FROM public.loct1 WHERE ctid = $1 RETURNING a, b + Remote SQL: DELETE FROM public.loct1 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b -> Nested Loop - Output: remt2.*, parent.tableoid, parent.ctid + Output: remt2.*, parent.tableoid, parent.ctid, $0 Join Filter: (parent.a = remt2.a) -> Append -> Seq Scan on public.parent parent_1 Output: parent_1.a, parent_1.tableoid, parent_1.ctid -> Foreign Scan on public.remt1 parent_2 - Output: parent_2.a, parent_2.tableoid, parent_2.ctid - Remote SQL: SELECT a, ctid FROM public.loct1 FOR UPDATE + Output: parent_2.a, parent_2.tableoid, parent_2.ctid, $0 + Remote SQL: SELECT a, ctid, tableoid FROM public.loct1 FOR UPDATE -> Materialize Output: remt2.*, remt2.a -> Foreign Scan on public.remt2 @@ -9293,7 +9299,7 @@ update utrtest set a = 1 where a = 1 or a = 2 returning *; -> Foreign Update on public.remp utrtest_1 Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b -> Seq Scan on public.locp utrtest_2 - Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record + Output: 1, utrtest_2.tableoid, utrtest_2.ctid, $0, NULL::record Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2)) (10 rows) @@ -9332,8 +9338,8 @@ insert into utrtest values (2, 'qux'); -- with a direct modification plan explain (verbose, costs off) update utrtest set a = 1 returning *; - QUERY PLAN ---------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b Foreign Update on public.remp utrtest_1 @@ -9342,7 +9348,7 @@ update utrtest set a = 1 returning *; -> Foreign Update on public.remp utrtest_1 Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b -> Seq Scan on public.locp utrtest_2 - Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record + Output: 1, utrtest_2.tableoid, utrtest_2.ctid, $0, NULL::record (9 rows) update utrtest set a = 1 returning *; @@ -9353,20 +9359,20 @@ insert into utrtest values (2, 'qux'); -- with a non-direct modification plan explain (verbose, costs off) update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 Foreign Update on public.remp utrtest_1 - Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b + Remote SQL: UPDATE public.loct SET a = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b Update on public.locp utrtest_2 -> Hash Join - Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, utrtest.* + Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, $0, utrtest.* Hash Cond: (utrtest.a = "*VALUES*".column1) -> Append -> Foreign Scan on public.remp utrtest_1 - Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, utrtest_1.* - Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE + Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, utrtest_1.*, $0 + Remote SQL: SELECT a, b, ctid, tableoid FROM public.loct FOR UPDATE -> Seq Scan on public.locp utrtest_2 Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, NULL::record -> Hash @@ -9392,15 +9398,15 @@ insert into utrtest values (3, 'xyzzy'); -- with a direct modification plan explain (verbose, costs off) update utrtest set a = 3 returning *; - QUERY PLAN ---------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b Update on public.locp utrtest_1 Foreign Update on public.remp utrtest_2 -> Append -> Seq Scan on public.locp utrtest_1 - Output: 3, utrtest_1.tableoid, utrtest_1.ctid, NULL::record + Output: 3, utrtest_1.tableoid, utrtest_1.ctid, $0, NULL::record -> Foreign Update on public.remp utrtest_2 Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b (9 rows) @@ -9410,22 +9416,22 @@ ERROR: cannot route tuples into foreign table to be updated "remp" -- with a non-direct modification plan explain (verbose, costs off) update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 Update on public.locp utrtest_1 Foreign Update on public.remp utrtest_2 - Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b + Remote SQL: UPDATE public.loct SET a = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b -> Hash Join - Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, (NULL::record) + Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, $0, (NULL::record) Hash Cond: (utrtest.a = "*VALUES*".column1) -> Append -> Seq Scan on public.locp utrtest_1 Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, NULL::record -> Foreign Scan on public.remp utrtest_2 - Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.* - Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE + Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.*, $0 + Remote SQL: SELECT a, b, ctid, tableoid FROM public.loct FOR UPDATE -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" @@ -12312,8 +12318,8 @@ RESET enable_hashjoin; -- Test that UPDATE/DELETE with inherited target works with async_capable enabled EXPLAIN (VERBOSE, COSTS OFF) UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ Update on public.async_pt Output: async_pt_1.a, async_pt_1.b, async_pt_1.c Foreign Update on public.async_p1 async_pt_1 @@ -12325,7 +12331,7 @@ UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *; -> Foreign Update on public.async_p2 async_pt_2 Remote SQL: UPDATE public.base_tbl2 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c -> Seq Scan on public.async_p3 async_pt_3 - Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoid, async_pt_3.ctid, NULL::record + Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoid, async_pt_3.ctid, $0, NULL::record, $1 Filter: (async_pt_3.b = 0) (13 rows) @@ -12352,7 +12358,7 @@ DELETE FROM async_pt WHERE b = 0 RETURNING *; -> Foreign Delete on public.async_p2 async_pt_2 Remote SQL: DELETE FROM public.base_tbl2 WHERE ((b = 0)) RETURNING a, b, c -> Seq Scan on public.async_p3 async_pt_3 - Output: async_pt_3.tableoid, async_pt_3.ctid + Output: async_pt_3.tableoid, async_pt_3.ctid, $0, $1 Filter: (async_pt_3.b = 0) (13 rows) @@ -12719,3 +12725,49 @@ SELECT server_name, -- Clean up \set VERBOSITY default RESET debug_discard_caches; +-- =================================================================== +-- check whether fdw created for partitioned table will delete tuples only from +-- desired partition +-- =================================================================== +CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int +) PARTITION BY RANGE (logdate); +CREATE TABLE measurement_y2006m02 PARTITION OF measurement + FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); +CREATE TABLE measurement_y2006m03 PARTITION OF measurement + FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); +CREATE TABLE measurement_y2006m04 PARTITION OF measurement + FOR VALUES FROM ('2006-04-01') TO ('2006-05-01'); +INSERT INTO measurement VALUES (1,'2006-02-01',1,1); +INSERT INTO measurement VALUES (2,'2006-03-01',1,1); +INSERT INTO measurement VALUES (3,'2006-04-01',1,1); +create foreign table measurement_fdw ( + city_id int options (column_name 'city_id') not null, + logdate date options (column_name 'logdate') not null, + peaktemp text options (column_name 'peaktemp'), + unitsales integer options (column_name 'unitsales') +) SERVER loopback OPTIONS (table_name 'measurement'); +DELETE FROM measurement_fdw +USING ( + SELECT t1.city_id sub_city_id + FROM measurement_fdw t1 + WHERE t1.city_id=1 + LIMIT 1000 +) sub +WHERE measurement_fdw.city_id = sub.sub_city_id +RETURNING city_id, logdate, peaktemp, unitsales; + city_id | logdate | peaktemp | unitsales +---------+------------+----------+----------- + 1 | 02-01-2006 | 1 | 1 +(1 row) + +SELECT * FROM measurement_fdw; + city_id | logdate | peaktemp | unitsales +---------+------------+----------+----------- + 2 | 03-01-2006 | 1 | 1 + 3 | 04-01-2006 | 1 | 1 +(2 rows) + diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 456b267f70b..48321b7dfb3 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -76,6 +76,8 @@ enum FdwScanPrivateIndex 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, @@ -174,6 +176,9 @@ typedef struct PgFdwScanState MemoryContext temp_cxt; /* context for per-tuple temporary data */ int fetch_size; /* number of tuples per fetch */ + + int tableoid_param_id; /* Param ID for remote table OID */ + bool set_tableoid_param; /* Do we need to set the Param? */ } PgFdwScanState; /* @@ -200,6 +205,7 @@ typedef struct PgFdwModifyState /* 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 */ @@ -473,6 +479,7 @@ static TupleTableSlot **execute_foreign_modify(EState *estate, static void prepare_foreign_modify(PgFdwModifyState *fmstate); static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate, ItemPointer tupleid, + Oid tableoid, TupleTableSlot **slots, int numSlots); static void store_returning_result(PgFdwModifyState *fmstate, @@ -787,6 +794,23 @@ postgresGetForeignRelSize(PlannerInfo *root, fpinfo->hidden_subquery_rels = NULL; /* Set the relation index. */ fpinfo->relation_index = baserel->relid; + fpinfo->tableoid_param = NULL; + + /* + * If the table is an UPDATE/DELETE target, the table's reltarget would + * have contained a Param representing the remote table OID of the target; + * get the Param and save a copy of it in fpinfo for use later. + */ + 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; + } + } } /* @@ -1248,6 +1272,7 @@ postgresGetForeignPlan(PlannerInfo *root, bool has_final_sort = false; bool has_limit = false; ListCell *lc; + int tableoid_param_id = -1; /* * Get FDW private data created by postgresGetForeignUpperPaths(), if any. @@ -1412,12 +1437,16 @@ postgresGetForeignPlan(PlannerInfo *root, /* Remember remote_exprs for possible use by postgresPlanDirectModify */ fpinfo->final_remote_exprs = remote_exprs; + if (fpinfo->tableoid_param) + tableoid_param_id = fpinfo->tableoid_param->paramid; + /* * 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), + 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, @@ -1550,6 +1579,8 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) 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)); @@ -1569,11 +1600,13 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) { fsstate->rel = node->ss.ss_currentRelation; fsstate->tupdesc = RelationGetDescr(fsstate->rel); + fsstate->set_tableoid_param = (fsstate->tableoid_param_id >= 0); } else { fsstate->rel = NULL; fsstate->tupdesc = get_tupdesc_for_join_scan_tuples(node); + fsstate->set_tableoid_param = false; } fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc); @@ -1605,6 +1638,7 @@ postgresIterateForeignScan(ForeignScanState *node) { PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; TupleTableSlot *slot = node->ss.ss_ScanTupleSlot; + HeapTuple tuple; /* * In sync mode, if this is the first call after Begin or ReScan, we need @@ -1631,12 +1665,22 @@ postgresIterateForeignScan(ForeignScanState *node) return ExecClearTuple(slot); } + tuple = fsstate->tuples[fsstate->next_tuple++]; + + if (fsstate->set_tableoid_param) + { + ExprContext *econtext = node->ss.ps.ps_ExprContext; + ParamExecData *prm = &(econtext->ecxt_param_exec_vals[fsstate->tableoid_param_id]); + + prm->execPlan = NULL; + prm->value = ObjectIdGetDatum(tuple->t_tableOid); + prm->isnull = false; + } + /* * Return the next tuple. */ - ExecStoreHeapTuple(fsstate->tuples[fsstate->next_tuple++], - slot, - false); + ExecStoreHeapTuple(tuple, slot, false); return slot; } @@ -1751,6 +1795,9 @@ postgresAddForeignUpdateTargets(PlannerInfo *root, 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. @@ -1766,6 +1813,27 @@ postgresAddForeignUpdateTargets(PlannerInfo *root, /* Register it as a row-identity column needed by this target rel */ add_row_identity_var(root, var, rtindex, "ctid"); + + /* Make a Param representing the tableoid value */ + param = makeNode(Param); + param->paramkind = PARAM_EXEC; + param->paramtype = OIDOID; + param->paramtypmod = -1; + param->paramcollid = InvalidOid; + param->location = -1; + /* paramid will be filled in by fix_foreign_params */ + param->paramid = -1; + param->target_rte = rtindex; + + /* Wrap it in a resjunk TLE with the right name ... */ + attrname = "remote_tableoid"; + + tle = makeTargetEntry((Expr *) param, + list_length(root->processed_tlist) + 1, + pstrdup(attrname), + true); + /* ... and add it to the query's targetlist */ + root->processed_tlist = lappend(root->processed_tlist, tle); } /* @@ -4013,7 +4081,7 @@ 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; + n_params = list_length(fmstate->target_attrs) + 2; fmstate->p_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * n_params); fmstate->p_nums = 0; @@ -4031,6 +4099,20 @@ create_foreign_modify(EState *estate, 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, + "remote_tableoid"); + + if (!AttributeNumberIsValid(fmstate->tableoidAttno)) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("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) @@ -4083,6 +4165,7 @@ execute_foreign_modify(EState *estate, { PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState; ItemPointer ctid = NULL; + Oid tableoid = InvalidOid; const char **p_values; PGresult *res; int n_rows; @@ -4128,6 +4211,7 @@ execute_foreign_modify(EState *estate, if (operation == CMD_UPDATE || operation == CMD_DELETE) { Datum datum; + Datum datum2; bool isNull; datum = ExecGetJunkAttribute(planSlots[0], @@ -4137,10 +4221,22 @@ execute_foreign_modify(EState *estate, if (isNull) elog(ERROR, "ctid is NULL"); ctid = (ItemPointer) DatumGetPointer(datum); + + /* Get the tableoid that was passed up as a resjunk column */ + datum2 = ExecGetJunkAttribute(planSlots[0], + fmstate->tableoidAttno, + &isNull); + /* shouldn't ever get a null result... */ + if (isNull) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("tableoid is NULL"))); + + tableoid = DatumGetObjectId(datum2); } /* Convert parameters needed by prepared statement to text form */ - p_values = convert_prep_stmt_params(fmstate, ctid, slots, *numSlots); + p_values = convert_prep_stmt_params(fmstate, ctid, tableoid, slots, *numSlots); /* * Execute the prepared statement. @@ -4245,6 +4341,7 @@ prepare_foreign_modify(PgFdwModifyState *fmstate) static const char ** convert_prep_stmt_params(PgFdwModifyState *fmstate, ItemPointer tupleid, + Oid tableoid, TupleTableSlot **slots, int numSlots) { @@ -4271,6 +4368,16 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate, 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 slots */ if (slots != NULL && fmstate->target_attrs != NIL) { @@ -4282,7 +4389,7 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate, for (i = 0; i < numSlots; i++) { - j = (tupleid != NULL) ? 1 : 0; + j = (tupleid != NULL) ? 2 : 0; foreach(lc, fmstate->target_attrs) { int attnum = lfirst_int(lc); @@ -4661,6 +4768,17 @@ init_returning_filter(PgFdwDirectModifyState *dmstate, 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)); /* @@ -5972,6 +6090,38 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, /* Mark that this join can be pushed down safely */ fpinfo->pushdown_safe = true; + /* + * If the join relation contains an UPDATE/DELETE target, either of the + * input relations would have saved the Param representing the remote + * table OID of the target; get the Param and remember it in 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) { @@ -7505,6 +7655,7 @@ make_tuple_from_result_row(PGresult *res, ErrorContextCallback errcallback; MemoryContext oldcontext; ListCell *lc; + Oid tableoid = InvalidOid; int j; Assert(row < PQntuples(res)); @@ -7587,6 +7738,17 @@ make_tuple_from_result_row(PGresult *res, ctid = (ItemPointer) DatumGetPointer(datum); } } + else if (i == TableOidAttributeNumber) + { + /* tableoid */ + if (valstr != NULL) + { + Datum datum; + + datum = DirectFunctionCall1(oidin, CStringGetDatum(valstr)); + tableoid = DatumGetObjectId(datum); + } + } errpos.cur_attno = 0; j++; @@ -7618,6 +7780,9 @@ make_tuple_from_result_row(PGresult *res, if (ctid) tuple->t_self = tuple->t_data->t_ctid = *ctid; + if (OidIsValid(tableoid)) + tuple->t_tableOid = tableoid; + /* * Stomp on the xmin, xmax, and cmin fields from the tuple created by * heap_form_tuple. heap_form_tuple actually creates the tuple with diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index e69735298d7..033f1c3de74 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -129,6 +129,9 @@ typedef struct PgFdwRelationInfo * representing the relation. */ int relation_index; + + /* PARAM_EXEC Param representing the remote table OID of a target rel */ + Param *tableoid_param; } PgFdwRelationInfo; /* diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 9a8f9e28135..f7613744d73 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -4420,3 +4420,47 @@ SELECT server_name, -- Clean up \set VERBOSITY default RESET debug_discard_caches; + +-- =================================================================== +-- check whether fdw created for partitioned table will delete tuples only from +-- desired partition +-- =================================================================== + +CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int +) PARTITION BY RANGE (logdate); + +CREATE TABLE measurement_y2006m02 PARTITION OF measurement + FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); + +CREATE TABLE measurement_y2006m03 PARTITION OF measurement + FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); + +CREATE TABLE measurement_y2006m04 PARTITION OF measurement + FOR VALUES FROM ('2006-04-01') TO ('2006-05-01'); + +INSERT INTO measurement VALUES (1,'2006-02-01',1,1); +INSERT INTO measurement VALUES (2,'2006-03-01',1,1); +INSERT INTO measurement VALUES (3,'2006-04-01',1,1); + +create foreign table measurement_fdw ( + city_id int options (column_name 'city_id') not null, + logdate date options (column_name 'logdate') not null, + peaktemp text options (column_name 'peaktemp'), + unitsales integer options (column_name 'unitsales') +) SERVER loopback OPTIONS (table_name 'measurement'); + +DELETE FROM measurement_fdw +USING ( + SELECT t1.city_id sub_city_id + FROM measurement_fdw t1 + WHERE t1.city_id=1 + LIMIT 1000 +) sub +WHERE measurement_fdw.city_id = sub.sub_city_id +RETURNING city_id, logdate, peaktemp, unitsales; + +SELECT * FROM measurement_fdw; diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 9c6436eb72f..0d0a92a181b 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -1149,6 +1149,26 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, (Node *) rel->reltarget->exprs, 1, &appinfo); + /* Do it if fdw is partition */ + if (planner_rt_fetch(childRTindex, root)->relkind == RELKIND_FOREIGN_TABLE && + !bms_is_empty(root->glob->foreignParamIDs)) + { + foreach(lc, root->processed_tlist) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + Param *param = (Param *) tle->expr; + + if (tle->resjunk && IsA(param, Param) && + IS_FOREIGN_PARAM(root, param) && + param->target_rte == childRTindex) // TODO same for another case + { + /* XXX is copyObject necessary here? */ + childrel->reltarget->exprs = + lappend(childrel->reltarget->exprs, copyObject(param)); + } + } + } + /* * We have to make child entries in the EquivalenceClass data * structures as well. This is needed either if the parent diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 63fe6637155..bc654273da6 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -981,6 +981,25 @@ use_physical_tlist(PlannerInfo *root, Path *path, int flags) } } + /* + * 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; } diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index 65d473d95b6..70a7d8d125b 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -31,6 +31,7 @@ #include "optimizer/planner.h" #include "optimizer/restrictinfo.h" #include "parser/analyze.h" +#include "parser/parsetree.h" #include "rewrite/rewriteManip.h" #include "utils/lsyscache.h" #include "utils/rel.h" @@ -230,6 +231,40 @@ add_other_rels_to_query(PlannerInfo *root) * *****************************************************************************/ +/* + * 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, List *final_tlist) +{ + RelOptInfo *target_rel = find_base_rel(root, root->parse->resultRelation); + ListCell *lc; + + /* + * If no parameters have been generated by any FDWs, we certainly don't + * need to do anything here. + */ + if (bms_is_empty(root->glob->foreignParamIDs)) + return; + + 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) && + param->target_rte == target_rel->relid) + { + /* XXX is copyObject necessary here? */ + target_rel->reltarget->exprs = lappend(target_rel->reltarget->exprs, + copyObject(param)); + } + } +} + /* * build_base_rel_tlists * Add targetlist entries for each var needed in the query's final tlist @@ -269,6 +304,16 @@ build_base_rel_tlists(PlannerInfo *root, List *final_tlist) list_free(having_vars); } } + + 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, final_tlist); + + } } /* diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index c4fd646b999..48236b08c27 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -374,6 +374,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions, glob->dependsOnRole = false; glob->partition_directory = NULL; glob->rel_notnullatts_hash = NULL; + glob->foreignParamIDs = NULL; /* * Assess whether it's feasible to use parallel mode for this query. We @@ -554,12 +555,15 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions, } /* - * 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 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) + if (glob->paramExecTypes != NIL && + 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) diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index ccdc9bc264a..49f4e2ffb25 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -3253,7 +3253,42 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context) } /* Special cases (apply only AFTER failing to match to lower tlist) */ 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; + } + // XXX Is it an error to be here? + } + /* If not, do fix_param_node() */ return fix_param_node(context->root, (Param *) node); + } if (IsA(node, AlternativeSubPlan)) return fix_join_expr_mutator(fix_alternative_subplan(context->root, (AlternativeSubPlan *) node, @@ -3364,7 +3399,28 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context) } /* Special cases (apply only AFTER failing to match to lower tlist) */ 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; + } + // XXX Is it an error to be here? + } + /* If not, do fix_param_node() */ return fix_param_node(context->root, (Param *) node); + } if (IsA(node, Aggref)) { Aggref *aggref = (Aggref *) node; diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 14192a13236..22061e748a1 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -3046,7 +3046,12 @@ finalize_primnode(Node *node, finalize_primnode_context *context) { int paramid = ((Param *) node)->paramid; - context->paramids = bms_add_member(context->paramids, 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 */ } diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c index 69b8b0c2ae0..2085cff03ff 100644 --- a/src/backend/optimizer/util/appendinfo.c +++ b/src/backend/optimizer/util/appendinfo.c @@ -944,6 +944,29 @@ add_row_identity_var(PlannerInfo *root, Var *orig_var, root->processed_tlist = lappend(root->processed_tlist, tle); } +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); + } + } +} + /* * add_row_identity_columns * @@ -988,8 +1011,12 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex, fdwroutine = GetFdwRoutineForRelation(target_relation, false); if (fdwroutine->AddForeignUpdateTargets != NULL) + { + fdwroutine->AddForeignUpdateTargets(root, rtindex, target_rte, target_relation); + fix_foreign_params(root, root->processed_tlist); + } /* * For UPDATE, we need to make the FDW fetch unchanged columns by diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 1158bc194c3..61da0ecc583 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -1284,6 +1284,27 @@ build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel, } continue; } + /* + * We allow FDWs to have PARAM_EXEC Params here. + */ + else 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; + } /* * Otherwise, anything in a baserel or joinrel targetlist ought to be diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 79ec136231b..718e2669911 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8818,7 +8818,7 @@ get_parameter(Param *param, deparse_context *context) * It's a bug if we get here for anything except PARAM_EXTERN Params, but * in production builds printing $N seems more useful than failing. */ - Assert(param->paramkind == PARAM_EXTERN); + Assert(param->paramkind == PARAM_EXTERN || param->paramkind == PARAM_EXEC); appendStringInfo(context->buf, "$%d", param->paramid); } diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 30d889b54c5..af5b89a9ae0 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -189,12 +189,20 @@ typedef struct PlannerGlobal /* extension state */ void **extension_state pg_node_attr(read_write_ignore); int extension_state_allocated; + + /* PARAM_EXEC Params generated by FDWs */ + Bitmapset *foreignParamIDs; } 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 diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 1b4436f2ff6..0ba0f0e6a5e 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -401,6 +401,7 @@ typedef struct Param Oid paramcollid; /* token location, or -1 if unknown */ ParseLoc location; + Index target_rte; } Param; /* -- 2.43.0
