public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 4/4] Implement vacuum full/cluster (INDEX_TABLESPACE <tablespace>)
13+ messages / 4 participants
[nested] [flat]
* [PATCH 4/4] Implement vacuum full/cluster (INDEX_TABLESPACE <tablespace>)
@ 2020-04-01 01:35 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Justin Pryzby @ 2020-04-01 01:35 UTC (permalink / raw)
---
doc/src/sgml/ref/cluster.sgml | 12 ++++-
doc/src/sgml/ref/vacuum.sgml | 12 ++++-
src/backend/commands/cluster.c | 56 ++++++++++++++---------
src/backend/commands/matview.c | 3 +-
src/backend/commands/tablecmds.c | 2 +-
src/backend/commands/vacuum.c | 40 ++++++++--------
src/backend/postmaster/autovacuum.c | 1 +
src/include/commands/cluster.h | 4 +-
src/include/commands/vacuum.h | 5 +-
src/test/regress/input/tablespace.source | 13 ++++++
src/test/regress/output/tablespace.source | 20 ++++++++
11 files changed, 117 insertions(+), 51 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index 6758ef97a6..9c0f5add59 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -29,6 +29,7 @@ CLUSTER [VERBOSE]
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
+ INDEX_TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -106,6 +107,15 @@ CLUSTER [VERBOSE]
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INDEX_TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ Specifies that the table's indexes will be rebuilt on a new tablespace.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>TABLESPACE</literal></term>
<listitem>
@@ -128,7 +138,7 @@ CLUSTER [VERBOSE]
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
- The tablespace where the table will be rebuilt.
+ The tablespace where the table or its indexes will be rebuilt.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 5261a7c727..28cab119b6 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -36,6 +36,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
PARALLEL <replaceable class="parameter">integer</replaceable>
TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
+ INDEX_TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -265,6 +266,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INDEX_TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ Specifies that the relation's indexes will be rebuilt on a new tablespace.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
@@ -314,7 +324,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
- The tablespace where the relation will be rebuilt.
+ The tablespace where the relation or its indexes will be rebuilt.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 626321e3ac..7fc6b69e95 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -71,7 +71,7 @@ typedef struct
static void rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose,
- Oid NewTableSpaceOid);
+ Oid NewTableSpaceOid, Oid NewIdxTableSpaceOid);
static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
bool verbose, bool *pSwapToastByContent,
TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
@@ -108,8 +108,9 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
ListCell *lc;
ClusterParams params = {0};
bool verbose = false;
- /* Name of tablespace to use for clustered relation. */
- char *tablespaceName = NULL;
+ /* Names of tablespaces to use for clustered relations. */
+ char *tablespaceName = NULL,
+ *idxtablespaceName = NULL;
/* Parse option list */
foreach(lc, stmt->params)
@@ -120,6 +121,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
verbose = defGetBoolean(opt);
else if (strcmp(opt->defname, "tablespace") == 0)
tablespaceName = defGetString(opt);
+ else if (strcmp(opt->defname, "index_tablespace") == 0)
+ idxtablespaceName = defGetString(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -130,18 +133,11 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
params.options = (verbose ? CLUOPT_VERBOSE : 0);
- /* Select tablespace Oid to use. */
- if (tablespaceName)
- {
- params.tablespaceOid = get_tablespace_oid(tablespaceName, false);
-
- /* Can't move a non-shared relation into pg_global */
- if (params.tablespaceOid == GLOBALTABLESPACE_OID)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move non-shared relation to tablespace \"%s\"",
- tablespaceName)));
- }
+ /* Get tablespaces to use. */
+ params.tablespaceOid = tablespaceName ?
+ get_tablespace_oid(tablespaceName, false) : InvalidOid;
+ params.idxtablespaceOid = idxtablespaceName ?
+ get_tablespace_oid(idxtablespaceName, false) : InvalidOid;
if (stmt->relation != NULL)
{
@@ -293,8 +289,9 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
* instead of index order. This is the new implementation of VACUUM FULL,
* and error messages should refer to the operation as VACUUM not CLUSTER.
*
- * "tablespaceOid" is the tablespace where the relation will be rebuilt, or
- * InvalidOid to use its current tablespace.
+ * "tablespaceOid" and "idxtablespaceOid" are the tablespaces where the relation
+ * and its indexes will be rebuilt, or InvalidOid to use their current
+ * tablespaces.
*/
void
cluster_rel(Oid tableOid, Oid indexOid, ClusterParams *params)
@@ -464,7 +461,8 @@ cluster_rel(Oid tableOid, Oid indexOid, ClusterParams *params)
TransferPredicateLocksToHeapRelation(OldHeap);
/* rebuild_relation does all the dirty work */
- rebuild_relation(OldHeap, indexOid, verbose, params->tablespaceOid);
+ rebuild_relation(OldHeap, indexOid, verbose, params->tablespaceOid,
+ params->idxtablespaceOid);
/* NB: rebuild_relation does table_close() on OldHeap */
@@ -613,10 +611,11 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal)
* NB: this routine closes OldHeap at the right time; caller should not.
*/
static void
-rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose, Oid NewTablespaceOid)
+rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose, Oid NewTablespaceOid, Oid NewIdxTablespaceOid)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid tableSpace = OldHeap->rd_rel->reltablespace;
+ Oid idxtableSpace;
Oid OIDNewHeap;
char relpersistence;
bool is_system_catalog;
@@ -626,7 +625,20 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose, Oid NewTablespace
/* Use new tablespace if passed. */
if (OidIsValid(NewTablespaceOid))
+ {
tableSpace = NewTablespaceOid;
+ /* It's not a shared catalog, so refuse to move it to shared tablespace */
+ if (tableSpace == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ get_tablespace_name(tableSpace))));
+ }
+
+ if (OidIsValid(NewIdxTablespaceOid))
+ idxtableSpace = NewIdxTablespaceOid;
+ else
+ idxtableSpace = get_rel_tablespace(indexOid);
/* Mark the correct index as clustered */
if (OidIsValid(indexOid))
@@ -655,7 +667,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose, Oid NewTablespace
finish_heap_swap(tableOid, OIDNewHeap, is_system_catalog,
swap_toast_by_content, false, true,
frozenXid, cutoffMulti,
- relpersistence);
+ relpersistence, idxtableSpace);
}
@@ -1403,12 +1415,12 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_internal,
TransactionId frozenXid,
MultiXactId cutoffMulti,
- char newrelpersistence)
+ char newrelpersistence, Oid idxtableSpace)
{
ObjectAddress object;
Oid mapped_tables[4];
int reindex_flags;
- ReindexParams reindex_params = {0};
+ ReindexParams reindex_params = { .tablespaceOid = idxtableSpace };
int i;
/* Report that we are now swapping relation files */
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index c5c25ce11d..ad1bfdc0d2 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -855,7 +855,8 @@ static void
refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence)
{
finish_heap_swap(matviewOid, OIDNewHeap, false, false, true, true,
- RecentXmin, ReadNextMultiXactId(), relpersistence);
+ RecentXmin, ReadNextMultiXactId(), relpersistence,
+ InvalidOid);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 75b04eb161..eba8478f91 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5077,7 +5077,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
!OidIsValid(tab->newTableSpace),
RecentXmin,
ReadNextMultiXactId(),
- persistence);
+ persistence, InvalidOid);
}
else
{
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 61565c3f14..0eb9786bea 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -109,9 +109,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool disable_page_skipping = false;
ListCell *lc;
- /* Name and Oid of tablespace to use for relations after VACUUM FULL. */
- char *tablespacename = NULL;
- Oid tablespaceOid = InvalidOid;
+ /* Tablespace to use for relations after VACUUM FULL. */
+ char *tablespacename = NULL,
+ *idxtablespacename = NULL;
/* Set default value */
params.index_cleanup = VACOPT_TERNARY_DEFAULT;
@@ -151,6 +151,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
params.truncate = get_vacopt_ternary_value(opt);
else if (strcmp(opt->defname, "tablespace") == 0)
tablespacename = defGetString(opt);
+ else if (strcmp(opt->defname, "index_tablespace") == 0)
+ idxtablespacename = defGetString(opt);
else if (strcmp(opt->defname, "parallel") == 0)
{
if (opt->arg == NULL)
@@ -211,26 +213,18 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("VACUUM FULL cannot be performed in parallel")));
- /* Get tablespace Oid to use. */
- if (tablespacename)
- {
- if ((params.options & VACOPT_FULL) == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("incompatible TABLESPACE option"),
- errdetail("TABLESPACE can only be used with VACUUM FULL.")));
-
- tablespaceOid = get_tablespace_oid(tablespacename, false);
-
- /* Can't move a non-shared relation into pg_global */
- if (tablespaceOid == GLOBALTABLESPACE_OID)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move non-shared relation to tablespace \"%s\"",
- tablespacename)));
+ if ((params.options & VACOPT_FULL) == 0 &&
+ (tablespacename || idxtablespacename))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("incompatible TABLESPACE option"),
+ errdetail("TABLESPACE can only be used with VACUUM FULL.")));
- }
- params.tablespace_oid = tablespaceOid;
+ /* Get tablespace Oids to use. */
+ params.tablespace_oid = tablespacename ?
+ get_tablespace_oid(tablespacename, false) : InvalidOid;
+ params.idxtablespace_oid = idxtablespacename ?
+ get_tablespace_oid(idxtablespacename, false) : InvalidOid;
/*
* Make sure VACOPT_ANALYZE is specified if any column lists are present.
@@ -1964,6 +1958,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
{
ClusterParams cluster_params = {
.tablespaceOid = params->tablespace_oid,
+ .idxtablespaceOid = params->idxtablespace_oid,
+ /* Other params initialized to 0/false/NULL */
};
/* close relation before vacuuming, but hold lock until commit */
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index a5d98c8c95..fd9eb75e11 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2933,6 +2933,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
tab->at_params.is_wraparound = wraparound;
tab->at_params.log_min_duration = log_min_duration;
tab->at_params.tablespace_oid = InvalidOid;
+ tab->at_params.idxtablespace_oid = InvalidOid;
tab->at_vacuum_cost_limit = vac_cost_limit;
tab->at_vacuum_cost_delay = vac_cost_delay;
tab->at_relname = NULL;
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index 868dea5ecc..c04a2ce20b 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -28,6 +28,7 @@ typedef struct ClusterParams
{
bits32 options; /* bitmask of CLUOPT_* */
Oid tablespaceOid; /* tablespace to rebuild relation, or InvalidOid */
+ Oid idxtablespaceOid; /* tablespace to rebuild relation's indexes */
} ClusterParams;
extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel);
@@ -45,6 +46,7 @@ extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_internal,
TransactionId frozenXid,
MultiXactId minMulti,
- char newrelpersistence);
+ char newrelpersistence,
+ Oid idxtablespaceOid);
#endif /* CLUSTER_H */
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 0934487d4b..6c4e085a45 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -227,8 +227,9 @@ typedef struct VacuumParams
* disabled.
*/
int nworkers;
- Oid tablespace_oid; /* tablespace to use for relations
- * rebuilt by VACUUM FULL */
+ /* tablespaces to use for relations rebuilt by VACUUM FULL */
+ Oid tablespace_oid;
+ Oid idxtablespace_oid;
} VacuumParams;
/* GUC parameters */
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 2244c1d51d..9a13112605 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -80,6 +80,19 @@ REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; --
SELECT relname FROM pg_class
WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+-- check CLUSTER with INDEX_TABLESPACE change to non-default location
+CLUSTER (INDEX_TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok
+-- check relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+-- check VACUUM with INDEX_TABLESPACE change
+VACUUM (FULL, ANALYSE, FREEZE, INDEX_TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok
+-- check relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+
-- create a schema we can use
CREATE SCHEMA testschema;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 889933a9c8..2f8ecf514d 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -114,6 +114,26 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
---------
(0 rows)
+-- check CLUSTER with INDEX_TABLESPACE change to non-default location
+CLUSTER (INDEX_TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok
+-- check relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+ relname
+-------------------------------
+ regress_tblspace_test_tbl_idx
+(1 row)
+
+-- check VACUUM with INDEX_TABLESPACE change
+VACUUM (FULL, ANALYSE, FREEZE, INDEX_TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok
+-- check relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname
+---------
+(0 rows)
+
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
--
2.17.0
--tsOsTdHNUZQcU9Ye--
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
@ 2026-06-01 10:44 Etsuro Fujita <[email protected]>
2026-06-05 11:59 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 11:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Etsuro Fujita @ 2026-06-01 10:44 UTC (permalink / raw)
To: Nikita Malakhov <[email protected]>; +Cc: Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
Hi Nikita,
On Fri, May 15, 2026 at 2:23 AM Nikita Malakhov <[email protected]> wrote:
> CFbot was unhappy with previous patch set, so here's updated one
Thanks for working on this issue!
I took a quick look at the patch set. IIUC I think it's created based
on what I proposed in the original thread, which is invasive and thus
not back-patchable, so what you are proposing here isn't
back-patchable, either, I think.
I think we should first work on a back-patchable solution. So I'd
like to re-propose the patch that I proposed in this thread before to
disallow UPDATE/DELETE in problematic cases [1]. Attached is a new
version of the patch. Changes are:
* Renamed the new table option inherited to remotely_inherited, to
avoid confusion with local inheritance.
* Moved the logic to prevent problematic UPDATE/DELETE from a planner
function to an executor function, to avoid throwing an error
unnecessarily when there are no target rows to update/delete.
* Added docs to postgres-fdw.sgml.
I'm planning to add the postgresImportForeignSchema() support in the
next version.
I think the remotely_inherited option would be useful when adding the
support for the UPDATE/DELETE, as it could be used to address one of
Tom Lane's comments about what I proposed in the original thread that
it adds the tabloid condition to a remote UPDATE/DELETE query whether
the target table is inherited or not: that could be avoid if the
option is set to false.
What do you think about that?
Best regards,
Etsuro Fujita
[1] https://www.postgresql.org/message-id/CAPmGK15CQK-oYFMAyq%2BrR0rQapUHtvAGuGgY5ahERHzZ4tmC8g%40mail.g...
Attachments:
[application/octet-stream] postgres_fdw-disallow-upddel-in-problematic-cases-v2.patch (11.0K, ../../CAPmGK166P+ngd2ehady=_f-L4MePgBdBNxN5gi5_gSAfmV82QA@mail.gmail.com/2-postgres_fdw-disallow-upddel-in-problematic-cases-v2.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index aaffcf31271..ef1846c20cc 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7197,6 +7197,70 @@ RESET enable_material;
DROP FOREIGN TABLE remt2;
DROP TABLE loct1;
DROP TABLE loct2;
+-- Test UPDATE/DELETE on remotely-inherited foreign tables
+CREATE TABLE ritest_pt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE ritest_pt_p1 PARTITION OF ritest_pt FOR VALUES IN (1);
+CREATE TABLE ritest_pt_p2 PARTITION OF ritest_pt FOR VALUES IN (2);
+CREATE FOREIGN TABLE ritest_ft (a int, b int) SERVER loopback OPTIONS (table_name 'ritest_pt', remotely_inherited 'true');
+INSERT INTO ritest_ft VALUES (1, 10), (2, 20);
+-- Use random() so that UPDATE/DELETE is not pushed down to the remote
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0;
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Update on public.ritest_ft
+ Remote SQL: UPDATE public.ritest_pt SET b = $2 WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: 100, ctid, ritest_ft.*
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT a, b, ctid FROM public.ritest_pt WHERE ((a = 1)) FOR UPDATE
+(6 rows)
+
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0; -- should fail
+ERROR: cannot update remotely-inherited foreign table "ritest_ft"
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Update on public.ritest_ft
+ Remote SQL: UPDATE public.ritest_pt SET b = $2 WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: 300, ctid, ritest_ft.*
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT a, b, ctid FROM public.ritest_pt WHERE ((b = 30)) FOR UPDATE
+(6 rows)
+
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0; -- should work
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Delete on public.ritest_ft
+ Remote SQL: DELETE FROM public.ritest_pt WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: ctid
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT ctid FROM public.ritest_pt WHERE ((a = 1)) FOR UPDATE
+(6 rows)
+
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0; -- should fail
+ERROR: cannot delete from remotely-inherited foreign table "ritest_ft"
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Delete on public.ritest_ft
+ Remote SQL: DELETE FROM public.ritest_pt WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: ctid
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT ctid FROM public.ritest_pt WHERE ((b = 30)) FOR UPDATE
+(6 rows)
+
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0; -- should work
+-- Cleanup
+DROP FOREIGN TABLE ritest_ft;
+DROP TABLE ritest_pt;
-- ===================================================================
-- test check constraints
-- ===================================================================
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 3944aedbacc..2341d9e5b69 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -116,6 +116,7 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
*/
if (strcmp(def->defname, "use_remote_estimate") == 0 ||
strcmp(def->defname, "updatable") == 0 ||
+ strcmp(def->defname, "remotely_inherited") == 0 ||
strcmp(def->defname, "truncatable") == 0 ||
strcmp(def->defname, "async_capable") == 0 ||
strcmp(def->defname, "parallel_commit") == 0 ||
@@ -255,6 +256,7 @@ InitPgFdwOptions(void)
/* updatable is available on both server and table */
{"updatable", ForeignServerRelationId, false},
{"updatable", ForeignTableRelationId, false},
+ {"remotely_inherited", ForeignTableRelationId, false},
/* truncatable is available on both server and table */
{"truncatable", ForeignServerRelationId, false},
{"truncatable", ForeignTableRelationId, false},
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index c42cb690c7b..52b3da4efbf 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -207,6 +207,9 @@ typedef struct PgFdwModifyState
int p_nums; /* number of parameters to transmit */
FmgrInfo *p_flinfo; /* output conversion functions for them */
+ /* update/delete operation stuff */
+ bool resultRelValid; /* have we checked the result relation? */
+
/* batch operation stuff */
int num_slots; /* number of slots to insert */
@@ -654,6 +657,7 @@ static TupleTableSlot **execute_foreign_modify(EState *estate,
TupleTableSlot **planSlots,
int *numSlots);
static void prepare_foreign_modify(PgFdwModifyState *fmstate);
+static void check_result_rel(PgFdwModifyState *fmstate, CmdType operation);
static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
ItemPointer tupleid,
TupleTableSlot **slots,
@@ -4236,6 +4240,9 @@ create_foreign_modify(EState *estate,
{
Assert(subplan != NULL);
+ /* Initialize valid flag for the result relation */
+ fmstate->resultRelValid = false;
+
/* Find the ctid resjunk column in the subplan's result */
fmstate->ctidAttno = ExecFindJunkAttributeInTlist(subplan->targetlist,
"ctid");
@@ -4308,6 +4315,11 @@ execute_foreign_modify(EState *estate,
operation == CMD_UPDATE ||
operation == CMD_DELETE);
+ /* For UPDATE/DELETE, check the result relation if not yet done. */
+ if ((operation == CMD_UPDATE || operation == CMD_DELETE) &&
+ !fmstate->resultRelValid)
+ check_result_rel(fmstate, operation);
+
/* First, process a pending asynchronous request, if any. */
if (fmstate->conn_state->pendingAreq)
process_pending_request(fmstate->conn_state->pendingAreq);
@@ -4448,6 +4460,52 @@ prepare_foreign_modify(PgFdwModifyState *fmstate)
fmstate->p_name = p_name;
}
+/*
+ * check_result_rel
+ * Check if the target foreign table is safe to update/delete via
+ * ExecForeignUpdate/ExecForeignDelete.
+ */
+static void
+check_result_rel(PgFdwModifyState *fmstate, CmdType operation)
+{
+ bool remotely_inherited;
+ ForeignTable *table;
+ ListCell *lc;
+
+ Assert(!fmstate->resultRelValid);
+ Assert(operation == CMD_UPDATE || operation == CMD_DELETE);
+
+ /*
+ * By default, any postgres_fdw foreign table isn't assumed
+ * remotely-inherited.
+ */
+ remotely_inherited = false;
+
+ table = GetForeignTable(RelationGetRelid(fmstate->rel));
+
+ foreach(lc, table->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "remotely_inherited") == 0)
+ remotely_inherited = defGetBoolean(def);
+ }
+
+ /*
+ * It's unsafe to update/delete remotely-inherited foreign tables via
+ * ExecForeignUpdate/ExecForeignDelete.
+ */
+ if (remotely_inherited)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg((operation == CMD_UPDATE) ?
+ "cannot update remotely-inherited foreign table \"%s\"" :
+ "cannot delete from remotely-inherited foreign table \"%s\"",
+ RelationGetRelationName(fmstate->rel))));
+
+ fmstate->resultRelValid = true;
+}
+
/*
* convert_prep_stmt_params
* Create array of text strings representing parameter values
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 267d3c1a7e7..7fc099bccb7 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1778,6 +1778,34 @@ DROP FOREIGN TABLE remt2;
DROP TABLE loct1;
DROP TABLE loct2;
+-- Test UPDATE/DELETE on remotely-inherited foreign tables
+CREATE TABLE ritest_pt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE ritest_pt_p1 PARTITION OF ritest_pt FOR VALUES IN (1);
+CREATE TABLE ritest_pt_p2 PARTITION OF ritest_pt FOR VALUES IN (2);
+CREATE FOREIGN TABLE ritest_ft (a int, b int) SERVER loopback OPTIONS (table_name 'ritest_pt', remotely_inherited 'true');
+INSERT INTO ritest_ft VALUES (1, 10), (2, 20);
+
+-- Use random() so that UPDATE/DELETE is not pushed down to the remote
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0;
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0; -- should fail
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0;
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0; -- should work
+
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0;
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0; -- should fail
+
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0;
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0; -- should work
+
+-- Cleanup
+DROP FOREIGN TABLE ritest_ft;
+DROP TABLE ritest_pt;
+
-- ===================================================================
-- test check constraints
-- ===================================================================
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b81f33732fb..f28da502923 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -630,6 +630,29 @@ OPTIONS (ADD password_required 'false');
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>remotely_inherited</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This option, which can be specified for a foreign table, determines if
+ the remote table is an inherited/partitioned table on the remote server
+ or a foreign table on it referencing such a table on another remote
+ server.
+ The default is <literal>false</literal>.
+ </para>
+
+ <para>
+ If the <literal>updatable</literal> option is set for a foreign table
+ whose remote table is any of the above,
+ <filename>postgres_fdw</filename> currently cannot properly
+ update/delete it, causing unexpected results, except in cases where the
+ whole <command>UPDATE/DELETE</command> processing is pushed down to the
+ remote server. Such unsafe modifications can be prevented by setting
+ this option. This might be imporved in future releases.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect3>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
@ 2026-06-05 11:59 ` Etsuro Fujita <[email protected]>
2026-06-06 20:33 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
2026-06-10 05:37 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
1 sibling, 2 replies; 13+ messages in thread
From: Etsuro Fujita @ 2026-06-05 11:59 UTC (permalink / raw)
To: Nikita Malakhov <[email protected]>; +Cc: Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <[email protected]> wrote:
> I think we should first work on a back-patchable solution. So I'd
> like to re-propose the patch that I proposed in this thread before to
> disallow UPDATE/DELETE in problematic cases [1]. Attached is a new
> version of the patch. Changes are:
>
> * Renamed the new table option inherited to remotely_inherited, to
> avoid confusion with local inheritance.
> * Moved the logic to prevent problematic UPDATE/DELETE from a planner
> function to an executor function, to avoid throwing an error
> unnecessarily when there are no target rows to update/delete.
> * Added docs to postgres-fdw.sgml.
>
> I'm planning to add the postgresImportForeignSchema() support in the
> next version.
I created the patch to add that support on top of the patch I sent in
a previous email, which I'm attaching along with the base patch. It's
the same as before, except that I fixed a typo in docs pointed out by
Michael-san off-list.
Comments welcome!
Best regards,
Etsuro Fujita
Attachments:
[application/octet-stream] v3-0001-postgres_fdw-Disallow-UPDATE-DELETE-in-problematic-c.patch (11.6K, ../../CAPmGK14KEFMTuQ1vYwWCo8SLks5rXv-56K-V+XMy4q8uQJvq1w@mail.gmail.com/2-v3-0001-postgres_fdw-Disallow-UPDATE-DELETE-in-problematic-c.patch)
download | inline diff:
From 8a604cfded373265b3c5da0a1d9cda0207997011 Mon Sep 17 00:00:00 2001
From: Etsuro Fujita <[email protected]>
Date: Fri, 5 Jun 2026 20:20:42 +0900
Subject: [PATCH 1/2] postgres_fdw: Disallow UPDATE/DELETE in problematic
cases.
---
.../postgres_fdw/expected/postgres_fdw.out | 64 +++++++++++++++++++
contrib/postgres_fdw/option.c | 2 +
contrib/postgres_fdw/postgres_fdw.c | 58 +++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 28 ++++++++
doc/src/sgml/postgres-fdw.sgml | 23 +++++++
5 files changed, 175 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e90289e4ab1..ff9c9e878e4 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7197,6 +7197,70 @@ RESET enable_material;
DROP FOREIGN TABLE remt2;
DROP TABLE loct1;
DROP TABLE loct2;
+-- Test UPDATE/DELETE on remotely-inherited foreign tables
+CREATE TABLE ritest_pt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE ritest_pt_p1 PARTITION OF ritest_pt FOR VALUES IN (1);
+CREATE TABLE ritest_pt_p2 PARTITION OF ritest_pt FOR VALUES IN (2);
+CREATE FOREIGN TABLE ritest_ft (a int, b int) SERVER loopback OPTIONS (table_name 'ritest_pt', remotely_inherited 'true');
+INSERT INTO ritest_ft VALUES (1, 10), (2, 20);
+-- Use random() so that UPDATE/DELETE is not pushed down to the remote
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0;
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Update on public.ritest_ft
+ Remote SQL: UPDATE public.ritest_pt SET b = $2 WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: 100, ctid, ritest_ft.*
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT a, b, ctid FROM public.ritest_pt WHERE ((a = 1)) FOR UPDATE
+(6 rows)
+
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0; -- should fail
+ERROR: cannot update remotely-inherited foreign table "ritest_ft"
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Update on public.ritest_ft
+ Remote SQL: UPDATE public.ritest_pt SET b = $2 WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: 300, ctid, ritest_ft.*
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT a, b, ctid FROM public.ritest_pt WHERE ((b = 30)) FOR UPDATE
+(6 rows)
+
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0; -- should work
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Delete on public.ritest_ft
+ Remote SQL: DELETE FROM public.ritest_pt WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: ctid
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT ctid FROM public.ritest_pt WHERE ((a = 1)) FOR UPDATE
+(6 rows)
+
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0; -- should fail
+ERROR: cannot delete from remotely-inherited foreign table "ritest_ft"
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Delete on public.ritest_ft
+ Remote SQL: DELETE FROM public.ritest_pt WHERE ctid = $1
+ -> Foreign Scan on public.ritest_ft
+ Output: ctid
+ Filter: (random() < '1'::double precision)
+ Remote SQL: SELECT ctid FROM public.ritest_pt WHERE ((b = 30)) FOR UPDATE
+(6 rows)
+
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0; -- should work
+-- Cleanup
+DROP FOREIGN TABLE ritest_ft;
+DROP TABLE ritest_pt;
-- ===================================================================
-- test check constraints
-- ===================================================================
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 79b16c3f318..340698e3bca 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -116,6 +116,7 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
*/
if (strcmp(def->defname, "use_remote_estimate") == 0 ||
strcmp(def->defname, "updatable") == 0 ||
+ strcmp(def->defname, "remotely_inherited") == 0 ||
strcmp(def->defname, "truncatable") == 0 ||
strcmp(def->defname, "async_capable") == 0 ||
strcmp(def->defname, "parallel_commit") == 0 ||
@@ -256,6 +257,7 @@ InitPgFdwOptions(void)
/* updatable is available on both server and table */
{"updatable", ForeignServerRelationId, false},
{"updatable", ForeignTableRelationId, false},
+ {"remotely_inherited", ForeignTableRelationId, false},
/* truncatable is available on both server and table */
{"truncatable", ForeignServerRelationId, false},
{"truncatable", ForeignTableRelationId, false},
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0a589f8db74..3466a8e70b5 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -207,6 +207,9 @@ typedef struct PgFdwModifyState
int p_nums; /* number of parameters to transmit */
FmgrInfo *p_flinfo; /* output conversion functions for them */
+ /* update/delete operation stuff */
+ bool resultRelValid; /* have we checked the result relation? */
+
/* batch operation stuff */
int num_slots; /* number of slots to insert */
@@ -654,6 +657,7 @@ static TupleTableSlot **execute_foreign_modify(EState *estate,
TupleTableSlot **planSlots,
int *numSlots);
static void prepare_foreign_modify(PgFdwModifyState *fmstate);
+static void check_result_rel(PgFdwModifyState *fmstate, CmdType operation);
static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
ItemPointer tupleid,
TupleTableSlot **slots,
@@ -4237,6 +4241,9 @@ create_foreign_modify(EState *estate,
{
Assert(subplan != NULL);
+ /* Initialize valid flag for the result relation */
+ fmstate->resultRelValid = false;
+
/* Find the ctid resjunk column in the subplan's result */
fmstate->ctidAttno = ExecFindJunkAttributeInTlist(subplan->targetlist,
"ctid");
@@ -4309,6 +4316,11 @@ execute_foreign_modify(EState *estate,
operation == CMD_UPDATE ||
operation == CMD_DELETE);
+ /* For UPDATE/DELETE, check the result relation if not yet done. */
+ if ((operation == CMD_UPDATE || operation == CMD_DELETE) &&
+ !fmstate->resultRelValid)
+ check_result_rel(fmstate, operation);
+
/* First, process a pending asynchronous request, if any. */
if (fmstate->conn_state->pendingAreq)
process_pending_request(fmstate->conn_state->pendingAreq);
@@ -4449,6 +4461,52 @@ prepare_foreign_modify(PgFdwModifyState *fmstate)
fmstate->p_name = p_name;
}
+/*
+ * check_result_rel
+ * Check if the target foreign table is safe to update/delete via
+ * ExecForeignUpdate/ExecForeignDelete.
+ */
+static void
+check_result_rel(PgFdwModifyState *fmstate, CmdType operation)
+{
+ bool remotely_inherited;
+ ForeignTable *table;
+ ListCell *lc;
+
+ Assert(!fmstate->resultRelValid);
+ Assert(operation == CMD_UPDATE || operation == CMD_DELETE);
+
+ /*
+ * By default, any postgres_fdw foreign table isn't assumed
+ * remotely-inherited.
+ */
+ remotely_inherited = false;
+
+ table = GetForeignTable(RelationGetRelid(fmstate->rel));
+
+ foreach(lc, table->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "remotely_inherited") == 0)
+ remotely_inherited = defGetBoolean(def);
+ }
+
+ /*
+ * It's unsafe to update/delete remotely-inherited foreign tables via
+ * ExecForeignUpdate/ExecForeignDelete.
+ */
+ if (remotely_inherited)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg((operation == CMD_UPDATE) ?
+ "cannot update remotely-inherited foreign table \"%s\"" :
+ "cannot delete from remotely-inherited foreign table \"%s\"",
+ RelationGetRelationName(fmstate->rel))));
+
+ fmstate->resultRelValid = true;
+}
+
/*
* convert_prep_stmt_params
* Create array of text strings representing parameter values
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index dfc58beb0d2..31d5ea8a47d 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1778,6 +1778,34 @@ DROP FOREIGN TABLE remt2;
DROP TABLE loct1;
DROP TABLE loct2;
+-- Test UPDATE/DELETE on remotely-inherited foreign tables
+CREATE TABLE ritest_pt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE ritest_pt_p1 PARTITION OF ritest_pt FOR VALUES IN (1);
+CREATE TABLE ritest_pt_p2 PARTITION OF ritest_pt FOR VALUES IN (2);
+CREATE FOREIGN TABLE ritest_ft (a int, b int) SERVER loopback OPTIONS (table_name 'ritest_pt', remotely_inherited 'true');
+INSERT INTO ritest_ft VALUES (1, 10), (2, 20);
+
+-- Use random() so that UPDATE/DELETE is not pushed down to the remote
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0;
+UPDATE ritest_ft SET b = 100 WHERE a = 1 AND random() < 1.0; -- should fail
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0;
+UPDATE ritest_ft SET b = 300 WHERE b = 30 AND random() < 1.0; -- should work
+
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0;
+DELETE FROM ritest_ft WHERE a = 1 AND random() < 1.0; -- should fail
+
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0;
+DELETE FROM ritest_ft WHERE b = 30 AND random() < 1.0; -- should work
+
+-- Cleanup
+DROP FOREIGN TABLE ritest_ft;
+DROP TABLE ritest_pt;
+
-- ===================================================================
-- test check constraints
-- ===================================================================
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b9e1b04463e..e6fa87c4b87 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -630,6 +630,29 @@ OPTIONS (ADD password_required 'false');
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>remotely_inherited</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This option, which can be specified for a foreign table, determines if
+ the remote table is an inherited/partitioned table on the remote server
+ or a foreign table on it referencing such a table on another remote
+ server.
+ The default is <literal>false</literal>.
+ </para>
+
+ <para>
+ If the <literal>updatable</literal> option is set for a foreign table
+ whose remote table is any of the above,
+ <filename>postgres_fdw</filename> currently cannot properly
+ update/delete it, causing unexpected results, except in cases where the
+ whole <command>UPDATE/DELETE</command> processing is pushed down to the
+ remote server. Such unsafe modifications can be prevented by setting
+ this option. This might be improved in future releases.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect3>
--
2.50.1 (Apple Git-155)
[application/octet-stream] v3-0002-postgres_fdw-Add-IMPORT-FOREIGN-SCHEMA-support-for-n.patch (22.1K, ../../CAPmGK14KEFMTuQ1vYwWCo8SLks5rXv-56K-V+XMy4q8uQJvq1w@mail.gmail.com/3-v3-0002-postgres_fdw-Add-IMPORT-FOREIGN-SCHEMA-support-for-n.patch)
download | inline diff:
From d774c5107fd68ff9d6edb4f9f23b79b9cbf71dc8 Mon Sep 17 00:00:00 2001
From: Etsuro Fujita <[email protected]>
Date: Fri, 5 Jun 2026 20:35:19 +0900
Subject: [PATCH 2/2] postgres_fdw: Add IMPORT FOREIGN SCHEMA support for new
option.
---
.../postgres_fdw/expected/postgres_fdw.out | 129 +++++++++-----
contrib/postgres_fdw/postgres_fdw.c | 162 ++++++++++++++----
contrib/postgres_fdw/sql/postgres_fdw.sql | 17 +-
3 files changed, 234 insertions(+), 74 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ff9c9e878e4..a631ea76dc0 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10092,16 +10092,16 @@ CREATE TABLE import_source.t4_part2 PARTITION OF import_source.t4
CREATE SCHEMA import_dest1;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
\det+ import_dest1.*
- List of foreign tables
- Schema | Table | Server | FDW options | Description
---------------+-------+----------+-------------------------------------------------+-------------
- import_dest1 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
- import_dest1 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
- import_dest1 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
- import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
- import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
- import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
- import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+ List of foreign tables
+ Schema | Table | Server | FDW options | Description
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest1 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ import_dest1 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
+ import_dest1 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') |
+ import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
+ import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
+ import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
(7 rows)
\d import_dest1.*
@@ -10135,7 +10135,7 @@ FDW options: (schema_name 'import_source', table_name 't3')
--------+---------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
Server: loopback
-FDW options: (schema_name 'import_source', table_name 't4')
+FDW options: (schema_name 'import_source', table_name 't4', remotely_inherited 'true')
Foreign table "import_dest1.x 4"
Column | Type | Collation | Nullable | Default | FDW options
@@ -10165,16 +10165,16 @@ CREATE SCHEMA import_dest2;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
OPTIONS (import_default 'true');
\det+ import_dest2.*
- List of foreign tables
- Schema | Table | Server | FDW options | Description
---------------+-------+----------+-------------------------------------------------+-------------
- import_dest2 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
- import_dest2 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
- import_dest2 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
- import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
- import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
- import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
- import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+ List of foreign tables
+ Schema | Table | Server | FDW options | Description
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest2 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ import_dest2 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
+ import_dest2 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') |
+ import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
+ import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
+ import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
(7 rows)
\d import_dest2.*
@@ -10208,7 +10208,7 @@ FDW options: (schema_name 'import_source', table_name 't3')
--------+---------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
Server: loopback
-FDW options: (schema_name 'import_source', table_name 't4')
+FDW options: (schema_name 'import_source', table_name 't4', remotely_inherited 'true')
Foreign table "import_dest2.x 4"
Column | Type | Collation | Nullable | Default | FDW options
@@ -10237,16 +10237,16 @@ CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
- List of foreign tables
- Schema | Table | Server | FDW options | Description
---------------+-------+----------+-------------------------------------------------+-------------
- import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
- import_dest3 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
- import_dest3 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
- import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
- import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
- import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
- import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+ List of foreign tables
+ Schema | Table | Server | FDW options | Description
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ import_dest3 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
+ import_dest3 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') |
+ import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
+ import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
+ import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
(7 rows)
\d import_dest3.*
@@ -10280,7 +10280,7 @@ FDW options: (schema_name 'import_source', table_name 't3')
--------+---------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
Server: loopback
-FDW options: (schema_name 'import_source', table_name 't4')
+FDW options: (schema_name 'import_source', table_name 't4', remotely_inherited 'true')
Foreign table "import_dest3.x 4"
Column | Type | Collation | Nullable | Default | FDW options
@@ -10320,16 +10320,16 @@ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
FROM SERVER loopback INTO import_dest4;
\det+ import_dest4.*
- List of foreign tables
- Schema | Table | Server | FDW options | Description
---------------+---------+----------+-----------------------------------------------------+-------------
- import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
- import_dest4 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
- import_dest4 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
- import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
- import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
- import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
- import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+ List of foreign tables
+ Schema | Table | Server | FDW options | Description
+--------------+---------+----------+---------------------------------------------------------------------------+-------------
+ import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ import_dest4 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
+ import_dest4 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') |
+ import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
+ import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
+ import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
(7 rows)
-- Assorted error cases
@@ -10363,6 +10363,49 @@ QUERY: CREATE FOREIGN TABLE t5 (
OPTIONS (schema_name 'import_source', table_name 't5');
CONTEXT: importing foreign table "t5"
ROLLBACK;
+-- Check that the remotely_inherited option is set when needed.
+CREATE TABLE import_source.inhchild (c1 int);
+CREATE TABLE import_source.t6 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t6;
+CREATE TABLE import_source.t7 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t7;
+ALTER TABLE import_source.inhchild NO INHERIT import_source.t7;
+CREATE FOREIGN TABLE import_source.t8 (c1 int) SERVER loopback
+ OPTIONS (remotely_inherited 'true');
+CREATE SCHEMA import_dest6;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t6, t7, t8)
+ FROM SERVER loopback INTO import_dest6;
+\det+ import_dest6.*
+ List of foreign tables
+ Schema | Table | Server | FDW options | Description
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest6 | t6 | loopback | (schema_name 'import_source', table_name 't6', remotely_inherited 'true') |
+ import_dest6 | t7 | loopback | (schema_name 'import_source', table_name 't7') |
+ import_dest6 | t8 | loopback | (schema_name 'import_source', table_name 't8', remotely_inherited 'true') |
+(3 rows)
+
+\d import_dest6.*
+ Foreign table "import_dest6.t6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 't6', remotely_inherited 'true')
+
+ Foreign table "import_dest6.t7"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 't7')
+
+ Foreign table "import_dest6.t8"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 't8', remotely_inherited 'true')
+
BEGIN;
CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' );
SELECT count(*)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 3466a8e70b5..8a5471b2b05 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -726,6 +726,9 @@ static bool import_fetched_statistics(const char *schemaname,
static void map_field_to_arg(PGresult *res, int row, int field,
int arg, Datum *values, char *nulls);
static bool import_spi_query_ok(void);
+static void append_import_schema_restrictions(StringInfo buf,
+ ImportForeignSchemaStmt *stmt,
+ PGconn *conn);
static void produce_tuple_asynchronously(AsyncRequest *areq, bool fetch);
static void fetch_more_data_begin(AsyncRequest *areq);
static void complete_pending_request(AsyncRequest *areq);
@@ -6389,7 +6392,10 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
PGconn *conn;
StringInfoData buf;
PGresult *res;
- int numrows,
+ char **inherited = NULL;
+ int numinherited,
+ inherited_idx,
+ numrows,
i;
ListCell *lc;
@@ -6444,6 +6450,60 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
PQclear(res);
resetStringInfo(&buf);
+ /*
+ * First, fetch/save all remotely-inherited table names from this schema,
+ * possibly restricted by EXCEPT or LIMIT TO.
+ */
+ appendStringInfoString(&buf,
+ "SELECT relname "
+ "FROM pg_class c "
+ " JOIN pg_namespace n ON "
+ " relnamespace = n.oid "
+ " LEFT JOIN (pg_foreign_table t "
+ " JOIN pg_foreign_server s ON "
+ " s.oid = t.ftserver "
+ " JOIN pg_foreign_data_wrapper w ON "
+ " w.oid = s.srvfdw) ON "
+ " t.ftrelid = c.oid "
+ "WHERE (c.relkind = "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) " "
+ " OR (c.relkind IN ("
+ CppAsString2(RELKIND_RELATION) ","
+ CppAsString2(RELKIND_FOREIGN_TABLE) ") "
+ " AND c.relhassubclass "
+ " AND EXISTS (SELECT 1 FROM pg_inherits "
+ " WHERE inhparent = c.oid)) "
+ " OR (c.relkind = "
+ CppAsString2(RELKIND_FOREIGN_TABLE) " "
+ " AND w.fdwname = \'postgres_fdw\' "
+ " AND t.ftoptions @> "
+ " ARRAY[\'remotely_inherited=true\'])) "
+ " AND n.nspname = ");
+ deparseStringLiteral(&buf, stmt->remote_schema);
+
+ /* Append EXCEPT/LIMIT TO restrictions */
+ append_import_schema_restrictions(&buf, stmt, conn);
+
+ /* Append ORDER BY at the end of query to ensure output ordering */
+ appendStringInfoString(&buf, " ORDER BY c.relname");
+
+ /* Fetch the data */
+ res = pgfdw_exec_query(conn, buf.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, buf.data);
+
+ /* Save the data */
+ numinherited = PQntuples(res);
+ if (numinherited > 0)
+ {
+ inherited = (char **) palloc0(numinherited * sizeof(char *));
+ for (i = 0; i < numinherited; i++)
+ inherited[i] = pstrdup(PQgetvalue(res, i, 0));
+ }
+
+ PQclear(res);
+ resetStringInfo(&buf);
+
/*
* Fetch all table data from this schema, possibly restricted by EXCEPT or
* LIMIT TO. (We don't actually need to pay any attention to EXCEPT/LIMIT
@@ -6511,35 +6571,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" AND n.nspname = ");
deparseStringLiteral(&buf, stmt->remote_schema);
- /* Partitions are supported since Postgres 10 */
- if (PQserverVersion(conn) >= 100000 &&
- stmt->list_type != FDW_IMPORT_SCHEMA_LIMIT_TO)
- appendStringInfoString(&buf, " AND NOT c.relispartition ");
-
- /* Apply restrictions for LIMIT TO and EXCEPT */
- if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
- stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
- {
- bool first_item = true;
-
- appendStringInfoString(&buf, " AND c.relname ");
- if (stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
- appendStringInfoString(&buf, "NOT ");
- appendStringInfoString(&buf, "IN (");
-
- /* Append list of table names within IN clause */
- foreach(lc, stmt->table_list)
- {
- RangeVar *rv = (RangeVar *) lfirst(lc);
-
- if (first_item)
- first_item = false;
- else
- appendStringInfoString(&buf, ", ");
- deparseStringLiteral(&buf, rv->relname);
- }
- appendStringInfoChar(&buf, ')');
- }
+ /* Append EXCEPT/LIMIT TO restrictions */
+ append_import_schema_restrictions(&buf, stmt, conn);
/* Append ORDER BY at the end of query to ensure output ordering */
appendStringInfoString(&buf, " ORDER BY c.relname, a.attnum");
@@ -6551,6 +6584,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
/* Process results */
numrows = PQntuples(res);
+ inherited_idx = 0;
/* note: incrementation of i happens in inner loop's while() test */
for (i = 0; i < numrows;)
{
@@ -6647,17 +6681,85 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
appendStringInfoString(&buf, ", table_name ");
deparseStringLiteral(&buf, tablename);
+ /*
+ * Also add the remotely_inherited option if needed, to prevent unsafe
+ * modifications to the foreign table (see check_result_rel()).
+ *
+ * By the definitions of the fetch queries using the same snapshot on
+ * the remote server, the inherited is guaranteed to be a strictly
+ * proper subset of the data processed here with the same order as it,
+ * so we determine whether the foreign table is remotely-inherited or
+ * not, by doing a merge join to it.
+ */
+ if (numinherited > 0 && inherited_idx < numinherited &&
+ strcmp(tablename, inherited[inherited_idx]) == 0)
+ {
+ appendStringInfoString(&buf, ", remotely_inherited \'true\'");
+ inherited_idx++;
+ }
+
appendStringInfoString(&buf, ");");
commands = lappend(commands, pstrdup(buf.data));
}
PQclear(res);
+ if (numinherited > 0)
+ {
+ Assert(inherited != NULL);
+ for (i = 0; i < numinherited; i++)
+ {
+ Assert(inherited[i] != NULL);
+ pfree(inherited[i]);
+ }
+ pfree(inherited);
+ }
+
ReleaseConnection(conn);
return commands;
}
+/*
+ * Append EXCEPT/LIMIT TO restrictions to a query.
+ */
+static void
+append_import_schema_restrictions(StringInfo buf,
+ ImportForeignSchemaStmt *stmt,
+ PGconn *conn)
+{
+ /* Partitions are supported since Postgres 10 */
+ if (PQserverVersion(conn) >= 100000 &&
+ stmt->list_type != FDW_IMPORT_SCHEMA_LIMIT_TO)
+ appendStringInfoString(buf, " AND NOT c.relispartition ");
+
+ /* Apply restrictions for LIMIT TO and EXCEPT */
+ if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
+ stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+ {
+ bool first_item = true;
+ ListCell *lc;
+
+ appendStringInfoString(buf, " AND c.relname ");
+ if (stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+ appendStringInfoString(buf, "NOT ");
+ appendStringInfoString(buf, "IN (");
+
+ /* Append list of table names within IN clause */
+ foreach(lc, stmt->table_list)
+ {
+ RangeVar *rv = (RangeVar *) lfirst(lc);
+
+ if (first_item)
+ first_item = false;
+ else
+ appendStringInfoString(buf, ", ");
+ deparseStringLiteral(buf, rv->relname);
+ }
+ appendStringInfoChar(buf, ')');
+ }
+}
+
/*
* Check if reltarget is safe enough to push down semi-join. Reltarget is not
* safe, if it contains references to inner rel relids, which do not belong to
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 31d5ea8a47d..12b7ad2235b 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3275,8 +3275,23 @@ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
ROLLBACK;
-BEGIN;
+-- Check that the remotely_inherited option is set when needed.
+CREATE TABLE import_source.inhchild (c1 int);
+CREATE TABLE import_source.t6 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t6;
+CREATE TABLE import_source.t7 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t7;
+ALTER TABLE import_source.inhchild NO INHERIT import_source.t7;
+CREATE FOREIGN TABLE import_source.t8 (c1 int) SERVER loopback
+ OPTIONS (remotely_inherited 'true');
+
+CREATE SCHEMA import_dest6;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t6, t7, t8)
+ FROM SERVER loopback INTO import_dest6;
+\det+ import_dest6.*
+\d import_dest6.*
+BEGIN;
CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' );
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-05 11:59 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
@ 2026-06-06 20:33 ` Nikita Malakhov <[email protected]>
2026-06-08 12:45 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Nikita Malakhov @ 2026-06-06 20:33 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
Hi!
Thanks for working on the subject! I'll try to take a look inside in a
couple of days.
On Fri, Jun 5, 2026 at 2:59 PM Etsuro Fujita <[email protected]>
wrote:
> On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <[email protected]>
> wrote:
> > I think we should first work on a back-patchable solution. So I'd
> > like to re-propose the patch that I proposed in this thread before to
> > disallow UPDATE/DELETE in problematic cases [1]. Attached is a new
> > version of the patch. Changes are:
> >
> > * Renamed the new table option inherited to remotely_inherited, to
> > avoid confusion with local inheritance.
> > * Moved the logic to prevent problematic UPDATE/DELETE from a planner
> > function to an executor function, to avoid throwing an error
> > unnecessarily when there are no target rows to update/delete.
> > * Added docs to postgres-fdw.sgml.
> >
> > I'm planning to add the postgresImportForeignSchema() support in the
> > next version.
>
> I created the patch to add that support on top of the patch I sent in
> a previous email, which I'm attaching along with the base patch. It's
> the same as before, except that I fixed a typo in docs pointed out by
> Michael-san off-list.
>
> Comments welcome!
>
> Best regards,
> Etsuro Fujita
>
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-05 11:59 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-06 20:33 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
@ 2026-06-08 12:45 ` Etsuro Fujita <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Etsuro Fujita @ 2026-06-08 12:45 UTC (permalink / raw)
To: Nikita Malakhov <[email protected]>; +Cc: Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
Hi Nikita,
On Sun, Jun 7, 2026 at 5:33 AM Nikita Malakhov <[email protected]> wrote:
> Thanks for working on the subject! I'll try to take a look inside in a couple of days.
Great!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-05 11:59 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
@ 2026-06-10 05:37 ` Michael Paquier <[email protected]>
2026-06-10 11:22 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2026-06-10 05:37 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Nikita Malakhov <[email protected]>; Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
On Fri, Jun 05, 2026 at 08:59:17PM +0900, Etsuro Fujita wrote:
> I created the patch to add that support on top of the patch I sent in
> a previous email, which I'm attaching along with the base patch. It's
> the same as before, except that I fixed a typo in docs pointed out by
> Michael-san off-list.
Splitting the patch set into two pieces, as of one for the
introduction of the remotely_inherited option defaulting to the
current HEAD behavior, and one for the modification of the IMPORT
FOREIGN SCHEMA, makes sense here. A backpatch of the first patch is a
no-brainer, so as it gives a way for users to switch to the new
behavior at will. I am however on edge regarding the wisdom of
backpatching the second patch, which would force a new behavior of the
postgres_fdw implementation for partitioned tables (based on my
read of the test with "t4") and INHERIT ("t6", "t8") depending on the
relkind or the property of the relation imported. I can't help but
wonder why you don't take a different, slightly more conservative
approach on HEAD and the stable branches with a new option that can be
specified to the IMPORT FOREIGN SCHEMA query, to make the choice of
setting remotely_inherited for a relation imported an opt-in or
opt-out choice.
I would not object with a switch of the default behavior across major
versions, and perhaps my argument is not sound enough, but I've learnt
my share when it comes to be careful with changes like the one you may
introduce here across a minor release, particularly knowing that
remotely_inherited *can* be set on an option basis when creating a
table *or* when importing a schema. The designs we have for these
queries allows this kind of flexibility.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-05 11:59 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 05:37 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
@ 2026-06-10 11:22 ` Etsuro Fujita <[email protected]>
2026-06-10 22:25 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Etsuro Fujita @ 2026-06-10 11:22 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Nikita Malakhov <[email protected]>; Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
On Wed, Jun 10, 2026 at 2:38 PM Michael Paquier <[email protected]> wrote:
> On Fri, Jun 05, 2026 at 08:59:17PM +0900, Etsuro Fujita wrote:
> > I created the patch to add that support on top of the patch I sent in
> > a previous email, which I'm attaching along with the base patch. It's
> > the same as before, except that I fixed a typo in docs pointed out by
> > Michael-san off-list.
>
> Splitting the patch set into two pieces, as of one for the
> introduction of the remotely_inherited option defaulting to the
> current HEAD behavior, and one for the modification of the IMPORT
> FOREIGN SCHEMA, makes sense here. A backpatch of the first patch is a
> no-brainer, so as it gives a way for users to switch to the new
> behavior at will. I am however on edge regarding the wisdom of
> backpatching the second patch, which would force a new behavior of the
> postgres_fdw implementation for partitioned tables (based on my
> read of the test with "t4") and INHERIT ("t6", "t8") depending on the
> relkind or the property of the relation imported. I can't help but
> wonder why you don't take a different, slightly more conservative
> approach on HEAD and the stable branches with a new option that can be
> specified to the IMPORT FOREIGN SCHEMA query, to make the choice of
> setting remotely_inherited for a relation imported an opt-in or
> opt-out choice.
>
> I would not object with a switch of the default behavior across major
> versions, and perhaps my argument is not sound enough, but I've learnt
> my share when it comes to be careful with changes like the one you may
> introduce here across a minor release, particularly knowing that
> remotely_inherited *can* be set on an option basis when creating a
> table *or* when importing a schema. The designs we have for these
> queries allows this kind of flexibility.
I agree that we should take a more conservative approach especially on
the stable branches, and I think it's a good idea to add the option to
IMPORT FOREIGN SCHEMA for that, so I will update the patch as such in
the next version.
Thanks for the comments!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-05 11:59 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 05:37 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
2026-06-10 11:22 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
@ 2026-06-10 22:25 ` Michael Paquier <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Michael Paquier @ 2026-06-10 22:25 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Nikita Malakhov <[email protected]>; Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
On Wed, Jun 10, 2026 at 08:22:31PM +0900, Etsuro Fujita wrote:
> On Wed, Jun 10, 2026 at 2:38 PM Michael Paquier <[email protected]> wrote:
>> I would not object with a switch of the default behavior across major
>> versions, and perhaps my argument is not sound enough, but I've learnt
>> my share when it comes to be careful with changes like the one you may
>> introduce here across a minor release, particularly knowing that
>> remotely_inherited *can* be set on an option basis when creating a
>> table *or* when importing a schema. The designs we have for these
>> queries allows this kind of flexibility.
>
> I agree that we should take a more conservative approach especially on
> the stable branches, and I think it's a good idea to add the option to
> IMPORT FOREIGN SCHEMA for that, so I will update the patch as such in
> the next version.
Cool, thanks.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
@ 2026-06-10 11:30 ` Etsuro Fujita <[email protected]>
2026-06-10 22:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Etsuro Fujita @ 2026-06-10 11:30 UTC (permalink / raw)
To: Nikita Malakhov <[email protected]>; +Cc: Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <[email protected]> wrote:
> On Fri, May 15, 2026 at 2:23 AM Nikita Malakhov <[email protected]> wrote:
> > CFbot was unhappy with previous patch set, so here's updated one
> I took a quick look at the patch set. IIUC I think it's created based
> on what I proposed in the original thread, which is invasive and thus
> not back-patchable, so what you are proposing here isn't
> back-patchable, either, I think.
One thing I noticed about what I proposed in the original thread (but
didn't when working on it) is that it would well handle cases where
the remote table is a (simple) inherited/partitioned table, but
wouldn't cases where it's e.g., a foreign table on the remote server
pointing to such a table on another remote server. I haven't looked
at your patch in very detail yet, but I tested it as shown below, and
it causes unexpected results, so I suppose it inherits the limitation.
create table pt (a int, b text) partition by list (a);
create table pt_p1 partition of pt for values in (1);
create table pt_p2 partition of pt for values in (2);
create foreign table ft1 (a int, b text) server loopback options
(table_name 'pt');
create foreign table ft2 (a int, b text) server loopback options
(table_name 'ft1');
insert into pt values (1, 'foo'), (2, 'bar');
select ctid, * from ft2;
ctid | a | b
-------+---+-----
(0,1) | 1 | foo
(0,1) | 2 | bar
(2 rows)
explain verbose update ft2 set b = b || b where b = 'bar' and random() < 1.0;
QUERY PLAN
------------------------------------------------------------------------------------------------
Update on public.ft2 (cost=100.00..121.66 rows=0 width=0)
Remote SQL: UPDATE public.ft1 SET b = $3 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.ft2 (cost=100.00..121.66 rows=1 width=106)
Output: (b || b), ctid, tableoid, $0, ft2.*
Filter: (random() < '1'::double precision)
Remote SQL: SELECT a, b, ctid, tableoid FROM public.ft1 WHERE
((b = 'bar')) FOR UPDATE
(6 rows)
update ft2 set b = b || b where b = 'bar' and random() < 1.0;
UPDATE 1
select ctid, * from ft2;
ctid | a | b
-------+---+--------
(0,2) | 1 | barbar
(0,1) | 2 | bar
(2 rows)
The first row belonging to pt_p1 is updated, which is wrong; the
second one belonging to pt_p2 should be updated.
To address this, I think it would be good if we could 1) extend the
concept of inheritance to cover remote inheritances, like pt, and 2)
extend inherited UPDATE/DELETE so that we update/delete leaf tables,
like pt_p2, somehow directly, as done for local inheritances. I'm not
sure about how to do that, though.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 11:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
@ 2026-06-10 22:30 ` Michael Paquier <[email protected]>
2026-06-13 19:43 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Paquier @ 2026-06-10 22:30 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Nikita Malakhov <[email protected]>; Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
On Wed, Jun 10, 2026 at 08:30:46PM +0900, Etsuro Fujita wrote:
> To address this, I think it would be good if we could 1) extend the
> concept of inheritance to cover remote inheritances, like pt, and 2)
> extend inherited UPDATE/DELETE so that we update/delete leaf tables,
> like pt_p2, somehow directly, as done for local inheritances. I'm not
> sure about how to do that, though.
FWIW, I think that there is a good argument for keeping it down to
simpler, and just not care about the option chain in such cases,
leaving it up to users to address that with two imports anyway? Just
having the option at one level would solve most historical problems I
could see on this thread. Good is sometimes a better option than
theoretically perfect. And good here means a simpler implementation
overall, at least it seems to me so..
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 11:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 22:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
@ 2026-06-13 19:43 ` Nikita Malakhov <[email protected]>
2026-06-15 14:54 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nikita Malakhov @ 2026-06-13 19:43 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
Hi!
While testing the proposed solution we've stumbled upon another vanilla bug
related to FDW -
a query with DELETE ... USING selects invalid records from partitioned FDW
tables:
CREATE EXTENSION postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
END;
$d$;
CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
CREATE USER MAPPING FOR public SERVER loopback3;
CREATE TABLE acc_entry
(
id bigint,
doc_date date,
impact int,
amount numeric
) PARTITION BY RANGE (doc_date);
CREATE TABLE acc_entry_p1
PARTITION OF acc_entry
FOR VALUES FROM ('2025-01-01') TO ('2025-07-01');
CREATE TABLE acc_entry_p2
PARTITION OF acc_entry
FOR VALUES FROM ('2025-07-01') TO ('2026-01-01');
CREATE FOREIGN TABLE measurement_fdw
(
id bigint,
doc_date date,
impact int,
amount numeric
)
SERVER loopback
OPTIONS (table_name 'acc_entry');
INSERT INTO acc_entry
SELECT
CASE
WHEN g IN (4,15,26,35,46,55,66,75,86,95)
THEN 2501020100000124
ELSE g
END AS id,
CASE WHEN g % 2 = 0 THEN timestamp '2025-02-02' ELSE timestamp
'2025-08-08' END,
1,
g
FROM generate_series(1,100) g;
DELETE FROM measurement_fdw
USING (
SELECT id
FROM measurement_fdw
WHERE id = 2501020100000124
LIMIT 1
) s
WHERE measurement_fdw.id = s.id;
The latter query selects and deletes records with invalid ID which should
not be selected at all.
Although rewritten query like
with sub as (
select t1.id sub_id
from measurement_fdw t1
where t1.id=2501020100000124
limit 1
)
select m.id, m.doc_date, m.impact, m.amount from measurement_fdw m, sub
where m.id = sub.sub_id;
works correctly.
Currently I try to figure out what's the cause of this strange behavior and
I'm suspicious about
/*
* WCO_RLS_MERGE_DELETE_CHECK is used to check DELETE USING quals on
* the existing target row.
*/
add_with_check_options(rel, rt_index,
WCO_RLS_MERGE_DELETE_CHECK,
merge_delete_permissive_policies,
merge_delete_restrictive_policies,
withCheckOptions,
hasSubLinks,
hasSubLinks,
true);
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 11:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 22:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
2026-06-13 19:43 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
@ 2026-06-15 14:54 ` Etsuro Fujita <[email protected]>
2026-07-09 14:03 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Etsuro Fujita @ 2026-06-15 14:54 UTC (permalink / raw)
To: Nikita Malakhov <[email protected]>; +Cc: Michael Paquier <[email protected]>; Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
On Sun, Jun 14, 2026 at 4:43 AM Nikita Malakhov <[email protected]> wrote:
> While testing the proposed solution we've stumbled upon another vanilla bug related to FDW -
> a query with DELETE ... USING selects invalid records from partitioned FDW tables:
> CREATE TABLE acc_entry
> (
> id bigint,
> doc_date date,
> impact int,
> amount numeric
> ) PARTITION BY RANGE (doc_date);
>
> CREATE TABLE acc_entry_p1
> PARTITION OF acc_entry
> FOR VALUES FROM ('2025-01-01') TO ('2025-07-01');
>
> CREATE TABLE acc_entry_p2
> PARTITION OF acc_entry
> FOR VALUES FROM ('2025-07-01') TO ('2026-01-01');
>
> CREATE FOREIGN TABLE measurement_fdw
> (
> id bigint,
> doc_date date,
> impact int,
> amount numeric
> )
> SERVER loopback
> OPTIONS (table_name 'acc_entry');
>
> INSERT INTO acc_entry
> SELECT
> CASE
> WHEN g IN (4,15,26,35,46,55,66,75,86,95)
> THEN 2501020100000124
> ELSE g
> END AS id,
> CASE WHEN g % 2 = 0 THEN timestamp '2025-02-02' ELSE timestamp '2025-08-08' END,
> 1,
> g
> FROM generate_series(1,100) g;
>
> DELETE FROM measurement_fdw
> USING (
> SELECT id
> FROM measurement_fdw
> WHERE id = 2501020100000124
> LIMIT 1
> ) s
> WHERE measurement_fdw.id = s.id;
>
> The latter query selects and deletes records with invalid ID which should not be selected at all.
I think that that would be another example that the bug discussed here
causes unexpected results, as I have this after inserting the data
into the partitioned table:
select tableoid::regclass, ctid, * from acc_entry where ctid in
(select ctid from acc_entry where id = 2501020100000124);
tableoid | ctid | id | doc_date | impact | amount
--------------+--------+------------------+------------+--------+--------
acc_entry_p1 | (0,2) | 2501020100000124 | 2025-02-02 | 1 | 4
acc_entry_p1 | (0,8) | 16 | 2025-02-02 | 1 | 16
acc_entry_p1 | (0,13) | 2501020100000124 | 2025-02-02 | 1 | 26
acc_entry_p1 | (0,18) | 36 | 2025-02-02 | 1 | 36
acc_entry_p1 | (0,23) | 2501020100000124 | 2025-02-02 | 1 | 46
acc_entry_p1 | (0,28) | 56 | 2025-02-02 | 1 | 56
acc_entry_p1 | (0,33) | 2501020100000124 | 2025-02-02 | 1 | 66
acc_entry_p1 | (0,38) | 76 | 2025-02-02 | 1 | 76
acc_entry_p1 | (0,43) | 2501020100000124 | 2025-02-02 | 1 | 86
acc_entry_p1 | (0,48) | 96 | 2025-02-02 | 1 | 96
acc_entry_p2 | (0,2) | 3 | 2025-08-08 | 1 | 3
acc_entry_p2 | (0,8) | 2501020100000124 | 2025-08-08 | 1 | 15
acc_entry_p2 | (0,13) | 25 | 2025-08-08 | 1 | 25
acc_entry_p2 | (0,18) | 2501020100000124 | 2025-08-08 | 1 | 35
acc_entry_p2 | (0,23) | 45 | 2025-08-08 | 1 | 45
acc_entry_p2 | (0,28) | 2501020100000124 | 2025-08-08 | 1 | 55
acc_entry_p2 | (0,33) | 65 | 2025-08-08 | 1 | 65
acc_entry_p2 | (0,38) | 2501020100000124 | 2025-08-08 | 1 | 75
acc_entry_p2 | (0,43) | 85 | 2025-08-08 | 1 | 85
acc_entry_p2 | (0,48) | 2501020100000124 | 2025-08-08 | 1 | 95
(20 rows)
Note that the rows with normal ids have the same ctid as the rows with
id=2501020100000124 (for example, ctid of the row with id=3 is (0,2),
which is the same as that of the first row, which has
id=2501020100000124), so the bug would delete such normal-id rows as
well when performing the delete query.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 11:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 22:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
2026-06-13 19:43 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
2026-06-15 14:54 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
@ 2026-07-09 14:03 ` Nikita Malakhov <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Nikita Malakhov @ 2026-07-09 14:03 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Michael Paquier <[email protected]>; Jehan-Guillaume de Rorthais <[email protected]>; [email protected]
Hi hackers!
I've fixed the issue mentioned in two previous messages, so for anyone
already have taken
my patch set above I'd compiled a fixed patch set. It does not object an
approach
proposed by Etsuro-san.
The first patch file
v4-0001-copy-and-remote-tableoid-param.patch
is a core patch introducing extended copy slot and tuple facilities
and core machinery for remote table OIDs;
The second file
v4-0002-teach-fdw-use-remote-tableoid.patch
is a fdw facilities changes (fixed) to use remote table OID.
On Mon, Jun 15, 2026 at 5:55 PM Etsuro Fujita <[email protected]>
wrote:
> On Sun, Jun 14, 2026 at 4:43 AM Nikita Malakhov <[email protected]> wrote:
> > While testing the proposed solution we've stumbled upon another vanilla
> bug related to FDW -
> > a query with DELETE ... USING selects invalid records from partitioned
> FDW tables:
>
> > CREATE TABLE acc_entry
> > (
> > id bigint,
> > doc_date date,
> > impact int,
> > amount numeric
> > ) PARTITION BY RANGE (doc_date);
> >
> > CREATE TABLE acc_entry_p1
> > PARTITION OF acc_entry
> > FOR VALUES FROM ('2025-01-01') TO ('2025-07-01');
> >
> > CREATE TABLE acc_entry_p2
> > PARTITION OF acc_entry
> > FOR VALUES FROM ('2025-07-01') TO ('2026-01-01');
> >
> > CREATE FOREIGN TABLE measurement_fdw
> > (
> > id bigint,
> > doc_date date,
> > impact int,
> > amount numeric
> > )
> > SERVER loopback
> > OPTIONS (table_name 'acc_entry');
> >
> > INSERT INTO acc_entry
> > SELECT
> > CASE
> > WHEN g IN (4,15,26,35,46,55,66,75,86,95)
> > THEN 2501020100000124
> > ELSE g
> > END AS id,
> > CASE WHEN g % 2 = 0 THEN timestamp '2025-02-02' ELSE timestamp
> '2025-08-08' END,
> > 1,
> > g
> > FROM generate_series(1,100) g;
> >
> > DELETE FROM measurement_fdw
> > USING (
> > SELECT id
> > FROM measurement_fdw
> > WHERE id = 2501020100000124
> > LIMIT 1
> > ) s
> > WHERE measurement_fdw.id = s.id;
> >
> > The latter query selects and deletes records with invalid ID which
> should not be selected at all.
>
> I think that that would be another example that the bug discussed here
> causes unexpected results, as I have this after inserting the data
> into the partitioned table:
>
> select tableoid::regclass, ctid, * from acc_entry where ctid in
> (select ctid from acc_entry where id = 2501020100000124);
> tableoid | ctid | id | doc_date | impact | amount
> --------------+--------+------------------+------------+--------+--------
> acc_entry_p1 | (0,2) | 2501020100000124 | 2025-02-02 | 1 | 4
> acc_entry_p1 | (0,8) | 16 | 2025-02-02 | 1 | 16
> acc_entry_p1 | (0,13) | 2501020100000124 | 2025-02-02 | 1 | 26
> acc_entry_p1 | (0,18) | 36 | 2025-02-02 | 1 | 36
> acc_entry_p1 | (0,23) | 2501020100000124 | 2025-02-02 | 1 | 46
> acc_entry_p1 | (0,28) | 56 | 2025-02-02 | 1 | 56
> acc_entry_p1 | (0,33) | 2501020100000124 | 2025-02-02 | 1 | 66
> acc_entry_p1 | (0,38) | 76 | 2025-02-02 | 1 | 76
> acc_entry_p1 | (0,43) | 2501020100000124 | 2025-02-02 | 1 | 86
> acc_entry_p1 | (0,48) | 96 | 2025-02-02 | 1 | 96
> acc_entry_p2 | (0,2) | 3 | 2025-08-08 | 1 | 3
> acc_entry_p2 | (0,8) | 2501020100000124 | 2025-08-08 | 1 | 15
> acc_entry_p2 | (0,13) | 25 | 2025-08-08 | 1 | 25
> acc_entry_p2 | (0,18) | 2501020100000124 | 2025-08-08 | 1 | 35
> acc_entry_p2 | (0,23) | 45 | 2025-08-08 | 1 | 45
> acc_entry_p2 | (0,28) | 2501020100000124 | 2025-08-08 | 1 | 55
> acc_entry_p2 | (0,33) | 65 | 2025-08-08 | 1 | 65
> acc_entry_p2 | (0,38) | 2501020100000124 | 2025-08-08 | 1 | 75
> acc_entry_p2 | (0,43) | 85 | 2025-08-08 | 1 | 85
> acc_entry_p2 | (0,48) | 2501020100000124 | 2025-08-08 | 1 | 95
> (20 rows)
>
> Note that the rows with normal ids have the same ctid as the rows with
> id=2501020100000124 (for example, ctid of the row with id=3 is (0,2),
> which is the same as that of the first row, which has
> id=2501020100000124), so the bug would delete such normal-id rows as
> well when performing the delete query.
>
> Best regards,
> Etsuro Fujita
>
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
Attachments:
[application/octet-stream] v4-0001-copy-and-remote-tableoid-param.patch (41.3K, ../../CAN-LCVMboJszNufW+4NRZh4M6c-u74RM4Y7bfjy_8PVmQVktJg@mail.gmail.com/3-v4-0001-copy-and-remote-tableoid-param.patch)
download | inline diff:
From 7c03cd1156b5e1ecbb5c063a22fba3a047a02c24 Mon Sep 17 00:00:00 2001
From: Nikita Malakhov <[email protected]>
Date: Thu, 9 Jul 2026 16:21:32 +0300
Subject: [PATCH] DELETE/UPDATE more than one row in partitioned foreign table
(1/2)
There is known bug in delete and update rows in partitioned foreign
tables via FDW [1]
Current copyTuple and copySlot machinery supposes that
source and destination slots and tuples always match. This patch modifies
copy internals to consider more complex case and copy only actual attributes,
and if destination slot/tuple has more they are set to null. This is a proof
of concept patch which shows such mechanics does not break anything and
allows to use it in cases when we have to pass or receive not matching number
of attributes.
Core patch - introduction of remote table OID returned from remote
query and passing it through planner and executor to use in foreign
queries
[1] Discussion: https://www.postgresql.org/message-id/flat/20250718175314.4513c00a%40karst
---
src/backend/access/common/heaptuple.c | 127 ++++++++++++++++++++++--
src/backend/executor/execGrouping.c | 3 +-
src/backend/executor/execMain.c | 4 +
src/backend/executor/execTuples.c | 107 ++++++++++++++------
src/backend/executor/nodeForeignscan.c | 2 +
src/backend/optimizer/path/allpaths.c | 20 ++++
src/backend/optimizer/plan/createplan.c | 19 ++++
src/backend/optimizer/plan/initsplan.c | 45 +++++++++
src/backend/optimizer/plan/planner.c | 14 ++-
src/backend/optimizer/plan/setrefs.c | 56 +++++++++++
src/backend/optimizer/plan/subselect.c | 7 +-
src/backend/optimizer/util/appendinfo.c | 27 +++++
src/backend/optimizer/util/inherit.c | 1 +
src/backend/optimizer/util/relnode.c | 21 ++++
src/backend/optimizer/util/tlist.c | 39 ++++++--
src/backend/utils/adt/ruleutils.c | 2 +-
src/include/access/htup_details.h | 22 ++++
src/include/executor/tuptable.h | 42 ++++++--
src/include/nodes/pathnodes.h | 7 ++
src/include/nodes/primnodes.h | 1 +
20 files changed, 500 insertions(+), 66 deletions(-)
diff --git a/src/backend/access/common/heaptuple.c b/src/backend/access/common/heaptuple.c
index f30346469ed..d4fbd962ec1 100644
--- a/src/backend/access/common/heaptuple.c
+++ b/src/backend/access/common/heaptuple.c
@@ -61,6 +61,7 @@
#include "access/sysattr.h"
#include "access/tupdesc_details.h"
#include "common/hashfn.h"
+#include "executor/tuptable.h"
#include "utils/datum.h"
#include "utils/expandeddatum.h"
#include "utils/hsearch.h"
@@ -219,11 +220,30 @@ Size
heap_compute_data_size(TupleDesc tupleDesc,
const Datum *values,
const bool *isnull)
+{
+ return heap_compute_data_size_ext(tupleDesc, values, isnull,
+ - 1 /* take natts from tupleDesc */);
+}
+
+/*
+ * heap_compute_data_size_ext
+ * Same as above, but allows to specify number of attributes explicitly.
+ *
+ * If "natts" <= 0, then number of attributes is taken from the TupleDesc.
+ */
+Size
+heap_compute_data_size_ext(TupleDesc tupleDesc,
+ const Datum *values,
+ const bool *isnull,
+ int natts)
{
Size data_length = 0;
int i;
int numberOfAttributes = tupleDesc->natts;
+ if (natts > 0)
+ numberOfAttributes = natts;
+
for (i = 0; i < numberOfAttributes; i++)
{
Datum val;
@@ -402,6 +422,22 @@ heap_fill_tuple(TupleDesc tupleDesc,
const Datum *values, const bool *isnull,
char *data, Size data_size,
uint16 *infomask, uint8 *bit)
+{
+ heap_fill_tuple_ext(tupleDesc, values, isnull, data, data_size, infomask,
+ bit, -1 /* take natts from tupleDesc */);
+}
+
+/*
+ * heap_fill_tuple_ext
+ * Same as above, but allows to specify number of attributes explicitly.
+ *
+ * If "natts" <= 0, then number of attributes is taken from the TupleDesc.
+ */
+void
+heap_fill_tuple_ext(TupleDesc tupleDesc,
+ const Datum *values, const bool *isnull,
+ char *data, Size data_size,
+ uint16 *infomask, uint8 *bit, int natts)
{
uint8 *bitP;
int bitmask;
@@ -412,6 +448,9 @@ heap_fill_tuple(TupleDesc tupleDesc,
char *start = data;
#endif
+ if (natts > 0)
+ numberOfAttributes = natts;
+
if (bit != NULL)
{
bitP = &bit[-1];
@@ -684,18 +723,48 @@ heap_getsysattr(HeapTuple tup, int attnum, TupleDesc tupleDesc, bool *isnull)
*/
HeapTuple
heap_copytuple(HeapTuple tuple)
+{
+ return heap_copytuple_ext(tuple, NULL, -1);
+}
+
+/* ----------------
+ * heap_copytuple_ext
+ *
+ * returns a copy of a tuple consists of attnum atts of original
+ *
+ * The HeapTuple struct, tuple header, and tuple data are all allocated
+ * as a single palloc() block.
+ * ----------------
+ */
+HeapTuple
+heap_copytuple_ext(HeapTuple tuple, TupleTableSlot *slot, int dstnatts)
{
HeapTuple newTuple;
+ Size tuplen;
if (!HeapTupleIsValid(tuple) || tuple->t_data == NULL)
return NULL;
- newTuple = (HeapTuple) palloc(HEAPTUPLESIZE + tuple->t_len);
- newTuple->t_len = tuple->t_len;
+ tuplen = tuple->t_len;
+
+ if (slot != NULL)
+ {
+ if (dstnatts < 0)
+ dstnatts = slot->tts_tupleDescriptor->natts;
+
+ if (dstnatts < slot->tts_tupleDescriptor->natts)
+ tuplen = heap_compute_data_size_ext(slot->tts_tupleDescriptor,
+ slot->tts_values,
+ slot->tts_isnull,
+ dstnatts);
+ }
+
+ newTuple = (HeapTuple) palloc(HEAPTUPLESIZE + tuplen);
+ newTuple->t_len = tuplen;
newTuple->t_self = tuple->t_self;
newTuple->t_tableOid = tuple->t_tableOid;
newTuple->t_data = (HeapTupleHeader) ((char *) newTuple + HEAPTUPLESIZE);
- memcpy(newTuple->t_data, tuple->t_data, tuple->t_len);
+ memcpy(newTuple->t_data, tuple->t_data, tuplen);
return newTuple;
}
@@ -1025,6 +1094,21 @@ HeapTuple
heap_form_tuple(TupleDesc tupleDescriptor,
const Datum *values,
const bool *isnull)
+{
+ return heap_form_tuple_ext(tupleDescriptor, values, isnull, -1);
+}
+
+/*
+ * heap_form_tuple_ext
+ * Same as above, but allows to specify number of attributes explicitly.
+ *
+ * If "natts" <= 0, then number of attributes is taken from the TupleDesc.
+ */
+HeapTuple
+heap_form_tuple_ext(TupleDesc tupleDescriptor,
+ const Datum *values,
+ const bool *isnull,
+ int natts)
{
HeapTuple tuple; /* return tuple */
HeapTupleHeader td; /* tuple data */
@@ -1035,6 +1119,9 @@ heap_form_tuple(TupleDesc tupleDescriptor,
int numberOfAttributes = tupleDescriptor->natts;
int i;
+ if(natts > 0)
+ numberOfAttributes = natts;
+
if (numberOfAttributes > MaxTupleAttributeNumber)
ereport(ERROR,
(errcode(ERRCODE_TOO_MANY_COLUMNS),
@@ -1063,7 +1150,7 @@ heap_form_tuple(TupleDesc tupleDescriptor,
hoff = len = MAXALIGN(len); /* align user data safely */
- data_len = heap_compute_data_size(tupleDescriptor, values, isnull);
+ data_len = heap_compute_data_size_ext(tupleDescriptor, values, isnull, natts);
len += data_len;
@@ -1092,13 +1179,14 @@ heap_form_tuple(TupleDesc tupleDescriptor,
HeapTupleHeaderSetNatts(td, numberOfAttributes);
td->t_hoff = hoff;
- heap_fill_tuple(tupleDescriptor,
+ heap_fill_tuple_ext(tupleDescriptor,
values,
isnull,
(char *) td + hoff,
data_len,
&td->t_infomask,
- (hasnull ? td->t_bits : NULL));
+ (hasnull ? td->t_bits : NULL),
+ natts);
return tuple;
}
@@ -1391,6 +1479,23 @@ heap_form_minimal_tuple(TupleDesc tupleDescriptor,
const Datum *values,
const bool *isnull,
Size extra)
+{
+ return heap_form_minimal_tuple_ext(tupleDescriptor, values, isnull, extra,
+ -1 /* take natts from tupleDesc */);
+}
+
+/*
+ * heap_form_minimal_tuple_ext
+ * Same as above, but allows to specify number of attributes explicitly.
+ *
+ * If "natts" <= 0, then number of attributes is taken from the TupleDesc.
+ */
+MinimalTuple
+heap_form_minimal_tuple_ext(TupleDesc tupleDescriptor,
+ const Datum *values,
+ const bool *isnull,
+ Size extra,
+ int natts)
{
MinimalTuple tuple; /* return tuple */
char *mem;
@@ -1401,6 +1506,9 @@ heap_form_minimal_tuple(TupleDesc tupleDescriptor,
int numberOfAttributes = tupleDescriptor->natts;
int i;
+ if (natts > 0)
+ numberOfAttributes = natts;
+
Assert(extra == MAXALIGN(extra));
if (numberOfAttributes > MaxTupleAttributeNumber)
@@ -1431,7 +1539,7 @@ heap_form_minimal_tuple(TupleDesc tupleDescriptor,
hoff = len = MAXALIGN(len); /* align user data safely */
- data_len = heap_compute_data_size(tupleDescriptor, values, isnull);
+ data_len = heap_compute_data_size_ext(tupleDescriptor, values, isnull, natts);
len += data_len;
@@ -1448,13 +1556,14 @@ heap_form_minimal_tuple(TupleDesc tupleDescriptor,
HeapTupleHeaderSetNatts(tuple, numberOfAttributes);
tuple->t_hoff = hoff + MINIMAL_TUPLE_OFFSET;
- heap_fill_tuple(tupleDescriptor,
+ heap_fill_tuple_ext(tupleDescriptor,
values,
isnull,
(char *) tuple + hoff,
data_len,
&tuple->t_infomask,
- (hasnull ? tuple->t_bits : NULL));
+ (hasnull ? tuple->t_bits : NULL),
+ natts);
return tuple;
}
diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c
index c107514a85d..7ef7c8c6d9d 100644
--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -583,7 +583,8 @@ LookupTupleHashEntry_internal(TupleHashTable hashtable, TupleTableSlot *slot,
* TupleHashEntryData or allocate an additional chunk.
*/
entry->firstTuple = ExecCopySlotMinimalTupleExtra(slot,
- hashtable->additionalsize);
+ hashtable->additionalsize,
+ -1 /* natts */);
}
}
else
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index fde502efd38..5bd766a3a81 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -2658,6 +2658,10 @@ ExecBuildAuxRowMark(ExecRowMark *erm, List *targetlist)
resname);
if (!AttributeNumberIsValid(aerm->ctidAttNo))
elog(ERROR, "could not find junk %s column", resname);
+//fdwproblem
+ snprintf(resname, sizeof(resname), "tableoid%u", erm->rowmarkId);
+ aerm->toidAttNo = ExecFindJunkAttributeInTlist(targetlist,
+ resname);
}
else
{
diff --git a/src/backend/executor/execTuples.c b/src/backend/executor/execTuples.c
index 7f4ebf95432..5462d26532a 100644
--- a/src/backend/executor/execTuples.c
+++ b/src/backend/executor/execTuples.c
@@ -79,6 +79,9 @@ static inline void tts_buffer_heap_store_tuple(TupleTableSlot *slot,
Buffer buffer,
bool transfer_pin);
static void tts_heap_store_tuple(TupleTableSlot *slot, HeapTuple tuple, bool shouldFree);
+static void tts_heap_materialize_ext(TupleTableSlot *slot, int natts);
+static void tts_minimal_materialize_ext(TupleTableSlot *slot, int natts);
+static void tts_buffer_heap_materialize_ext(TupleTableSlot *slot, int natts);
const TupleTableSlotOps TTSOpsVirtual;
@@ -269,18 +272,29 @@ static void
tts_virtual_copyslot(TupleTableSlot *dstslot, TupleTableSlot *srcslot)
{
TupleDesc srcdesc = srcslot->tts_tupleDescriptor;
+ TupleDesc dstdesc = dstslot->tts_tupleDescriptor;
+ int min_natts = Min(dstdesc->natts, srcdesc->natts);
tts_virtual_clear(dstslot);
slot_getallattrs(srcslot);
- for (int natt = 0; natt < srcdesc->natts; natt++)
+ for (int natt = 0; natt < min_natts; natt++)
{
dstslot->tts_values[natt] = srcslot->tts_values[natt];
dstslot->tts_isnull[natt] = srcslot->tts_isnull[natt];
}
- dstslot->tts_nvalid = srcdesc->natts;
+ if (dstdesc->natts > srcdesc->natts)
+ {
+ for (int natt = min_natts; natt < dstdesc->natts; natt++)
+ {
+ dstslot->tts_values[natt] = (Datum) 0;
+ dstslot->tts_isnull[natt] = true;
+ }
+ }
+
+ dstslot->tts_nvalid = dstdesc->natts;
dstslot->tts_flags &= ~TTS_FLAG_EMPTY;
/* make sure storage doesn't depend on external memory */
@@ -288,24 +302,26 @@ tts_virtual_copyslot(TupleTableSlot *dstslot, TupleTableSlot *srcslot)
}
static HeapTuple
-tts_virtual_copy_heap_tuple(TupleTableSlot *slot)
+tts_virtual_copy_heap_tuple(TupleTableSlot *slot, int natts)
{
Assert(!TTS_EMPTY(slot));
- return heap_form_tuple(slot->tts_tupleDescriptor,
+ return heap_form_tuple_ext(slot->tts_tupleDescriptor,
slot->tts_values,
- slot->tts_isnull);
+ slot->tts_isnull,
+ natts);
}
static MinimalTuple
-tts_virtual_copy_minimal_tuple(TupleTableSlot *slot, Size extra)
+tts_virtual_copy_minimal_tuple(TupleTableSlot *slot, Size extra, int natts)
{
Assert(!TTS_EMPTY(slot));
- return heap_form_minimal_tuple(slot->tts_tupleDescriptor,
+ return heap_form_minimal_tuple_ext(slot->tts_tupleDescriptor,
slot->tts_values,
slot->tts_isnull,
- extra);
+ extra,
+ natts);
}
@@ -397,6 +413,12 @@ tts_heap_is_current_xact_tuple(TupleTableSlot *slot)
static void
tts_heap_materialize(TupleTableSlot *slot)
+{
+ tts_heap_materialize_ext(slot, -1);
+}
+
+static void
+tts_heap_materialize_ext(TupleTableSlot *slot, int natts)
{
HeapTupleTableSlot *hslot = (HeapTupleTableSlot *) slot;
MemoryContext oldContext;
@@ -417,9 +439,10 @@ tts_heap_materialize(TupleTableSlot *slot)
hslot->off = 0;
if (!hslot->tuple)
- hslot->tuple = heap_form_tuple(slot->tts_tupleDescriptor,
+ hslot->tuple = heap_form_tuple_ext(slot->tts_tupleDescriptor,
slot->tts_values,
- slot->tts_isnull);
+ slot->tts_isnull,
+ natts);
else
{
/*
@@ -427,7 +450,7 @@ tts_heap_materialize(TupleTableSlot *slot)
* context of the given slot (else it would have TTS_FLAG_SHOULDFREE
* set). Copy the tuple into the given slot's memory context.
*/
- hslot->tuple = heap_copytuple(hslot->tuple);
+ hslot->tuple = heap_copytuple_ext(hslot->tuple, slot, natts);
}
slot->tts_flags |= TTS_FLAG_SHOULDFREE;
@@ -461,24 +484,24 @@ tts_heap_get_heap_tuple(TupleTableSlot *slot)
}
static HeapTuple
-tts_heap_copy_heap_tuple(TupleTableSlot *slot)
+tts_heap_copy_heap_tuple(TupleTableSlot *slot, int natts)
{
HeapTupleTableSlot *hslot = (HeapTupleTableSlot *) slot;
Assert(!TTS_EMPTY(slot));
if (!hslot->tuple)
- tts_heap_materialize(slot);
+ tts_heap_materialize_ext(slot, natts);
- return heap_copytuple(hslot->tuple);
+ return heap_copytuple_ext(hslot->tuple, slot, natts);
}
static MinimalTuple
-tts_heap_copy_minimal_tuple(TupleTableSlot *slot, Size extra)
+tts_heap_copy_minimal_tuple(TupleTableSlot *slot, Size extra, int natts)
{
HeapTupleTableSlot *hslot = (HeapTupleTableSlot *) slot;
if (!hslot->tuple)
- tts_heap_materialize(slot);
+ tts_heap_materialize_ext(slot, natts);
return minimal_tuple_from_heap_tuple(hslot->tuple, extra);
}
@@ -585,6 +608,12 @@ tts_minimal_is_current_xact_tuple(TupleTableSlot *slot)
static void
tts_minimal_materialize(TupleTableSlot *slot)
+{
+ tts_minimal_materialize_ext(slot, -1);
+}
+
+static void
+tts_minimal_materialize_ext(TupleTableSlot *slot, int natts)
{
MinimalTupleTableSlot *mslot = (MinimalTupleTableSlot *) slot;
MemoryContext oldContext;
@@ -606,10 +635,11 @@ tts_minimal_materialize(TupleTableSlot *slot)
if (!mslot->mintuple)
{
- mslot->mintuple = heap_form_minimal_tuple(slot->tts_tupleDescriptor,
+ mslot->mintuple = heap_form_minimal_tuple_ext(slot->tts_tupleDescriptor,
slot->tts_values,
slot->tts_isnull,
- 0);
+ 0 /* no extra */,
+ natts);
}
else
{
@@ -637,9 +667,13 @@ tts_minimal_copyslot(TupleTableSlot *dstslot, TupleTableSlot *srcslot)
{
MemoryContext oldcontext;
MinimalTuple mintuple;
+ int min_natts;
+
+ min_natts = Min(dstslot->tts_tupleDescriptor->natts,
+ srcslot->tts_tupleDescriptor->natts);
oldcontext = MemoryContextSwitchTo(dstslot->tts_mcxt);
- mintuple = ExecCopySlotMinimalTuple(srcslot);
+ mintuple = ExecCopySlotMinimalTupleExteded(srcslot, min_natts);
MemoryContextSwitchTo(oldcontext);
ExecStoreMinimalTuple(mintuple, dstslot, true);
@@ -657,7 +691,7 @@ tts_minimal_get_minimal_tuple(TupleTableSlot *slot)
}
static HeapTuple
-tts_minimal_copy_heap_tuple(TupleTableSlot *slot)
+tts_minimal_copy_heap_tuple(TupleTableSlot *slot, int natts)
{
MinimalTupleTableSlot *mslot = (MinimalTupleTableSlot *) slot;
@@ -668,7 +702,7 @@ tts_minimal_copy_heap_tuple(TupleTableSlot *slot)
}
static MinimalTuple
-tts_minimal_copy_minimal_tuple(TupleTableSlot *slot, Size extra)
+tts_minimal_copy_minimal_tuple(TupleTableSlot *slot, Size extra, int natts)
{
MinimalTupleTableSlot *mslot = (MinimalTupleTableSlot *) slot;
@@ -802,6 +836,12 @@ tts_buffer_is_current_xact_tuple(TupleTableSlot *slot)
static void
tts_buffer_heap_materialize(TupleTableSlot *slot)
+{
+ tts_buffer_heap_materialize_ext(slot, -1 /* natts */);
+}
+
+static void
+tts_buffer_heap_materialize_ext(TupleTableSlot *slot, int natts)
{
BufferHeapTupleTableSlot *bslot = (BufferHeapTupleTableSlot *) slot;
MemoryContext oldContext;
@@ -874,12 +914,16 @@ tts_buffer_heap_copyslot(TupleTableSlot *dstslot, TupleTableSlot *srcslot)
TTS_SHOULDFREE(srcslot) ||
!bsrcslot->base.tuple)
{
+ int min_natts;
MemoryContext oldContext;
+ min_natts = Min(dstslot->tts_tupleDescriptor->natts,
+ srcslot->tts_tupleDescriptor->natts);
+
ExecClearTuple(dstslot);
dstslot->tts_flags &= ~TTS_FLAG_EMPTY;
oldContext = MemoryContextSwitchTo(dstslot->tts_mcxt);
- bdstslot->base.tuple = ExecCopySlotHeapTuple(srcslot);
+ bdstslot->base.tuple = ExecCopySlotHeapTupleExteded(srcslot, min_natts);
dstslot->tts_flags |= TTS_FLAG_SHOULDFREE;
MemoryContextSwitchTo(oldContext);
}
@@ -915,27 +959,27 @@ tts_buffer_heap_get_heap_tuple(TupleTableSlot *slot)
}
static HeapTuple
-tts_buffer_heap_copy_heap_tuple(TupleTableSlot *slot)
+tts_buffer_heap_copy_heap_tuple(TupleTableSlot *slot, int natts)
{
BufferHeapTupleTableSlot *bslot = (BufferHeapTupleTableSlot *) slot;
Assert(!TTS_EMPTY(slot));
if (!bslot->base.tuple)
- tts_buffer_heap_materialize(slot);
+ tts_buffer_heap_materialize_ext(slot, natts);
- return heap_copytuple(bslot->base.tuple);
+ return heap_copytuple_ext(bslot->base.tuple, slot, natts);
}
static MinimalTuple
-tts_buffer_heap_copy_minimal_tuple(TupleTableSlot *slot, Size extra)
+tts_buffer_heap_copy_minimal_tuple(TupleTableSlot *slot, Size extra, int natts)
{
BufferHeapTupleTableSlot *bslot = (BufferHeapTupleTableSlot *) slot;
Assert(!TTS_EMPTY(slot));
if (!bslot->base.tuple)
- tts_buffer_heap_materialize(slot);
+ tts_buffer_heap_materialize_ext(slot, natts);
return minimal_tuple_from_heap_tuple(bslot->base.tuple, extra);
}
@@ -1874,8 +1918,7 @@ ExecStoreAllNullTuple(TupleTableSlot *slot)
* Until the slot is materialized, the contents of the slot depend on the
* datum.
*/
-void
-ExecStoreHeapTupleDatum(Datum data, TupleTableSlot *slot)
+void ExecStoreHeapTupleDatum(Datum data, TupleTableSlot *slot)
{
HeapTupleData tuple = {0};
HeapTupleHeader td;
@@ -1885,6 +1928,7 @@ ExecStoreHeapTupleDatum(Datum data, TupleTableSlot *slot)
tuple.t_len = HeapTupleHeaderGetDatumLength(td);
tuple.t_self = td->t_ctid;
tuple.t_data = td;
+ tuple.t_tableOid = InvalidOid;
ExecClearTuple(slot);
@@ -1929,7 +1973,7 @@ ExecFetchSlotHeapTuple(TupleTableSlot *slot, bool materialize, bool *shouldFree)
{
if (shouldFree)
*shouldFree = true;
- return slot->tts_ops->copy_heap_tuple(slot);
+ return slot->tts_ops->copy_heap_tuple(slot, -1 /* natts */);
}
else
{
@@ -1980,7 +2024,8 @@ ExecFetchSlotMinimalTuple(TupleTableSlot *slot,
{
if (shouldFree)
*shouldFree = true;
- return slot->tts_ops->copy_minimal_tuple(slot, 0);
+ return slot->tts_ops->copy_minimal_tuple(slot, 0 /* no extra */,
+ -1 /* natts */);
}
}
diff --git a/src/backend/executor/nodeForeignscan.c b/src/backend/executor/nodeForeignscan.c
index 6f0daddce07..21b3e9a961b 100644
--- a/src/backend/executor/nodeForeignscan.c
+++ b/src/backend/executor/nodeForeignscan.c
@@ -65,6 +65,8 @@ ForeignNext(ForeignScanState *node)
* Insert valid value into tableoid, the only actually-useful system
* column.
*/
+
+ slot->tts_remoteOid = slot->tts_tableOid;
if (plan->fsSystemCol && !TupIsNull(slot))
slot->tts_tableOid = RelationGetRelid(node->ss.ss_currentRelation);
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 9c040ecefc8..eb71d2bb54a 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1165,6 +1165,26 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
(Node *) rel->reltarget->exprs,
1, &appinfo);
+ /* Do it if fdw is partition */
+ if (planner_rt_fetch(childRTindex, root)->relkind == RELKIND_FOREIGN_TABLE &&
+ !bms_is_empty(root->glob->foreignParamIDs))
+ {
+ foreach(lc, root->processed_tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Param *param = (Param *) tle->expr;
+
+ if (tle->resjunk && IsA(param, Param) &&
+ IS_FOREIGN_PARAM(root, param) &&
+ param->target_rte == childRTindex) // TODO same for another case
+ {
+ /* XXX is copyObject necessary here? */
+ childrel->reltarget->exprs =
+ lappend(childrel->reltarget->exprs, copyObject(param));
+ }
+ }
+ }
+
/*
* We have to make child entries in the EquivalenceClass data
* structures as well. This is needed either if the parent
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 2c696ea0268..ca9226391a5 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -986,6 +986,25 @@ use_physical_tlist(PlannerInfo *root, Path *path, int flags)
}
}
+ /*
+ * Also, can't do it to a ForeignPath if the path is requested to emit
+ * Params generated by the FDW.
+ */
+ if (IsA(path, ForeignPath) &&
+ path->parent->relid == root->parse->resultRelation &&
+ !bms_is_empty(root->glob->foreignParamIDs))
+ {
+ foreach(lc, path->pathtarget->exprs)
+ {
+ Param *param = (Param *) lfirst(lc);
+ if (param && IsA(param, Param))
+ {
+ Assert(IS_FOREIGN_PARAM(root, param));
+ return false;
+ }
+ }
+ }
+
return true;
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index b38422c47a4..cc09065e561 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -32,6 +32,7 @@
#include "optimizer/planner.h"
#include "optimizer/restrictinfo.h"
#include "parser/analyze.h"
+#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
@@ -243,6 +244,40 @@ add_other_rels_to_query(PlannerInfo *root)
*
*****************************************************************************/
+/*
+ * add_params_to_result_rel
+ * If the query's final tlist contains Params the FDW generated, add
+ * targetlist entries for each such Param to the result relation.
+ */
+static void
+add_params_to_result_rel(PlannerInfo *root, List *final_tlist)
+{
+ RelOptInfo *target_rel = find_base_rel(root, root->parse->resultRelation);
+ ListCell *lc;
+
+ /*
+ * If no parameters have been generated by any FDWs, we certainly don't
+ * need to do anything here.
+ */
+ if (bms_is_empty(root->glob->foreignParamIDs))
+ return;
+
+ foreach(lc, final_tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Param *param = (Param *) tle->expr;
+
+ if (tle->resjunk && IsA(param, Param) &&
+ IS_FOREIGN_PARAM(root, param) &&
+ param->target_rte == target_rel->relid)
+ {
+ /* XXX is copyObject necessary here? */
+ target_rel->reltarget->exprs = lappend(target_rel->reltarget->exprs,
+ copyObject(param));
+ }
+ }
+}
+
/*
* build_base_rel_tlists
* Add targetlist entries for each var needed in the query's final tlist
@@ -282,6 +317,16 @@ build_base_rel_tlists(PlannerInfo *root, List *final_tlist)
list_free(having_vars);
}
}
+
+ if (root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE)
+ {
+ int result_relation = root->parse->resultRelation;
+
+ if (planner_rt_fetch(result_relation, root)->relkind == RELKIND_FOREIGN_TABLE)
+ add_params_to_result_rel(root, final_tlist);
+
+ }
}
/*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 3225185d16f..03439053355 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -388,6 +388,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
glob->dependsOnRole = false;
glob->partition_directory = NULL;
glob->rel_notnullatts_hash = NULL;
+ glob->foreignParamIDs = NULL;
/*
* Assess whether it's feasible to use parallel mode for this query. We
@@ -616,12 +617,15 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
}
/*
- * If any Params were generated, run through the plan tree and compute
- * each plan node's extParam/allParam sets. Ideally we'd merge this into
- * set_plan_references' tree traversal, but for now it has to be separate
- * because we need to visit subplans before not after main plan.
+ * If any Params were generated by the planner not by FDWs, run through
+ * the plan tree and compute each plan node's extParam/allParam sets.
+ * (Params added by FDWs are irrelevant for parameter change signaling.)
+ * Ideally we'd merge this into set_plan_references' tree traversal, but
+ * for now it has to be separate because we need to visit subplans before
+ * not after main plan.
*/
- if (glob->paramExecTypes != NIL)
+ if (glob->paramExecTypes != NIL &&
+ bms_num_members(glob->foreignParamIDs) < list_length(glob->paramExecTypes))
{
Assert(list_length(glob->subplans) == list_length(glob->subroots));
forboth(lp, glob->subplans, lr, glob->subroots)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index e1182255055..bf8f8fdab4c 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -3277,7 +3277,42 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
}
/* Special cases (apply only AFTER failing to match to lower tlist) */
if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+
+ /*
+ * If the Param is a PARAM_EXEC Param generated by an FDW, it should
+ * have bubbled up from a lower plan node; convert it into a simple
+ * Var referencing the output of the subplan.
+ *
+ * Note: set_join_references() would have kept has_non_vars=true for
+ * the subplan emitting the Param since it effectively belong to the
+ * result relation and that relation can never be the nullable side of
+ * an outer join.
+ */
+ if (IS_FOREIGN_PARAM(context->root, param))
+ {
+ if (context->outer_itlist && context->outer_itlist->has_non_vars)
+ {
+ newvar = search_indexed_tlist_for_non_var((Expr *) node,
+ context->outer_itlist,
+ OUTER_VAR);
+ if (newvar)
+ return (Node *) newvar;
+ }
+ if (context->inner_itlist && context->inner_itlist->has_non_vars)
+ {
+ newvar = search_indexed_tlist_for_non_var((Expr *) node,
+ context->inner_itlist,
+ INNER_VAR);
+ if (newvar)
+ return (Node *) newvar;
+ }
+ // XXX Is it an error to be here?
+ }
+ /* If not, do fix_param_node() */
return fix_param_node(context->root, (Param *) node);
+ }
if (IsA(node, AlternativeSubPlan))
return fix_join_expr_mutator(fix_alternative_subplan(context->root,
(AlternativeSubPlan *) node,
@@ -3388,7 +3423,28 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
}
/* Special cases (apply only AFTER failing to match to lower tlist) */
if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+ /*
+ * If the Param is a PARAM_EXEC Param generated by an FDW, it should
+ * have bubbled up from a lower plan node; convert it into a simple
+ * Var referencing the output of the subplan.
+ */
+ if (IS_FOREIGN_PARAM(context->root, param))
+ {
+ if (context->subplan_itlist->has_non_vars)
+ {
+ newvar = search_indexed_tlist_for_non_var((Expr *) node,
+ context->subplan_itlist,
+ context->newvarno);
+ if (newvar)
+ return (Node *) newvar;
+ }
+ // XXX Is it an error to be here?
+ }
+ /* If not, do fix_param_node() */
return fix_param_node(context->root, (Param *) node);
+ }
if (IsA(node, Aggref))
{
Aggref *aggref = (Aggref *) node;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 6aa8971c95d..67422914aa4 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -3194,7 +3194,12 @@ finalize_primnode(Node *node, finalize_primnode_context *context)
{
int paramid = ((Param *) node)->paramid;
- context->paramids = bms_add_member(context->paramids, paramid);
+ /*
+ * Params added by FDWs are irrelevant for parameter change
+ * signaling.
+ */
+ if (!bms_is_member(paramid, context->root->glob->foreignParamIDs))
+ context->paramids = bms_add_member(context->paramids, paramid);
}
return false; /* no more to do here */
}
diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c
index 778e4662f6e..7089a267125 100644
--- a/src/backend/optimizer/util/appendinfo.c
+++ b/src/backend/optimizer/util/appendinfo.c
@@ -948,6 +948,29 @@ add_row_identity_var(PlannerInfo *root, Var *orig_var,
root->processed_tlist = lappend(root->processed_tlist, tle);
}
+static void
+fix_foreign_params(PlannerInfo *root, List *tlist)
+{
+ ListCell *lc;
+
+ foreach(lc, tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Param *param = (Param *) tle->expr;
+
+ if (tle->resjunk && IsA(param, Param) &&
+ param->paramkind == PARAM_EXEC &&
+ param->paramid == -1)
+ {
+ param->paramid = list_length(root->glob->paramExecTypes);
+ root->glob->paramExecTypes =
+ lappend_oid(root->glob->paramExecTypes, param->paramtype);
+ root->glob->foreignParamIDs =
+ bms_add_member(root->glob->foreignParamIDs, param->paramid);
+ }
+ }
+}
+
/*
* add_row_identity_columns
*
@@ -992,8 +1015,12 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
fdwroutine = GetFdwRoutineForRelation(target_relation, false);
if (fdwroutine->AddForeignUpdateTargets != NULL)
+ {
+
fdwroutine->AddForeignUpdateTargets(root, rtindex,
target_rte, target_relation);
+ fix_foreign_params(root, root->processed_tlist);
+ }
/*
* For UPDATE, we need to make the FDW fetch unchanged columns by
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index 6a7b9edff3f..d8f08485e39 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -294,6 +294,7 @@ expand_inherited_rtentry(PlannerInfo *root, RelOptInfo *rel,
InvalidOid,
0);
snprintf(resname, sizeof(resname), "tableoid%u", oldrc->rowmarkId);
+
tle = makeTargetEntry((Expr *) var,
list_length(root->processed_tlist) + 1,
pstrdup(resname),
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 687e923c46c..51294420987 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -1320,6 +1320,27 @@ build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
}
continue;
}
+ /*
+ * We allow FDWs to have PARAM_EXEC Params here.
+ */
+ else if (IsA(var, Param))
+ {
+ Param *param = (Param *) var;
+
+ Assert(IS_FOREIGN_PARAM(root, param));
+
+ joinrel->reltarget->exprs =
+ lappend(joinrel->reltarget->exprs, param);
+
+ /*
+ * Estimate using the type info (Note: keep this in sync with
+ * set_rel_width())
+ */
+ joinrel->reltarget->width +=
+ get_typavgwidth(param->paramtype, param->paramtypmod);
+
+ continue;
+ }
/*
* Otherwise, anything in a baserel or joinrel targetlist ought to be
diff --git a/src/backend/optimizer/util/tlist.c b/src/backend/optimizer/util/tlist.c
index 752ea9222f6..e382810b19f 100644
--- a/src/backend/optimizer/util/tlist.c
+++ b/src/backend/optimizer/util/tlist.c
@@ -329,18 +329,37 @@ apply_tlist_labeling(List *dest_tlist, List *src_tlist)
ListCell *ld,
*ls;
- Assert(list_length(dest_tlist) == list_length(src_tlist));
- forboth(ld, dest_tlist, ls, src_tlist)
+ foreach(ld, dest_tlist)
{
+ bool has_src = false;
TargetEntry *dest_tle = (TargetEntry *) lfirst(ld);
- TargetEntry *src_tle = (TargetEntry *) lfirst(ls);
-
- Assert(dest_tle->resno == src_tle->resno);
- dest_tle->resname = src_tle->resname;
- dest_tle->ressortgroupref = src_tle->ressortgroupref;
- dest_tle->resorigtbl = src_tle->resorigtbl;
- dest_tle->resorigcol = src_tle->resorigcol;
- dest_tle->resjunk = src_tle->resjunk;
+
+ foreach(ls, src_tlist)
+ {
+ TargetEntry *src_tle = (TargetEntry *) lfirst(ls);
+
+ if (dest_tle->resno == src_tle->resno)
+ {
+ has_src = true;
+ dest_tle->resname = src_tle->resname;
+ dest_tle->ressortgroupref = src_tle->ressortgroupref;
+ dest_tle->resorigtbl = src_tle->resorigtbl;
+ dest_tle->resorigcol = src_tle->resorigcol;
+ dest_tle->resjunk = src_tle->resjunk;
+ break;
+ }
+ }
+
+ if (!has_src)
+ {
+ TargetEntry *new_entry = palloc0(sizeof(TargetEntry));
+ new_entry->resname = dest_tle->resname;
+ new_entry->ressortgroupref = dest_tle->ressortgroupref;
+ new_entry->resorigtbl = dest_tle->resorigtbl;
+ new_entry->resorigcol = dest_tle->resorigcol;
+ new_entry->resjunk = dest_tle->resjunk;
+ src_tlist = lappend(src_tlist, new_entry);
+ }
}
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b3aef797e7d..302bf7d2ff5 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9376,7 +9376,7 @@ get_parameter(Param *param, deparse_context *context)
* It's a bug if we get here for anything except PARAM_EXTERN Params, but
* in production builds printing $N seems more useful than failing.
*/
- Assert(param->paramkind == PARAM_EXTERN);
+ Assert(param->paramkind == PARAM_EXTERN || param->paramkind == PARAM_EXEC);
appendStringInfo(context->buf, "$%d", param->paramid);
}
diff --git a/src/include/access/htup_details.h b/src/include/access/htup_details.h
index 77a6c48fd71..1f59f363a68 100644
--- a/src/include/access/htup_details.h
+++ b/src/include/access/htup_details.h
@@ -794,10 +794,17 @@ HeapTupleClearHeapOnly(const HeapTupleData *tuple)
/* prototypes for functions in common/heaptuple.c */
extern Size heap_compute_data_size(TupleDesc tupleDesc,
const Datum *values, const bool *isnull);
+extern Size heap_compute_data_size_ext(TupleDesc tupleDesc,
+ const Datum *values, const bool *isnull,
+ int natts);
extern void heap_fill_tuple(TupleDesc tupleDesc,
const Datum *values, const bool *isnull,
char *data, Size data_size,
uint16 *infomask, uint8 *bit);
+extern void heap_fill_tuple_ext(TupleDesc tupleDesc,
+ const Datum *values, const bool *isnull,
+ char *data, Size data_size,
+ uint16 *infomask, uint8 *bit, int natts);
extern bool heap_attisnull(HeapTuple tup, int attnum, TupleDesc tupleDesc);
extern Datum nocachegetattr(HeapTuple tup, int attnum,
TupleDesc tupleDesc);
@@ -806,10 +813,20 @@ extern Datum heap_getsysattr(HeapTuple tup, int attnum, TupleDesc tupleDesc,
extern Datum getmissingattr(TupleDesc tupleDesc,
int attnum, bool *isnull);
extern HeapTuple heap_copytuple(HeapTuple tuple);
+
+struct TupleTableSlot;
+extern HeapTuple heap_copytuple_ext(HeapTuple tuple,
+ struct TupleTableSlot *slot,
+ int dstnatts);
+
extern void heap_copytuple_with_tuple(HeapTuple src, HeapTuple dest);
extern Datum heap_copy_tuple_as_datum(HeapTuple tuple, TupleDesc tupleDesc);
extern HeapTuple heap_form_tuple(TupleDesc tupleDescriptor,
const Datum *values, const bool *isnull);
+extern HeapTuple heap_form_tuple_ext(TupleDesc tupleDescriptor,
+ const Datum *values,
+ const bool *isnull,
+ int natts);
extern HeapTuple heap_modify_tuple(HeapTuple tuple,
TupleDesc tupleDesc,
const Datum *replValues,
@@ -827,6 +844,11 @@ extern void heap_freetuple(HeapTuple htup);
extern MinimalTuple heap_form_minimal_tuple(TupleDesc tupleDescriptor,
const Datum *values, const bool *isnull,
Size extra);
+extern MinimalTuple heap_form_minimal_tuple_ext(TupleDesc tupleDescriptor,
+ const Datum *values,
+ const bool *isnull,
+ Size extra,
+ int natts);
extern void heap_free_minimal_tuple(MinimalTuple mtup);
extern MinimalTuple heap_copy_minimal_tuple(MinimalTuple mtup, Size extra);
extern HeapTuple heap_tuple_from_minimal_tuple(MinimalTuple mtup);
diff --git a/src/include/executor/tuptable.h b/src/include/executor/tuptable.h
index 3db6c9c9bd0..02ddc4fb79d 100644
--- a/src/include/executor/tuptable.h
+++ b/src/include/executor/tuptable.h
@@ -141,6 +141,7 @@ typedef struct TupleTableSlot
MemoryContext tts_mcxt; /* slot itself is in this context */
ItemPointerData tts_tid; /* stored tuple's tid */
Oid tts_tableOid; /* table oid of tuple */
+ Oid tts_remoteOid;
} TupleTableSlot;
/* routines for a TupleTableSlot implementation */
@@ -223,8 +224,10 @@ struct TupleTableSlotOps
* meaningful "system columns" in the copy. The copy is not be "owned" by
* the slot i.e. the caller has to take responsibility to free memory
* consumed by the slot.
+ *
+ * TODO comment for "natts"
*/
- HeapTuple (*copy_heap_tuple) (TupleTableSlot *slot);
+ HeapTuple (*copy_heap_tuple) (TupleTableSlot *slot, int natts);
/*
* Return a copy of minimal tuple representing the contents of the slot.
@@ -237,8 +240,10 @@ struct TupleTableSlotOps
* The copy has "extra" bytes (maxaligned and zeroed) available before the
* tuple, which is useful so that some callers may store extra data along
* with the minimal tuple without the need for an additional allocation.
+ *
+ * TODO comment for "natts"
*/
- MinimalTuple (*copy_minimal_tuple) (TupleTableSlot *slot, Size extra);
+ MinimalTuple (*copy_minimal_tuple) (TupleTableSlot *slot, Size extra, int natts);
};
/*
@@ -368,6 +373,12 @@ extern void slot_getsomeattrs_int(TupleTableSlot *slot, int attnum);
#ifndef FRONTEND
+/* Forward declarations */
+static inline HeapTuple ExecCopySlotHeapTupleExteded(TupleTableSlot *slot,
+ int natts);
+static inline MinimalTuple ExecCopySlotMinimalTupleExteded(TupleTableSlot *slot,
+ int natts);
+
/*
* This function forces the entries of the slot's Datum/isnull arrays to be
* valid at least up through the attnum'th entry.
@@ -502,10 +513,16 @@ ExecMaterializeSlot(TupleTableSlot *slot)
*/
static inline HeapTuple
ExecCopySlotHeapTuple(TupleTableSlot *slot)
+{
+ return ExecCopySlotHeapTupleExteded(slot, -1 /* natts */);
+}
+
+static inline HeapTuple
+ExecCopySlotHeapTupleExteded(TupleTableSlot *slot, int natts)
{
Assert(!TTS_EMPTY(slot));
- return slot->tts_ops->copy_heap_tuple(slot);
+ return slot->tts_ops->copy_heap_tuple(slot, natts);
}
/*
@@ -514,7 +531,13 @@ ExecCopySlotHeapTuple(TupleTableSlot *slot)
static inline MinimalTuple
ExecCopySlotMinimalTuple(TupleTableSlot *slot)
{
- return slot->tts_ops->copy_minimal_tuple(slot, 0);
+ return ExecCopySlotMinimalTupleExteded(slot, -1 /* natts */);
+}
+
+static inline MinimalTuple
+ExecCopySlotMinimalTupleExteded(TupleTableSlot *slot, int natts)
+{
+ return slot->tts_ops->copy_minimal_tuple(slot, 0 /* no extra */, natts);
}
/*
@@ -524,9 +547,9 @@ ExecCopySlotMinimalTuple(TupleTableSlot *slot)
* caller to make an additional allocation).
*/
static inline MinimalTuple
-ExecCopySlotMinimalTupleExtra(TupleTableSlot *slot, Size extra)
+ExecCopySlotMinimalTupleExtra(TupleTableSlot *slot, Size extra, int natts)
{
- return slot->tts_ops->copy_minimal_tuple(slot, extra);
+ return slot->tts_ops->copy_minimal_tuple(slot, extra, natts);
}
/*
@@ -545,9 +568,12 @@ ExecCopySlot(TupleTableSlot *dstslot, TupleTableSlot *srcslot)
{
Assert(!TTS_EMPTY(srcslot));
Assert(srcslot != dstslot);
- Assert(dstslot->tts_tupleDescriptor->natts ==
- srcslot->tts_tupleDescriptor->natts);
+ /*
+ * If source slot has less attributes then target - copy source and
+ * set target to nulls. Otherwise copy only leading attributes and set
+ * target natts to source counter.
+ */
dstslot->tts_ops->copyslot(dstslot, srcslot);
return dstslot;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 27a2c6815b7..abe4904cc24 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -271,12 +271,19 @@ typedef struct PlannerGlobal
/* extension state */
void **extension_state pg_node_attr(read_write_ignore);
int extension_state_allocated;
+
+ /* PARAM_EXEC Params generated by FDWs */
+ Bitmapset *foreignParamIDs;
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
#define planner_subplan_get_plan(root, subplan) \
((Plan *) list_nth((root)->glob->subplans, (subplan)->plan_id - 1))
+/* macro for checking if a Param is a PARAM_EXEC Param generated by an FDW */
+#define IS_FOREIGN_PARAM(root, param) \
+ ((param)->paramkind == PARAM_EXEC && \
+ bms_is_member((param)->paramid, (root)->glob->foreignParamIDs))
/*----------
* PlannerInfo
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index cacef7d4151..e0433cd1f5e 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -402,6 +402,7 @@ typedef struct Param
Oid paramcollid;
/* token location, or -1 if unknown */
ParseLoc location;
+ Index target_rte;
} Param;
/*
--
2.43.0
[application/octet-stream] v4-0002-teach-fdw-use-remote-tableoid.patch (95.5K, ../../CAN-LCVMboJszNufW+4NRZh4M6c-u74RM4Y7bfjy_8PVmQVktJg@mail.gmail.com/4-v4-0002-teach-fdw-use-remote-tableoid.patch)
download | inline diff:
From df2312765d9c99e675ba69c17640ff1adaee3189 Mon Sep 17 00:00:00 2001
From: Nikita Malakhov <[email protected]>
Date: Thu, 9 Jul 2026 16:51:27 +0300
Subject: [PATCH] DELETE/UPDATE more than one row in partitioned foreign table
(2/2)
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 | 518 ++++++++++--------
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, 717 insertions(+), 253 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 5ebae1cedc2..fd3baf8f742 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;
@@ -6436,27 +6436,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
@@ -6534,13 +6534,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
@@ -6562,20 +6562,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
@@ -6595,19 +6595,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)
@@ -6638,13 +6638,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)
@@ -6676,24 +6676,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"
@@ -7205,19 +7205,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.*;
@@ -7274,17 +7274,17 @@ prepare fdw_part_upd2(int) as
returning tableoid::regclass, a, b;
explain (verbose, costs off)
execute fdw_part_upd2(2);
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.fdw_part_update
Output: (fdw_part_update_1.tableoid)::regclass, fdw_part_update_1.a, fdw_part_update_1.b
Foreign Update on public.fdw_part_update_p2 fdw_part_update_2
- Remote SQL: UPDATE public.fdw_part_update_remote SET b = $2 WHERE ctid = $1 RETURNING a, b
+ Remote SQL: UPDATE public.fdw_part_update_remote SET b = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING a, b
-> Append
Subplans Removed: 1
-> Foreign Scan on public.fdw_part_update_p2 fdw_part_update_2
- Output: ((fdw_part_update_2.b + ((random())::integer * 0)) + 1), fdw_part_update_2.tableoid, fdw_part_update_2.ctid, fdw_part_update_2.*
- Remote SQL: SELECT a, b, ctid FROM public.fdw_part_update_remote WHERE ((a = $1::integer)) FOR UPDATE
+ Output: ((fdw_part_update_2.b + ((random())::integer * 0)) + 1), fdw_part_update_2.tableoid, fdw_part_update_2.ctid, fdw_part_update_2.tableoid, $0, fdw_part_update_2.*
+ Remote SQL: SELECT a, b, ctid, tableoid FROM public.fdw_part_update_remote WHERE ((a = $1::integer)) FOR UPDATE
(9 rows)
execute fdw_part_upd2(2);
@@ -7442,13 +7442,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
@@ -7456,13 +7456,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
@@ -7555,14 +7555,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
@@ -7570,14 +7570,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
@@ -7626,14 +7626,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;
@@ -7712,13 +7712,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;
@@ -8045,13 +8045,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;
@@ -8193,12 +8193,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)
@@ -8299,13 +8299,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)
@@ -8323,13 +8323,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)
@@ -8357,13 +8357,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;
@@ -8381,13 +8381,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;
@@ -8424,28 +8424,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;
@@ -8463,39 +8463,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
@@ -8837,22 +8839,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
@@ -8884,24 +8886,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
@@ -9042,19 +9044,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
@@ -9068,17 +9072,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)
@@ -9103,20 +9107,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;
@@ -9134,20 +9138,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
@@ -9178,22 +9184,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
@@ -9210,22 +9216,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
@@ -9455,7 +9461,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)
@@ -9494,8 +9500,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
@@ -9504,7 +9510,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 *;
@@ -9515,22 +9521,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*"
@@ -9554,15 +9560,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)
@@ -9572,22 +9578,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*"
@@ -12474,8 +12480,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
@@ -12487,7 +12493,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)
@@ -12514,7 +12520,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)
@@ -13129,6 +13135,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 0ea72a64ce5..e5919eb361c 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"
@@ -82,6 +83,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,
@@ -180,6 +183,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;
/*
@@ -206,6 +213,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 */
@@ -306,6 +314,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.
*/
@@ -506,6 +543,86 @@ 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_lookup(remoteMap->oid_mapping, local_oid);
+ if (entry)
+ oidmapping_delete(remoteMap->oid_mapping, local_oid);
+ else
+ remoteMap->refcount++;
+
+ entry = oidmapping_insert(remoteMap->oid_mapping, local_oid, &found);
+ 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
*/
@@ -660,6 +777,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,
@@ -1007,6 +1125,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;
+ }
+ }
}
/*
@@ -1468,6 +1603,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.
@@ -1632,12 +1768,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,
@@ -1770,6 +1910,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));
@@ -1789,11 +1931,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);
@@ -1825,6 +1969,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
@@ -1851,12 +1996,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;
}
@@ -1971,6 +2127,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.
@@ -1986,6 +2145,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);
}
/*
@@ -2348,6 +2539,7 @@ postgresExecForeignDelete(EState *estate,
rslot = execute_foreign_modify(estate, resultRelInfo, CMD_DELETE,
&slot, &planSlot, &numSlots);
+ destroy_remote_map();
return rslot ? rslot[0] : NULL;
}
@@ -4233,7 +4425,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;
@@ -4251,6 +4443,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)
@@ -4303,6 +4509,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;
@@ -4348,7 +4555,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,
@@ -4357,10 +4566,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.
@@ -4465,6 +4692,7 @@ prepare_foreign_modify(PgFdwModifyState *fmstate)
static const char **
convert_prep_stmt_params(PgFdwModifyState *fmstate,
ItemPointer tupleid,
+ Oid tableoid,
TupleTableSlot **slots,
int numSlots)
{
@@ -4491,6 +4719,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)
{
@@ -4502,7 +4740,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);
@@ -4567,9 +4805,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;
@@ -4675,7 +4914,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))
@@ -4881,6 +5121,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));
/*
@@ -4899,6 +5150,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;
@@ -4990,10 +5243,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;
}
/*
@@ -6937,6 +7193,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)
{
@@ -8470,6 +8758,7 @@ make_tuple_from_result_row(PGresult *res,
ErrorContextCallback errcallback;
MemoryContext oldcontext;
ListCell *lc;
+ Oid tableoid = InvalidOid;
int j;
Assert(row < PQntuples(res));
@@ -8552,6 +8841,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++;
@@ -8583,6 +8883,8 @@ make_tuple_from_result_row(PGresult *res,
if (ctid)
tuple->t_self = tuple->t_data->t_ctid = *ctid;
+ 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 e868da00ace..7cb8ed7c337 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4702,6 +4702,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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2026-07-09 14:03 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-01 01:35 [PATCH 4/4] Implement vacuum full/cluster (INDEX_TABLESPACE <tablespace>) Justin Pryzby <[email protected]>
2026-06-01 10:44 Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-05 11:59 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-06 20:33 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
2026-06-08 12:45 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 05:37 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
2026-06-10 11:22 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 22:25 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
2026-06-10 11:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-06-10 22:30 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Michael Paquier <[email protected]>
2026-06-13 19:43 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
2026-06-15 14:54 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Etsuro Fujita <[email protected]>
2026-07-09 14:03 ` Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table Nikita Malakhov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox