agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
Date: Tue, 29 Jul 2025 17:48:52 +0200
Message-ID: <20250729174852.14f23557@karst> (raw)
In-Reply-To: <CAPmGK15CQK-oYFMAyq+rR0rQapUHtvAGuGgY5ahERHzZ4tmC8g@mail.gmail.com>
References: <20250718175314.4513c00a@karst>
<CAPmGK15CQK-oYFMAyq+rR0rQapUHtvAGuGgY5ahERHzZ4tmC8g@mail.gmail.com>
Hi,
On Wed, 23 Jul 2025 19:38:19 +0900
Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> Hi,
>
> On Sat, Jul 19, 2025 at 12:53 AM Jehan-Guillaume de Rorthais
> <jgdr@dalibo.com> wrote:
> > […]
> > Please, find in attachment the old patch forbidding more than one row to be
> > deleted/updated from postgresExecForeignDelete and
> > postgresExecForeignUpdate. I just rebased them without modification. I
> > suppose this is much safer than leaving the FDW destroy arbitrary rows on
> > the remote side based on their sole ctid.
>
> Thanks for rebasing the patch set, but I don't think the idea
> implemented in the second patch is safe; even with the patch we have:
>
> […]
>
> The row in the partition plt_p2 is updated, which is wrong as the row
> doesn't satisfy the query's WHERE condition.
That's a clever test to expose the weakness of this patch…
> > 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.
> 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...
It seems to me it never has been explored, is it?
Regards,
view thread (30+ messages) latest in thread
Message-ID: <20250729174852.14f23557@karst>
Permalink: ../20250729174852.14f23557@karst/
Also on: postgresql.org/message-id/20250729174852.14f23557@karst
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jgdr@dalibo.com, etsuro.fujita@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
In-Reply-To: <20250729174852.14f23557@karst>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox