Re: 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(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