v2-0003-teach-fdw-use-remote-tableoid.patch
application/octet-stream
Filename: v2-0003-teach-fdw-use-remote-tableoid.patch
Type: application/octet-stream
Part: 2
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v2-0003
Subject: DELETE/UPDATE more than one row in partitioned foreign table (3/3)
| File | + | − |
|---|---|---|
| contrib/postgres_fdw/deparse.c | 74 | 11 |
| contrib/postgres_fdw/expected/postgres_fdw.out | 280 | 228 |
| contrib/postgres_fdw/postgres_fdw.c | 311 | 9 |
| contrib/postgres_fdw/postgres_fdw.h | 3 | 0 |
| contrib/postgres_fdw/sql/postgres_fdw.sql | 44 | 0 |
From 47999936a1b9a670307d4528ec6c4b560cec3961 Mon Sep 17 00:00:00 2001
From: Nikita Malakhov <n.malakhov@postgrespro.ru>
Date: Thu, 14 May 2026 17:45:21 +0300
Subject: [PATCH] DELETE/UPDATE more than one row in partitioned foreign table
(3/3)
This patch modifies FDW engine to use remote table OID introduced
in previous patch for DELETE and UPDATE of partitioned tables.
[1] Discussion: https://www.postgresql.org/message-id/flat/20250718175314.4513c00a%40karst
---
contrib/postgres_fdw/deparse.c | 85 ++-
.../postgres_fdw/expected/postgres_fdw.out | 508 ++++++++++--------
contrib/postgres_fdw/postgres_fdw.c | 320 ++++++++++-
contrib/postgres_fdw/postgres_fdw.h | 3 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 44 ++
5 files changed, 712 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 e90289e4ab1..d8641209acb 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)
@@ -12989,6 +12995,52 @@ 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)
+
-- ===================================================================
-- test cleanup of failed connections on abort
-- ===================================================================
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 85be46eb2a2..2b4889ba3e6 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -22,6 +22,7 @@
#include "commands/explain_format.h"
#include "commands/explain_state.h"
#include "commands/vacuum.h"
+#include "common/hashfn.h"
#include "executor/execAsync.h"
#include "executor/instrument.h"
#include "executor/spi.h"
@@ -80,6 +81,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 +181,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 +211,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 +312,35 @@ typedef struct
int64 offset_est;
} PgFdwPathExtraData;
+typedef struct OidMappingEntry
+{
+ Oid local_oid; /* key */
+ Oid remote_oid; /* value */
+ char status; /* hash entry status */
+} OidMappingEntry;
+
+#define SH_PREFIX oidmapping
+#define SH_ELEMENT_TYPE OidMappingEntry
+#define SH_KEY_TYPE Oid
+#define SH_KEY local_oid
+#define SH_HASH_KEY(tb, key) murmurhash32(key)
+#define SH_EQUAL(tb, a, b) a == b
+#define SH_SCOPE static inline
+#define SH_DEFINE
+#define SH_DECLARE
+#include "lib/simplehash.h"
+
+typedef struct PgFdwRemoteMap
+{
+ oidmapping_hash *oid_mapping;
+ MemoryContext hash_cxt;
+ uint32 refcount;
+} PgFdwRemoteMap;
+
+static PgFdwRemoteMap *remoteMap;
+
+#define N_INITIAL_OIDMAPPING_ELEMS 50
+
/*
* Identify the attribute where data conversion fails.
*/
@@ -502,6 +539,85 @@ static const Oid attclear_argtypes[ATTCLEAR_SQL_NUM_FIELDS] =
*/
PG_FUNCTION_INFO_V1(postgres_fdw_handler);
+/* Private cache */
+static void
+init_remote_map(void)
+{
+ MemoryContext remoteMapCxt;
+
+ Assert(remoteMap == NULL);
+
+ remoteMapCxt = AllocSetContextCreate(CacheMemoryContext,
+ "PgFdwRemoteMap",
+ ALLOCSET_SMALL_SIZES);
+
+ remoteMap = (PgFdwRemoteMap *)
+ MemoryContextAllocZero(remoteMapCxt, sizeof(PgFdwRemoteMap));
+
+ remoteMap->hash_cxt = remoteMapCxt;
+ remoteMap->oid_mapping = oidmapping_create(remoteMap->hash_cxt,
+ N_INITIAL_OIDMAPPING_ELEMS,
+ NULL);
+}
+
+static void
+insert_oid_pair(Oid local_oid, Oid remote_oid)
+{
+ bool found;
+ OidMappingEntry *entry;
+ MemoryContext tmp;
+
+ Assert(OidIsValid(local_oid) && OidIsValid(remote_oid));
+
+ if (remoteMap == NULL)
+ init_remote_map();
+
+ tmp = MemoryContextSwitchTo(remoteMap->hash_cxt);
+
+ /* This mapping may already present in the hashtable. */
+ entry = oidmapping_insert(remoteMap->oid_mapping, local_oid, &found);
+
+ if (!found)
+ {
+ remoteMap->refcount++;
+ entry->remote_oid = remote_oid;
+ }
+
+ MemoryContextSwitchTo(tmp);
+}
+
+static Oid
+find_remote_oid(Oid local_oid)
+{
+ OidMappingEntry *entry;
+
+ Assert(OidIsValid(local_oid));
+
+ if (remoteMap == NULL)
+ return InvalidOid;
+
+ entry = oidmapping_lookup(remoteMap->oid_mapping, local_oid);
+
+ return ((entry != NULL) ? entry->remote_oid : InvalidOid);
+}
+
+static void
+destroy_remote_map(void)
+{
+ if (remoteMap == NULL)
+ return;
+
+ Assert(remoteMap->refcount > 0);
+ remoteMap->refcount--;
+
+ if (remoteMap->refcount == 0)
+ {
+ oidmapping_destroy(remoteMap->oid_mapping);
+ MemoryContextDelete(remoteMap->hash_cxt);
+ remoteMap = NULL;
+ }
+}
+
/*
* FDW callback routines
*/
@@ -656,6 +772,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,
@@ -1002,6 +1119,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 +1597,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 +1762,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 +1904,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 +1925,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 +1963,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 +1990,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 +2121,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 +2139,38 @@ 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 +2533,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 +4419,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 +4437,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 +4503,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 +4549,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 +4560,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 +4686,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 +4713,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 +4734,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 +4799,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 +4908,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 +5115,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 +5144,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 +5237,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 +7156,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 +8721,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 +8804,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 +8846,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 dfc58beb0d2..ba2d753b821 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4634,6 +4634,50 @@ SELECT server_name,
\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;
+
-- ===================================================================
-- test cleanup of failed connections on abort
-- ===================================================================
--
2.43.0