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

Hello Hackers,

We’d like to propose a change to improve DELETE and UPDATE behavior on
partitioned tables
containing foreign partitions.

Currently, DELETE or UPDATE (D/U) on a partitioned table with foreign
partitions fails with an error
as below, if the FDW does not support the operation:

`ERROR: cannot delete from foreign table`

This failure occurs during executor initialization (`ExecInitModifyTable`),
where 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 D/U, the operation is
rejected outright, even if that
partition would not be affected by the query.

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

This behavior presents a usability hurdle as it forces the user to work
around this limitation by issuing D/U
statements separately on each individual child partition. This is
cumbersome and breaks the workflow of managing such tables via the root.

We are proposing a patch that would allow users to have a better workflow
by continuing to perform D/U via root partition
even in presence of foreign partitions not implementing D/U API.
*The key change is to defer the FDW check for foreign partitions from
`ExecInitModifyTable` to `ExecDelete` and `ExecUpdate`.*
This would ensure that the foreign partitions are checked only when they
are actually targeted by the operation.

However, if a D/U is issued on the root partition and it includes foreign
partitions that do not support the operation,
it will still result in an error. This is intentional because the onus of
managing data in foreign partitions lies with the user.
Only after the user removes relevant data from those foreign partitions
will such operations succeed at root level.

We also want to mention that `TRUNCATE` suffers from the same limitation
but can be taken as a next step
once D/U are handled.

The proposed patch is attached for review.

> postgres=# select version();
> version
>
> ----------------------------------------------------------------------------------------------------------------------
> PostgreSQL 18beta1 on aarch64-apple-darwin24.5.0, compiled by Apple clang
> version 17.0.0 (clang-1700.0.13.5), 64-bit
> (1 row)
> 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
> '/Users/sshirisha/workspace/postgres/src/test/regress/data/test_data_float.csv',
> format 'csv', delimiter ',');
> ALTER TABLE pt ATTACH PARTITION ext FOR VALUES FROM (10) TO (20);
> SELECT * FROM pt;
> postgres=# SELECT * FROM pt;
> a | b
> ----+-----
> 5 | 0.1
> 6 | 0.2
> 15 | 0.3
> 21 | 0.4
> (4 rows)

*Without Patch :*

> postgres=# DELETE FROM pt WHERE b = 0.2; --- delete errors out
> even if foreign_table `ext` is not the target
> 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; --- update errors out
> even if foreign_table `ext` is not the target
> ERROR: cannot update foreign table "ext"
> postgres=# UPDATE pt SET b = 0.5;
> ERROR: cannot update foreign table "ext"

*With Patch:*

> 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"

Thanks and Regards,
Shirisha and Ashwin
Broadcom Inc.

--
This electronic communication and the information and any files transmitted
with it, or attached to it, are confidential and are intended solely for
the use of the individual or entity to whom it is addressed and may contain
information that is confidential, legally privileged, protected by privacy
laws, or otherwise restricted from disclosure to anyone else. If you are
not the intended recipient or the person responsible for delivering the
e-mail to the intended recipient, you are hereby notified that any use,
copying, distributing, dissemination, forwarding, printing, or copying of
this e-mail is strictly prohibited. If you received this e-mail in error,
please return the e-mail to the sender, delete it from your computer, and
destroy any printed copy of it.