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

Hi,

On Thu, Jun 12, 2025 at 1:47 PM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:

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.

The proposed change would make the behavior consistent with the cases
for INSERT/COPY into partitioned tables with non-insertable
foreign-table partitions, so +1 in general. (I have not looked at the
patch in detail yet.)

Best regards,
Etsuro Fujita

Hi all,

The proposed change would make the behavior consistent with the cases

for INSERT/COPY into partitioned tables with non-insertable
foreign-table partitions, so +1 in general.

Thanks for the initial feedback on making this behavior consistent with
INSERT/COPY.

Just wanted to follow up on the patch, any additional feedback or
improvement in the patch would be very helpful.

Thanks and Regards,
Shirisha
Broadcom Inc.

On Thu, Jun 12, 2025 at 3:59 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

Hi,

On Thu, Jun 12, 2025 at 1:47 PM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:

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.

The proposed change would make the behavior consistent with the cases
for INSERT/COPY into partitioned tables with non-insertable
foreign-table partitions, so +1 in general. (I have not looked at the
patch in detail yet.)

Best regards,
Etsuro Fujita

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

On Tue, Jul 1, 2025 at 2:24 AM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:

Just wanted to follow up on the patch, any additional feedback or improvement in the patch would be very helpful.

Did you add the patch to the CommitFest App?

https://commitfest.postgresql.org

If not, I will recommend that to get more feedback from developers.

Best regards,
Etsuro Fujita

Did you add the patch to the CommitFest App?

Yes, I had attempted to register the patch earlier but had to wait for the
cool-off period to pass for my newly created account.

I’ve now successfully registered the patch on the CommitFest app
https://commitfest.postgresql.org/patch/5901

Thanks and Regards,
Shirisha
Broadcom Inc.

On Sun, Jul 6, 2025 at 2:34 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

On Tue, Jul 1, 2025 at 2:24 AM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:

Just wanted to follow up on the patch, any additional feedback or

improvement in the patch would be very helpful.

Did you add the patch to the CommitFest App?

https://commitfest.postgresql.org

If not, I will recommend that to get more feedback from developers.

Best regards,
Etsuro Fujita

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

On Mon, Jul 7, 2025 at 6:46 PM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:

Did you add the patch to the CommitFest App?

Yes, I had attempted to register the patch earlier but had to wait for the cool-off period to pass for my newly created account.

I’ve now successfully registered the patch on the CommitFest app
https://commitfest.postgresql.org/patch/5901

Thanks for that. I tested/reviewed the patch quickly.

The patch does not work well with table inheritance:

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 ',');
select * from ft;
a | b
-----+----
BBB | 42
(1 row)

alter foreign table ft inherit pt;
update pt set b = b + 1000 where a = 'AAA';
ERROR: cannot update foreign table "ft"

Why doesn't the patch cover this case?

--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -127,3 +127,31 @@ SELECT pg_partition_root('ptif_li_child');
 DROP VIEW ptif_test_view;
 DROP MATERIALIZED VIEW ptif_test_matview;
 DROP TABLE ptif_li_parent, ptif_li_child;
+
+-- Test UPDATE/DELETE on partition table with foreign partitions
+\getenv abs_srcdir PG_ABS_SRCDIR
+CREATE EXTENSION file_fdw;
+CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

I think the regression tests should be moved to file_fdw.

That is it. I will do the rest of the review in Commitfest PG19-2 (as
this was registered for it).

Best regards,
Etsuro Fujita

Thanks Etsuro for the review comments.
I've addressed all of them in this updated patch:

- Rebased the patch on the latest changes
- Added support for inherited foreign tables
- Moved the regression test to file_fdw
- Added test for inherited foreign tables

Please find the updated patch attached.
CommitFest link - https://commitfest.postgresql.org/patch/5901

Thanks and Regards,
Shirisha
Broadcom Inc.

On Wed, Jul 9, 2025 at 5:06 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

On Wed, Sep 17, 2025 at 1:38 AM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:

I've addressed all of them in this updated patch:

- Rebased the patch on the latest changes
- Added support for inherited foreign tables
- Moved the regression test to file_fdw
- Added test for inherited foreign tables

Please find the updated patch attached.
CommitFest link - https://commitfest.postgresql.org/patch/5901

Will review.

Thanks for updating the patch!

Best regards,
Etsuro Fujita