Hi, On Thu, Jun 12, 2025 at 1:47 PM Shirisha Shirisha <shirisha...@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
