Proposal to allow DELETE/UPDATE on partitioned tables with unsupported foreign partitions

From b376d1e2a442066b4cae4b939fc4e7a212ae644b Mon Sep 17 00:00:00 2001
From: Shirisha SN <sshirisha@vmware.com>
Date: Fri, 6 Jun 2025 22:27:40 +0530
Subject: [PATCH v2] Allow DELETE/UPDATE on tables with foreign partitions or
 inherited foreign tables

Currently, DELETE or UPDATE on a partitioned table with foreign partitions
fail with an error as below, if the FDW does not support the operation:

	`ERROR: cannot delete from foreign table`

This occurs because during executor initialization (ExecInitModifyTable),
PostgreSQL scans all partitions of the target table and checks whether each one
supports the requested operation. If any foreign partition's FDW lacks support
for DELETE or UPDATE, the operation is rejected outright, even if that
partition would not be affected by the query.

A similar issue arises with inherited foreign tables, where DELETE/UPDATEs
targeted on non-foreign tables are also blocked.

As a result, DELETE/UPDATE operations are blocked even when they only target
non-foreign relations. This means the system errors out without considering
whether foreign or inherited foreign tables are actually involved in the
operation. Even if no matching rows exist in a foreign partition, the operation
still fails.

This commit defers the FDW check for foreign partitions and inherited foreign tables from
`ExecInitModifyTable` to `ExecDelete` and `ExecUpdate`. This change ensures
that foreign child tables are checked only when they are actually targeted by the
operation.

However, if a DELETE or UPDATE is issued on the root table and it includes
foreign child tables that do not support the operation, it will still result in
an error. This is intentional because the responsibility for managing data in
foreign tables lies with the user. Only after the user has removed relevant
data from those foreign tables will such operations on the root table
succeed.

** Mini repro for partition table with foreign partition: **
```
CREATE EXTENSION file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE TABLE pt (a int, b numeric) PARTITION BY RANGE(a);
CREATE TABLE pt_part1 PARTITION OF pt FOR VALUES FROM (0) TO (10);
INSERT INTO pt SELECT 5, 0.1;
INSERT INTO pt SELECT 6, 0.2;

CREATE FOREIGN TABLE ext (a int, b numeric) SERVER file_server OPTIONS (filename 'path-to-file', format 'csv', delimiter ',');
ALTER TABLE pt ATTACH PARTITION ext FOR VALUES FROM (10) TO (20);
postgres=# SELECT * FROM pt;
 a  |  b
----+-----
  5 | 0.1
  6 | 0.2
 15 | 0.3
 21 | 0.4
(4 rows)
```

** Before Fix: **
```
postgres=# DELETE FROM pt WHERE b = 0.2;
ERROR:  cannot delete from foreign table "ext"
postgres=# DELETE FROM pt;
ERROR:  cannot delete from foreign table "ext"

postgres=# UPDATE pt set b = 0.5 WHERE b = 0.1;
ERROR:  cannot update foreign table "ext"
postgres=# UPDATE pt SET b = 0.5;
ERROR:  cannot update foreign table "ext"
```

** After Fix: **
```
postgres=# DELETE FROM pt WHERE b = 0.2;
DELETE 1
postgres=# DELETE FROM pt;
ERROR:  cannot delete from foreign table "ext"

postgres=# UPDATE pt SET b = 0.5 WHERE b = 0.1;
UPDATE 1
postgres=# UPDATE pt SET b = 0.5;
ERROR:  cannot update foreign table "ext"
```

** Mini repro for table with inherited foreign table: **
```
CREATE TABLE pt (a text, b int);
INSERT INTO pt VALUES ('AAA', 42);
CREATE FOREIGN TABLE ft (a text, b int) server file_server OPTIONS (filename 'path-to-file', format 'csv', delimiter ',');
ALTER FOREIGN TABLE ft INHERIT pt;
SELECT * FROM pt;
  a  | b
-----+----
 AAA | 42
 BBB | 42
(2 rows)
```

** Before Fix: **
```
UPDATE pt SET b = b + 1000 WHERE a = 'AAA';
ERROR:  cannot update foreign table "ft"
DELETE FROM pt WHERE a = 'AAA';
ERROR:  cannot delete from foreign table "ft"
```

** After Fix: **
```
UPDATE pt SET b = b + 1000 WHERE a = 'AAA';
UPDATE 1
DELETE FROM pt WHERE a = 'AAA';
DELETE 1
```

Co-authored-by: Ashwin Agrawal <ashwin.agrawal@broadcom.com>
---
 contrib/file_fdw/expected/file_fdw.out | 105 ++++++++++++++++++++++---
 contrib/file_fdw/sql/file_fdw.sql      |  42 +++++++++-
 src/backend/executor/nodeModifyTable.c |  67 +++++++++++++++-
 3 files changed, 199 insertions(+), 15 deletions(-)

diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 246e3d3e566..157b2851648 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -333,14 +333,18 @@ SELECT * FROM agg_csv WHERE a < 0;
 RESET constraint_exclusion;
 -- table inheritance tests
 CREATE TABLE agg (a int2, b float4);
+INSERT INTO agg SELECT 5,0.1;
+INSERT INTO agg SELECT 6,0.2;
 ALTER FOREIGN TABLE agg_csv INHERIT agg;
 SELECT tableoid::regclass, * FROM agg;
  tableoid |  a  |    b    
 ----------+-----+---------
+ agg      |   5 |     0.1
+ agg      |   6 |     0.2
  agg_csv  | 100 |  99.097
  agg_csv  |   0 | 0.09561
  agg_csv  |  42 |  324.78
-(3 rows)
+(5 rows)
 
 SELECT tableoid::regclass, * FROM agg_csv;
  tableoid |  a  |    b    
@@ -351,16 +355,51 @@ SELECT tableoid::regclass, * FROM agg_csv;
 (3 rows)
 
 SELECT tableoid::regclass, * FROM ONLY agg;
- tableoid | a | b 
-----------+---+---
-(0 rows)
+ tableoid | a |  b  
+----------+---+-----
+ agg      | 5 | 0.1
+ agg      | 6 | 0.2
+(2 rows)
 
--- updates aren't supported
-UPDATE agg SET a = 1;
+-- updates on foreign tables are not supported
+UPDATE agg SET a = 10 WHERE b = 99.097::float4;
+ERROR:  cannot update foreign table "agg_csv"
+UPDATE agg SET a = 10 WHERE a = 100;
+ERROR:  cannot update foreign table "agg_csv"
+UPDATE agg SET a = 10;
 ERROR:  cannot update foreign table "agg_csv"
+-- these updates should be allowed
+UPDATE agg SET a = 10 WHERE b = 0.1::float4;
+UPDATE agg SET a = 20 WHERE a = 10;
+SELECT tableoid::regclass, * FROM agg;
+ tableoid |  a  |    b    
+----------+-----+---------
+ agg      |   6 |     0.2
+ agg      |  20 |     0.1
+ agg_csv  | 100 |  99.097
+ agg_csv  |   0 | 0.09561
+ agg_csv  |  42 |  324.78
+(5 rows)
+
+-- deletes on foreign tables are not supported
+DELETE from agg WHERE b = 99.097::float4;
+ERROR:  cannot delete from foreign table "agg_csv"
 DELETE FROM agg WHERE a = 100;
 ERROR:  cannot delete from foreign table "agg_csv"
--- but this should be allowed
+DELETE FROM agg;
+ERROR:  cannot delete from foreign table "agg_csv"
+-- these deletes should be allowed
+DELETE FROM agg WHERE b = 0.1::float4;
+DELETE FROM agg WHERE a = 6;
+SELECT tableoid::regclass, * FROM agg;
+ tableoid |  a  |    b    
+----------+-----+---------
+ agg_csv  | 100 |  99.097
+ agg_csv  |   0 | 0.09561
+ agg_csv  |  42 |  324.78
+(3 rows)
+
+-- this should be allowed
 SELECT tableoid::regclass, * FROM agg FOR UPDATE;
  tableoid |  a  |    b    
 ----------+-----+---------
@@ -539,15 +578,63 @@ ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
 ERROR:  permission denied to set the "filename" option of a file_fdw foreign table
 DETAIL:  Only roles with privileges of the "pg_read_server_files" role may set this option.
 SET ROLE regress_file_fdw_superuser;
