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.
