Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table

On Wed, Jul 30, 2025 at 12:48 AM Jehan-Guillaume de Rorthais
<jgdr@dalibo.com> wrote:
> On Wed, 23 Jul 2025 19:38:19 +0900
> Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > On Sat, Jul 19, 2025 at 12:53 AM Jehan-Guillaume de Rorthais
> > <jgdr@dalibo.com> wrote:
> > > Or maybe we should just not support foreign table to reference a
> > > remote partitioned table?
> >
> > I don't think so because we can execute SELECT, INSERT, and direct
> > UPDATE/DELETE safely on such a foreign table.
>
> Sure, but it's still possible to create one local foreign partition pointing to
> remote foreign equivalent. And it seems safer considering how hard it seems to
> keep corruptions away from the current situation.

Yeah, that would be a simple workaround for this issue.

> > I think a simple fix for this is to teach the system that the foreign
> > table is a partitioned table; in more detail, I would like to propose
> > to 1) add to postgres_fdw a table option, inherited, to indicate
> > whether the foreign table is a partitioned/inherited table or not, and
> > 2) modify postgresPlanForeignModify() to throw an error if the given
> > operation is an update/delete on such a foreign table.  Attached is a
> > WIP patch for that.  I think it is the user's responsibility to set
> > the option properly, but we could modify postgresImportForeignSchema()
> > to support that.  Also, I think this would be back-patchable.
>
> So it's just a flag the user must set to allow/disallow UPDATE/DELETE on a
> foreign table. I'm not convinced by this solution as users can still
> easily corrupt their data just because they overlooked the documentation.
>
> What about the first solution Ashutosh Bapat was suggesting: «Use WHERE CURRENT
> OF with cursors to update rows.» ?
> https://www.postgresql.org/message-id/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS%2BOxcQo%3DaBDn1COywmcg%40mail.gmail.com
>
> It seems to me it never has been explored, is it?

My concern about that solution is: as mentioned by him, it requires
fetching only one row from the remote at a time, which would lead to
large performance degradation when updating many rows.

Best regards,
Etsuro Fujita