+-- Test UPDATE/DELETE on partition table with foreign partitions
+CREATE TABLE pt_root (a int2, b float4) PARTITION BY range (a);
+CREATE TABLE pt_child PARTITION OF pt_root FOR VALUES FROM (0) TO (10);
+INSERT INTO pt_root SELECT 5, 0.1;
+INSERT INTO pt_root SELECT 6, 0.2;
+ALTER TABLE pt_root ATTACH PARTITION agg_csv FOR VALUES FROM (10) TO (20);
+SELECT * FROM pt_root;
+  a  |    b    
+-----+---------
+   5 |     0.1
+   6 |     0.2
+ 100 |  99.097
+   0 | 0.09561
+  42 |  324.78
+(5 rows)
+
+-- delete on foreign tables are not supported
+DELETE FROM pt_root WHERE b = 99.097::float4;
+ERROR:  cannot delete from foreign table "agg_csv"
+DELETE FROM pt_root;
+ERROR:  cannot delete from foreign table "agg_csv"
+-- this delete should be allowed
+DELETE FROM pt_root WHERE b = 0.1::float4;
+SELECT * FROM pt_root;
+  a  |    b    
+-----+---------
+   6 |     0.2
+ 100 |  99.097
+   0 | 0.09561
+  42 |  324.78
+(4 rows)
+
+-- updates on foreign tables are not supported
+UPDATE pt_root SET b = 0.10 WHERE b = 99.097::float4;
+ERROR:  cannot update foreign table "agg_csv"
+UPDATE pt_root SET b = 0.10;
+ERROR:  cannot update foreign table "agg_csv"
+-- this update should be allowed
+UPDATE pt_root SET b = 0.6 WHERE b = 0.2::float4;
+SELECT * FROM pt_root;
+  a  |    b    
+-----+---------
+   6 |     0.6
+ 100 |  99.097
+   0 | 0.09561
+  42 |  324.78
+(4 rows)
+
+DROP TABLE pt_root;
 -- cleanup
 RESET ROLE;
 DROP EXTENSION file_fdw CASCADE;
-NOTICE:  drop cascades to 9 other objects
+NOTICE:  drop cascades to 8 other objects
 DETAIL:  drop cascades to server file_server
 drop cascades to user mapping for regress_file_fdw_superuser on server file_server
 drop cascades to user mapping for regress_no_priv_user on server file_server
 drop cascades to foreign table agg_text
-drop cascades to foreign table agg_csv
 drop cascades to foreign table agg_bad
 drop cascades to foreign table header_match
 drop cascades to foreign table header_doesnt_match
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 1a397ad4bd1..dd6f0cd818c 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -207,14 +207,29 @@ RESET constraint_exclusion;
 
 -- table inheritance tests
 CREATE TABLE agg (a int2, b float4);
+INSERT INTO agg SELECT 5,0.1;
+INSERT INTO agg SELECT 6,0.2;
 ALTER FOREIGN TABLE agg_csv INHERIT agg;
 SELECT tableoid::regclass, * FROM agg;
 SELECT tableoid::regclass, * FROM agg_csv;
 SELECT tableoid::regclass, * FROM ONLY agg;
--- updates aren't supported
-UPDATE agg SET a = 1;
+-- updates on foreign tables are not supported
+UPDATE agg SET a = 10 WHERE b = 99.097::float4;
+UPDATE agg SET a = 10 WHERE a = 100;
+UPDATE agg SET a = 10;
+-- these updates should be allowed
+UPDATE agg SET a = 10 WHERE b = 0.1::float4;
+UPDATE agg SET a = 20 WHERE a = 10;
+SELECT tableoid::regclass, * FROM agg;
+-- deletes on foreign tables are not supported
+DELETE from agg WHERE b = 99.097::float4;
 DELETE FROM agg WHERE a = 100;
--- but this should be allowed
+DELETE FROM agg;
+-- these deletes should be allowed
+DELETE FROM agg WHERE b = 0.1::float4;
+DELETE FROM agg WHERE a = 6;
+SELECT tableoid::regclass, * FROM agg;
+-- this should be allowed
 SELECT tableoid::regclass, * FROM agg FOR UPDATE;
 ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
 DROP TABLE agg;
@@ -285,6 +300,27 @@ SET ROLE regress_file_fdw_user;
 ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
 SET ROLE regress_file_fdw_superuser;
 
