From 8e8d186585c1716f6573e9e0593bdec6badbc7e4 Mon Sep 17 00:00:00 2001 From: Nikita Malakhov Date: Tue, 21 Apr 2026 14:21:13 +0300 Subject: [PATCH] [POC] This patch modifies FDW engine to use remote table OID for DELETE and UPDATE of partitioned tables. [1] https://www.postgresql.org/message-id/flat/CAPmGK15CQK-oYFMAyq%2BrR0rQapUHtvAGuGgY5ahERHzZ4tmC8g%40mail.gmail.com#4321975bbd1af71c78d45d9a441e8458 --- contrib/postgres_fdw/deparse.c | 85 ++- .../postgres_fdw/expected/postgres_fdw.out | 508 ++++++++++-------- contrib/postgres_fdw/postgres_fdw.c | 308 ++++++++++- contrib/postgres_fdw/postgres_fdw.h | 3 + contrib/postgres_fdw/sql/postgres_fdw.sql | 44 ++ 5 files changed, 700 insertions(+), 248 deletions(-) diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 2dcc6c8af1b..8d566bce4bf 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, @@ -350,10 +352,11 @@ foreign_expr_walker(Node *node, /* Var belongs to foreign table */ /* - * System columns other than ctid should not be sent to - * the remote, since we don't make any effort to ensure - * that local and remote values match (tableoid, in - * particular, almost certainly doesn't match). + * System columns other than ctid and remote table oid + * should not be sent to the remote, since we don't make + * any effort to ensure that local and remote values + * match (tableoid, in particular, almost certainly + * doesn't match). */ if (var->varattno < 0 && var->varattno != SelfItemPointerAttributeNumber) @@ -1235,6 +1238,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 +1410,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 +1463,7 @@ deparseTargetList(StringInfo buf, Relation rel, bool is_returning, Bitmapset *attrs_used, + bool tableoid_needed, bool qualify_col, List **retrieved_attrs) { @@ -1499,6 +1522,22 @@ deparseTargetList(StringInfo buf, SelfItemPointerAttributeNumber); } + if (tableoid_needed && + (bms_is_member(TableOidAttributeNumber - FirstLowInvalidHeapAttributeNumber, + attrs_used))) + { + 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 */ if (first && !is_returning) appendStringInfoString(buf, "NULL"); @@ -2259,7 +2298,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 +2318,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 +2436,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 +2551,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; @@ -2719,6 +2758,12 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte, ADD_REL_QUALIFIER(buf, varno); appendStringInfoString(buf, "ctid"); } + else if (varattno == TableOidAttributeNumber) + { + if (qualify_col) + ADD_REL_QUALIFIER(buf, varno); + appendStringInfoString(buf, "tableoid"); + } else if (varattno < 0) { /* @@ -2730,8 +2775,9 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte, Oid fetchval = 0; if (varattno == TableOidAttributeNumber) + { fetchval = rte->relid; - + } if (qualify_col) { appendStringInfoString(buf, "CASE WHEN ("); @@ -2782,7 +2828,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 +3213,23 @@ 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 10e87acabef..8b93f6785fc 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -5275,14 +5275,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, tableoid, $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 @@ -5429,14 +5429,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, tableoid, $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; @@ -6400,27 +6400,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, ft2.tableoid, ($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, r1.tableoid, 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.tableoid, 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.tableoid, 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.tableoid, 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.tableoid, 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 @@ -6498,13 +6498,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, target.tableoid, $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 @@ -6526,20 +6526,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, d.tableoid, ($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, r1.tableoid, 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.tableoid, 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.tableoid, 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 @@ -6559,19 +6559,19 @@ EXPLAIN (verbose, costs off) WITH cte AS ( UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING * ) SELECT * FROM cte ORDER BY c1; -- can't be pushed down - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Sort Output: cte.c1, cte.c2, cte.c3, cte.c4, cte.c5, cte.c6, cte.c7, cte.c8 Sort Key: cte.c1 CTE cte -> Update on public.ft2 Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.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, ft2.ctid, ft2.* + Output: 'bar'::text, ft2.ctid, ft2.tableoid, $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 -> CTE Scan on cte Output: cte.c1, cte.c2, cte.c3, cte.c4, cte.c5, cte.c6, cte.c7, cte.c8 (13 rows) @@ -6602,13 +6602,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, ft2.tableoid, ($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.tableoid, 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) @@ -6640,24 +6640,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, ft2.tableoid, ($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, r1.tableoid, 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, ft2.tableoid, ft4.*, ft5.*, ft4.c1, ft5.c1, ($0) -> Nested Loop - Output: ft2.ctid, ft4.*, ft4.c1 + Output: ft2.ctid, ft2.tableoid, ($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, ft2.tableoid, $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" @@ -7169,19 +7169,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, remt2.tableoid, ($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.tableoid, 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.*; @@ -7342,13 +7342,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, foreign_tbl.tableoid, $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 @@ -7356,13 +7356,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, foreign_tbl.tableoid, $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 @@ -7455,14 +7455,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, parent_tbl_1.tableoid, $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 @@ -7470,14 +7470,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, parent_tbl_1.tableoid, $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 @@ -7526,14 +7526,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, parent_tbl_1.tableoid, $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; @@ -7612,13 +7612,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, tableoid, $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; @@ -7945,13 +7945,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, tableoid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) UPDATE rem1 set f1 = 10; @@ -8093,12 +8093,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, tableoid, $0 Replaces: Scan on rem1 One-Time Filter: false (6 rows) @@ -8199,13 +8199,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, tableoid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) EXPLAIN (verbose, costs off) @@ -8223,13 +8223,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, tableoid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) EXPLAIN (verbose, costs off) @@ -8257,13 +8257,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, tableoid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) DROP TRIGGER trig_row_before_delete ON rem1; @@ -8281,13 +8281,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, tableoid, $0, rem1.* + Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE (5 rows) DROP TRIGGER trig_row_after_delete ON rem1; @@ -8324,28 +8324,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, parent_tbl_1.tableoid, $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, parent_tbl_1.tableoid, $0 + Remote SQL: SELECT ctid, tableoid FROM public.local_tbl FOR UPDATE (6 rows) DELETE FROM parent_tbl; @@ -8363,39 +8363,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, (NULL::oid), $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 + Output: parent_tbl_1.b, parent_tbl_1.tableoid, parent_tbl_1.ctid, NULL::oid, 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.tableoid, 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, (NULL::oid), $0 + -> Append + -> Seq Scan on public.parent_tbl parent_tbl_1 + Output: parent_tbl_1.tableoid, parent_tbl_1.ctid, NULL::oid + -> Foreign Scan on public.foreign_tbl parent_tbl_2 + Output: parent_tbl_2.tableoid, parent_tbl_2.ctid, parent_tbl_2.tableoid, $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 @@ -8737,22 +8739,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, (NULL::oid), $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 + Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::oid, 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.tableoid, 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 @@ -8784,24 +8786,24 @@ 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, (NULL::oid), $0, (NULL::record) Merge Cond: (bar.f1 = foo.f1) -> Sort - Output: bar.f2, bar.f1, bar.tableoid, bar.ctid, (NULL::record) + Output: bar.f2, bar.f1, bar.tableoid, bar.ctid, (NULL::oid), (NULL::record) Sort Key: bar.f1 -> Append -> Seq Scan on public.bar bar_1 - Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record + Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::oid, 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.tableoid, 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 @@ -8942,19 +8944,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, (NULL::oid), $0 + -> Append + -> Index Scan using i_foo_f1 on public.foo foo_1 + Output: foo_1.tableoid, foo_1.ctid, NULL::oid + 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 @@ -8968,17 +8972,17 @@ delete from foo where f1 < 5 returning *; explain (verbose, costs off) update bar set f2 = f2 + 100 returning *; - QUERY PLAN ------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Update on public.bar Output: bar_1.f1, bar_1.f2 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, (NULL::oid), $0, (NULL::record) -> Append -> Seq Scan on public.bar bar_1 - Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record + Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::oid, NULL::record -> Foreign Update on public.bar2 bar_2 Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2 (11 rows) @@ -9003,20 +9007,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, (NULL::oid), $0, (NULL::record) -> Append -> Seq Scan on public.bar bar_1 - Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record + Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::oid, 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.tableoid, bar_2.*, $0 + Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE (12 rows) update bar set f2 = f2 + 100; @@ -9034,20 +9038,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, (NULL::oid), $0, (NULL::record) + -> Append + -> Seq Scan on public.bar bar_1 + Output: bar_1.tableoid, bar_1.ctid, NULL::oid, NULL::record + Filter: (bar_1.f2 < 400) + -> Foreign Scan on public.bar2 bar_2 + Output: bar_2.tableoid, bar_2.ctid, bar_2.tableoid, 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 @@ -9078,22 +9084,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, (NULL::oid), $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 + Output: parent_1.b, parent_1.a, parent_1.tableoid, parent_1.ctid, NULL::oid, 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.tableoid, 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 @@ -9110,22 +9116,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, (NULL::oid), $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 + Output: parent_1.a, parent_1.tableoid, parent_1.ctid, NULL::oid -> 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, parent_2.tableoid, $0 + Remote SQL: SELECT a, ctid, tableoid FROM public.loct1 FOR UPDATE -> Materialize Output: remt2.*, remt2.a -> Foreign Scan on public.remt2 @@ -9355,7 +9361,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, NULL::oid, $0, NULL::record Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2)) (10 rows) @@ -9394,8 +9400,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 @@ -9404,7 +9410,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, NULL::oid, $0, NULL::record (9 rows) update utrtest set a = 1 returning *; @@ -9415,22 +9421,22 @@ 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, utrtest.tableoid, $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.tableoid, 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 + Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, NULL::oid, NULL::record -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" @@ -9454,15 +9460,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, NULL::oid, $0, NULL::record -> Foreign Update on public.remp utrtest_2 Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b (9 rows) @@ -9472,22 +9478,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, (NULL::oid), $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 + Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, NULL::oid, 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.tableoid, utrtest_2.*, $0 + Remote SQL: SELECT a, b, ctid, tableoid FROM public.loct FOR UPDATE -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" @@ -12374,8 +12380,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 @@ -12387,7 +12393,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, NULL::oid, $0, NULL::record, $1 Filter: (async_pt_3.b = 0) (13 rows) @@ -12414,7 +12420,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, NULL::oid, $0, $1 Filter: (async_pt_3.b = 0) (13 rows) @@ -12977,3 +12983,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 0f20f38c83e..45079999ecf 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -80,6 +80,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, @@ -178,6 +180,10 @@ 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? */ + Oid remote_tableoid; } PgFdwScanState; /* @@ -204,6 +210,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 */ @@ -304,6 +311,20 @@ typedef struct int64 offset_est; } PgFdwPathExtraData; +typedef struct OidMappingEntry +{ + Oid key; + Oid remote_oid; +} OidMappingEntry; + +typedef struct PgFdwRemoteMap +{ + HTAB *oid_mapping; + uint32 refcount; +} PgFdwRemoteMap; + +static PgFdwRemoteMap *remoteMap; + /* * Identify the attribute where data conversion fails. */ @@ -656,6 +677,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, @@ -760,6 +782,88 @@ static void merge_fdw_options(PgFdwRelationInfo *fpinfo, const PgFdwRelationInfo *fpinfo_i); static int get_batch_size_option(Relation rel); +/* Private cache */ +static void +init_remote_map(void) +{ + MemoryContext hash_cxt; + HASHCTL info; + + Assert(remoteMap == NULL); + + remoteMap = (PgFdwRemoteMap *) + MemoryContextAllocZero(CacheMemoryContext, sizeof(PgFdwRemoteMap)); + + hash_cxt = AllocSetContextCreate(CacheMemoryContext, + "PgFdwRemoteMap", + ALLOCSET_DEFAULT_SIZES); + info.keysize = sizeof(Oid); + info.entrysize = sizeof(OidMappingEntry); + info.hcxt = hash_cxt; + + remoteMap->oid_mapping = + hash_create("PgFdwRemoteMap hashtable", + 256, /* arbitrary initial size */ + &info, + HASH_ELEM | HASH_BLOBS | HASH_CONTEXT); +} + +static void +insert_oid_pair(Oid local_oid, Oid remote_oid) +{ + bool found; + OidMappingEntry *entry; + + Assert(OidIsValid(local_oid) && OidIsValid(remote_oid)); + + if (remoteMap == NULL) + init_remote_map(); + + /* This mapping may already present in the hashtable. */ + entry = (OidMappingEntry *) + hash_search(remoteMap->oid_mapping, &local_oid, HASH_ENTER, &found); + + if (!found) + { + remoteMap->refcount++; + entry->remote_oid = remote_oid; + } +} + +static Oid +find_remote_oid(Oid local_oid) +{ + OidMappingEntry *entry; + bool found; + + Assert(OidIsValid(local_oid)); + + if (remoteMap == NULL) + return InvalidOid; + + entry = (OidMappingEntry *) + hash_search(remoteMap->oid_mapping, &local_oid, HASH_FIND, &found); + + return (found ? entry->remote_oid : InvalidOid); +} + +static void +destroy_remote_map(void) +{ + if (remoteMap == NULL) + return; + + Assert(remoteMap->refcount > 0); + remoteMap->refcount--; + + if (remoteMap->refcount == 0) + { + hash_destroy(remoteMap->oid_mapping); + + pfree(remoteMap); + remoteMap = NULL; + } +} /* * Foreign-data wrapper handler function: return a struct with pointers @@ -1002,6 +1106,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; + } + } } /* @@ -1463,6 +1584,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. @@ -1627,12 +1749,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, @@ -1765,6 +1891,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)); @@ -1784,11 +1912,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); @@ -1820,6 +1950,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 @@ -1846,12 +1977,23 @@ 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; + + insert_oid_pair(RelationGetRelid(fsstate->rel), tuple->t_tableOid); + } /* * Return the next tuple. */ - ExecStoreHeapTuple(fsstate->tuples[fsstate->next_tuple++], - slot, - false); + ExecStoreHeapTuple(tuple, slot, false); return slot; } @@ -1966,6 +2108,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. @@ -1981,6 +2126,39 @@ 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 Var representing the desired value */ + var = makeVar(rtindex, + TableOidAttributeNumber, + OIDOID, + -1, + InvalidOid, + 0); + /* Register it as a row-identity column needed by this target rel */ + add_row_identity_var(root, var, rtindex, "remote_tableoid"); + + /* 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); } /* @@ -2343,6 +2521,7 @@ postgresExecForeignDelete(EState *estate, rslot = execute_foreign_modify(estate, resultRelInfo, CMD_DELETE, &slot, &planSlot, &numSlots); + destroy_remote_map(); return rslot ? rslot[0] : NULL; } @@ -4228,7 +4407,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 = palloc0_array(FmgrInfo, n_params); fmstate->p_nums = 0; @@ -4246,6 +4425,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) @@ -4298,6 +4491,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; @@ -4343,7 +4537,9 @@ execute_foreign_modify(EState *estate, if (operation == CMD_UPDATE || operation == CMD_DELETE) { Datum datum; + Datum datum2; bool isNull; + Oid remote_tableoid = InvalidOid; datum = ExecGetJunkAttribute(planSlots[0], fmstate->ctidAttno, @@ -4352,10 +4548,28 @@ 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); + + /* Remote OID */ + remote_tableoid = find_remote_oid(tableoid); + + if(OidIsValid(remote_tableoid)) + tableoid = remote_tableoid; } /* 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. @@ -4460,6 +4674,7 @@ prepare_foreign_modify(PgFdwModifyState *fmstate) static const char ** convert_prep_stmt_params(PgFdwModifyState *fmstate, ItemPointer tupleid, + Oid tableoid, TupleTableSlot **slots, int numSlots) { @@ -4486,6 +4701,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) { @@ -4497,7 +4722,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); @@ -4562,9 +4787,10 @@ finish_foreign_modify(PgFdwModifyState *fmstate) { Assert(fmstate != NULL); + destroy_remote_map(); + /* If we created a prepared statement, destroy it */ deallocate_query(fmstate); - /* Release remote connection */ ReleaseConnection(fmstate->conn); fmstate->conn = NULL; @@ -4670,7 +4896,8 @@ build_remote_returning(Index rtindex, Relation rel, List *returningList) if (IsA(var, Var) && var->varno == rtindex && var->varattno <= InvalidAttrNumber && - var->varattno != SelfItemPointerAttributeNumber) + var->varattno != SelfItemPointerAttributeNumber && + var->varattno != TableOidAttributeNumber) continue; /* don't need it */ if (tlist_member((Expr *) var, tlist)) @@ -4876,6 +5103,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)); /* @@ -4894,6 +5132,8 @@ init_returning_filter(PgFdwDirectModifyState *dmstate, */ if (attrno == SelfItemPointerAttributeNumber) dmstate->ctidAttno = i; + else if (attrno == TableOidAttributeNumber) + dmstate->oidAttno = i; else Assert(false); dmstate->hasSystemCols = true; @@ -4985,10 +5225,13 @@ apply_returning_filter(PgFdwDirectModifyState *dmstate, /* ctid */ if (dmstate->ctidAttno) { + Oid tableoid = InvalidOid; ItemPointer ctid = NULL; ctid = (ItemPointer) DatumGetPointer(old_values[dmstate->ctidAttno - 1]); + tableoid = DatumGetObjectId(old_values[dmstate->oidAttno - 1]); resultTup->t_self = *ctid; + resultTup->t_tableOid = tableoid; } /* @@ -6901,6 +7144,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) { @@ -8434,6 +8709,7 @@ make_tuple_from_result_row(PGresult *res, ErrorContextCallback errcallback; MemoryContext oldcontext; ListCell *lc; + Oid tableoid = InvalidOid; int j; Assert(row < PQntuples(res)); @@ -8516,6 +8792,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++; @@ -8547,6 +8834,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 a2bb1ff352c..376c55d4dd0 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 79ad5be8bf9..81d7cf03c1a 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -4620,3 +4620,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; -- 2.43.0