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

Hi hackers!

We've been bitten by this bug recently too. So I've taken off from the
previous approach,
have fixed some issues and developed it a but further, please check this
POC patch.

While working on it I've stumbled upon a bunch of checks inside copy
slot/tuple machinery
which considered only case when source and destination match, with an old
commentary
that this is not very good and has to be improved, so I've slightly
modified this functionality
for my purposes.

On Wed, Aug 6, 2025 at 2:25 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

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

-- 
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/