+-- Test UPDATE/DELETE on partition table with foreign partitions
+CREATE TABLE pt_root (a int2, b float4) PARTITION BY range (a);
+CREATE TABLE pt_child PARTITION OF pt_root FOR VALUES FROM (0) TO (10);
+INSERT INTO pt_root SELECT 5, 0.1;
+INSERT INTO pt_root SELECT 6, 0.2;
+ALTER TABLE pt_root ATTACH PARTITION agg_csv FOR VALUES FROM (10) TO (20);
+SELECT * FROM pt_root;
+-- delete on foreign tables are not supported
+DELETE FROM pt_root WHERE b = 99.097::float4;
+DELETE FROM pt_root;
+-- this delete should be allowed
+DELETE FROM pt_root WHERE b = 0.1::float4;
+SELECT * FROM pt_root;
+-- updates on foreign tables are not supported
+UPDATE pt_root SET b = 0.10 WHERE b = 99.097::float4;
+UPDATE pt_root SET b = 0.10;
+-- this update should be allowed
+UPDATE pt_root SET b = 0.6 WHERE b = 0.2::float4;
+SELECT * FROM pt_root;
+
+DROP TABLE pt_root;
 -- cleanup
 RESET ROLE;
 DROP EXTENSION file_fdw CASCADE;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4c5647ac38a..26d7938488a 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1585,10 +1585,34 @@ ExecDelete(ModifyTableContext *context,
 	TupleTableSlot *slot = NULL;
 	TM_Result	result;
 	bool		saveOld;
+	FdwRoutine *fdwroutine;
 
 	if (tupleDeleted)
 		*tupleDeleted = false;
 
+	/*
+	 * For foreign partitions and inherited foreign tables, raise error
+	 * during DELETE if the FDW does not support it. This check is deferred
+	 * from ExecInitModifyTable to allow deletes on non-foreign tables to
+	 * proceed without error.
+	 */
+	if (resultRelationDesc->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+	{
+		fdwroutine = resultRelInfo->ri_FdwRoutine;
+		if (fdwroutine->ExecForeignDelete == NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("cannot delete from foreign table \"%s\"",
+							RelationGetRelationName(resultRelationDesc))));
+
+		if (fdwroutine->IsForeignRelUpdatable != NULL &&
+			(fdwroutine->IsForeignRelUpdatable(resultRelationDesc) & (1 << CMD_DELETE)) == 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+					errmsg("foreign table \"%s\" does not allow deletes",
+							RelationGetRelationName(resultRelationDesc))));
+	}
+
 	/*
 	 * Prepare for the delete.  This includes BEFORE ROW triggers, so we're
 	 * done if it says we are.
@@ -2466,6 +2490,7 @@ ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
 	Relation	resultRelationDesc = resultRelInfo->ri_RelationDesc;
 	UpdateContext updateCxt = {0};
 	TM_Result	result;
+	FdwRoutine *fdwroutine;
 
 	/*
 	 * abort the operation if not running transactions
@@ -2480,6 +2505,29 @@ ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
 	if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot, NULL))
 		return NULL;
 
+	/*
+	 * For foreign partitions and inherited foreign tables, raise error
+	 * during UPDATE if the FDW does not support it. This check is deferred
+	 * from ExecInitModifyTable to allow updates on non-foreign tables to
+	 * proceed without error.
+	 */
+	if (resultRelationDesc->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+	{
+		fdwroutine = resultRelInfo->ri_FdwRoutine;
+		if (fdwroutine->ExecForeignUpdate == NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("cannot update foreign table \"%s\"",
+							RelationGetRelationName(resultRelationDesc))));
+
+		if (fdwroutine->IsForeignRelUpdatable != NULL &&
+			(fdwroutine->IsForeignRelUpdatable(resultRelationDesc) & (1 << CMD_UPDATE)) == 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+					errmsg("foreign table \"%s\" does not allow updates",
+							RelationGetRelationName(resultRelationDesc))));
+	}
+
 	/* INSTEAD OF ROW UPDATE Triggers */
 	if (resultRelInfo->ri_TrigDesc &&
 		resultRelInfo->ri_TrigDesc->trig_update_instead_row)
@@ -4786,6 +4834,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	i = 0;
 	foreach(l, resultRelations)
 	{
+		bool 		skip_rel_check = false;
 		Index		resultRelation = lfirst_int(l);
 		List	   *mergeActions = NIL;
 
@@ -4810,10 +4859,22 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 			bms_is_member(i, node->fdwDirectModifyPlans);
 
 		/*
-		 * Verify result relation is a valid target for the current operation
+		 * Verify result relation is a valid target for the current operation.
+		 * Skip this verification only when:
+		 * - the relation is a foreign table,
+		 * - the operation is DELETE or UPDATE, and
+		 * - the query involves multiple result relations
+		 *
+		 * In such cases, the validation is deferred to ExecDelete or
+		 * ExecUpdate, where the specific foreign partition is processed.
 		 */
-		CheckValidResultRel(resultRelInfo, operation, node->onConflictAction,
-							mergeActions);
+		rel = resultRelInfo->ri_RelationDesc;
+		skip_rel_check = (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE &&
+							(operation == CMD_DELETE || operation == CMD_UPDATE) &&
+							nrels > 1);
+		if (!skip_rel_check)
+			CheckValidResultRel(resultRelInfo, operation, node->onConflictAction,
+								mergeActions);
 
 		resultRelInfo++;
 		i++;
-- 
2.39.5 (Apple Git-154)