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

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(dot)fujita(at)gmail(dot)com>
wrote:

> Hi,
>
> On Thu, Jun 12, 2025 at 1:47 PM Shirisha Shirisha
> <shirisha(dot)sn(at)broadcom(dot)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.