Horton
Menu

Thread

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

    Jehan-Guillaume de Rorthais <jgdr@dalibo.com> — 2025-07-18T15:53:14Z

    Hi all,

We have been bitten by this old bug recently:

https://www.postgresql.org/message-id/flat/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS%2BOxcQo%3DaBDn1COywmcg%40mail.gmail.com

I suspect this bug lost attention when the only fixed submitted to the
commitfest has been flagged as "returned with feedback":

https://commitfest.postgresql.org/patch/1819/

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.

The original discussion talked about using "WHERE CURRENT OF" with cursors to
update/delete rows but discard it because of performance penalty. As adding
tableoid as a junk clause as been rejected, should we investigate the former?
At least for existing major release?

Or maybe we should just not support foreign table to reference a
remote partitioned table?

I'm afraid other fix suggestions from 2018-2019 couldn't be backported as they
seem to require additional feature in FDW altogether. This might be another
reason this bug has been forgotten.

Regards,

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-07-23T10:38:19Z

    Hi,

On Sat, Jul 19, 2025 at 12:53 AM Jehan-Guillaume de Rorthais
<jgdr@dalibo.com> wrote:
> We have been bitten by this old bug recently:
>
> https://www.postgresql.org/message-id/flat/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS%2BOxcQo%3DaBDn1COywmcg%40mail.gmail.com
>
> I suspect this bug lost attention when the only fixed submitted to the
> commitfest has been flagged as "returned with feedback":
>
> https://commitfest.postgresql.org/patch/1819/

This is on my TODO list, but I didn't have time to work on it, unfortunately.

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

create table plt (a int, b int) partition by list (a);
create table plt_p1 partition of plt for values in (1);
create table plt_p2 partition of plt for values in (2);
create function trig_null() returns trigger language plpgsql as
  $$ begin return null; end; $$;
create trigger trig_null before update or delete on plt_p1
  for each row execute function trig_null();
create foreign table fplt (a int, b int)
  server loopback options (table_name 'plt');

insert into fplt values (1, 1), (2, 2);
INSERT 0 0
select tableoid::regclass, ctid, * from plt;
 tableoid | ctid  | a | b
----------+-------+---+---
 plt_p1   | (0,1) | 1 | 1
 plt_p2   | (0,1) | 2 | 2
(2 rows)

explain verbose update fplt set b = (case when random() <= 1 then 10
else 20 end) where a = 1;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Update on public.fplt  (cost=100.00..128.02 rows=0 width=0)
   Remote SQL: UPDATE public.plt SET b = $2 WHERE ctid = $1
   ->  Foreign Scan on public.fplt  (cost=100.00..128.02 rows=7 width=42)
         Output: CASE WHEN (random() <= '1'::double precision) THEN 10
ELSE 20 END, ctid, fplt.*
         Remote SQL: SELECT a, b, ctid FROM public.plt WHERE ((a = 1))
FOR UPDATE
(5 rows)

update fplt set b = (case when random() <= 1 then 10 else 20 end) where a = 1;
UPDATE 1
select tableoid::regclass, ctid, * from plt;
 tableoid | ctid  | a | b
----------+-------+---+----
 plt_p1   | (0,1) | 1 |  1
 plt_p2   | (0,2) | 2 | 10
(2 rows)

The row in the partition plt_p2 is updated, which is wrong as the row
doesn't satisfy the query's WHERE condition.

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

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.

What do you think about that?

Best regards,
Etsuro Fujita

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-07-27T10:35:59Z

    On Wed, Jul 23, 2025 at 7:38 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> 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.

I noticed that this issue occurs in other cases: eg, if a foreign
table is an updatable view on a partitioned/inherited table,
non-pushed-down updates/deletes on the foreign table have the same
issue.  So adding the support in postgresImportForeignSchema() is not
that simple.  I feel like leaving it to the user, at least for
back-branches.

Best regards,
Etsuro Fujita

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

    Jehan-Guillaume de Rorthais <jgdr@dalibo.com> — 2025-07-29T15:48:52Z

    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.gmail.com

It seems to me it never has been explored, is it?

Regards,

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-08-06T11:25:12Z

    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

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

    Nikita Malakhov <hukutoc@gmail.com> — 2026-04-21T11:34:28Z

    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/

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

    Nikita Malakhov <hukutoc@gmail.com> — 2026-05-14T15:05:41Z

    Hi hackers!

I've rebased the patch set and created patch in current commitfest.
Reviewers are welcome!
Current Postgres copy slot/tuple mechanics supports only the case where
source and destination
slot/tuple have an equal number of attributes. To support additional remote
OID attribute
I've extended these functions to be able to work when source and
destination differ.
The second patch - core-remote-tableoid-param - introduces remote table OID
and
passes it through the executor to be used by the FDW engine.
The third patch teaches FDW engine to use this remote table OID in UPDATE
and
DELETE queries.

CF entry: https://commitfest.postgresql.org/patch/6770/

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

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

    Nikita Malakhov <hukutoc@gmail.com> — 2026-05-14T17:22:55Z

    Hi hackers,

CFbot was unhappy with previous patch set, so here's updated one

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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-01T10:44:34Z

    Hi Nikita,

On Fri, May 15, 2026 at 2:23 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
> CFbot was unhappy with previous patch set, so here's updated one

Thanks for working on this issue!

I took a quick look at the patch set.  IIUC I think it's created based
on what I proposed in the original thread, which is invasive and thus
not back-patchable, so what you are proposing here isn't
back-patchable, either, I think.

I think we should first work on a back-patchable solution.  So I'd
like to re-propose the patch that I proposed in this thread before to
disallow UPDATE/DELETE in problematic cases [1].  Attached is a new
version of the patch.  Changes are:

* Renamed the new table option inherited to remotely_inherited, to
avoid confusion with local inheritance.
* Moved the logic to prevent problematic UPDATE/DELETE from a planner
function to an executor function, to avoid throwing an error
unnecessarily when there are no target rows to update/delete.
* Added docs to postgres-fdw.sgml.

I'm planning to add the postgresImportForeignSchema() support in the
next version.

I think the remotely_inherited option would be useful when adding the
support for the UPDATE/DELETE, as it could be used to address one of
Tom Lane's comments about what I proposed in the original thread that
it adds the tabloid condition to a remote UPDATE/DELETE query whether
the target table is inherited or not: that could be avoid if the
option is set to false.

What do you think about that?

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAPmGK15CQK-oYFMAyq%2BrR0rQapUHtvAGuGgY5ahERHzZ4tmC8g%40mail.gmail.com

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-05T11:59:17Z

    On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> I think we should first work on a back-patchable solution.  So I'd
> like to re-propose the patch that I proposed in this thread before to
> disallow UPDATE/DELETE in problematic cases [1].  Attached is a new
> version of the patch.  Changes are:
>
> * Renamed the new table option inherited to remotely_inherited, to
> avoid confusion with local inheritance.
> * Moved the logic to prevent problematic UPDATE/DELETE from a planner
> function to an executor function, to avoid throwing an error
> unnecessarily when there are no target rows to update/delete.
> * Added docs to postgres-fdw.sgml.
>
> I'm planning to add the postgresImportForeignSchema() support in the
> next version.

I created the patch to add that support on top of the patch I sent in
a previous email, which I'm attaching along with the base patch.  It's
the same as before, except that I fixed a typo in docs pointed out by
Michael-san off-list.

Comments welcome!

Best regards,
Etsuro Fujita

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

    Nikita Malakhov <hukutoc@gmail.com> — 2026-06-06T20:33:32Z

    Hi!

Thanks for working on the subject! I'll try to take a look inside in a
couple of days.

On Fri, Jun 5, 2026 at 2:59 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

> On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <etsuro.fujita@gmail.com>
> wrote:
> > I think we should first work on a back-patchable solution.  So I'd
> > like to re-propose the patch that I proposed in this thread before to
> > disallow UPDATE/DELETE in problematic cases [1].  Attached is a new
> > version of the patch.  Changes are:
> >
> > * Renamed the new table option inherited to remotely_inherited, to
> > avoid confusion with local inheritance.
> > * Moved the logic to prevent problematic UPDATE/DELETE from a planner
> > function to an executor function, to avoid throwing an error
> > unnecessarily when there are no target rows to update/delete.
> > * Added docs to postgres-fdw.sgml.
> >
> > I'm planning to add the postgresImportForeignSchema() support in the
> > next version.
>
> I created the patch to add that support on top of the patch I sent in
> a previous email, which I'm attaching along with the base patch.  It's
> the same as before, except that I fixed a typo in docs pointed out by
> Michael-san off-list.
>
> Comments welcome!
>
> Best regards,
> Etsuro Fujita
>


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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-08T12:45:03Z

    Hi Nikita,

On Sun, Jun 7, 2026 at 5:33 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
> Thanks for working on the subject! I'll try to take a look inside in a couple of days.

Great!

Best regards,
Etsuro Fujita

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

    Michael Paquier <michael@paquier.xyz> — 2026-06-10T05:37:57Z

    On Fri, Jun 05, 2026 at 08:59:17PM +0900, Etsuro Fujita wrote:
> I created the patch to add that support on top of the patch I sent in
> a previous email, which I'm attaching along with the base patch.  It's
> the same as before, except that I fixed a typo in docs pointed out by
> Michael-san off-list.

Splitting the patch set into two pieces, as of one for the
introduction of the remotely_inherited option defaulting to the
current HEAD behavior, and one for the modification of the IMPORT
FOREIGN SCHEMA, makes sense here.  A backpatch of the first patch is a
no-brainer, so as it gives a way for users to switch to the new
behavior at will.  I am however on edge regarding the wisdom of
backpatching the second patch, which would force a new behavior of the
postgres_fdw implementation for partitioned tables (based on my
read of the test with "t4") and INHERIT ("t6", "t8") depending on the
relkind or the property of the relation imported.  I can't help but
wonder why you don't take a different, slightly more conservative
approach on HEAD and the stable branches with a new option that can be
specified to the IMPORT FOREIGN SCHEMA query, to make the choice of
setting remotely_inherited for a relation imported an opt-in or
opt-out choice.

I would not object with a switch of the default behavior across major
versions, and perhaps my argument is not sound enough, but I've learnt
my share when it comes to be careful with changes like the one you may
introduce here across a minor release, particularly knowing that
remotely_inherited *can* be set on an option basis when creating a
table *or* when importing a schema.  The designs we have for these
queries allows this kind of flexibility.
--
Michael

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-10T11:22:31Z

    On Wed, Jun 10, 2026 at 2:38 PM Michael Paquier <michael@paquier.xyz> wrote:
> On Fri, Jun 05, 2026 at 08:59:17PM +0900, Etsuro Fujita wrote:
> > I created the patch to add that support on top of the patch I sent in
> > a previous email, which I'm attaching along with the base patch.  It's
> > the same as before, except that I fixed a typo in docs pointed out by
> > Michael-san off-list.
>
> Splitting the patch set into two pieces, as of one for the
> introduction of the remotely_inherited option defaulting to the
> current HEAD behavior, and one for the modification of the IMPORT
> FOREIGN SCHEMA, makes sense here.  A backpatch of the first patch is a
> no-brainer, so as it gives a way for users to switch to the new
> behavior at will.  I am however on edge regarding the wisdom of
> backpatching the second patch, which would force a new behavior of the
> postgres_fdw implementation for partitioned tables (based on my
> read of the test with "t4") and INHERIT ("t6", "t8") depending on the
> relkind or the property of the relation imported.  I can't help but
> wonder why you don't take a different, slightly more conservative
> approach on HEAD and the stable branches with a new option that can be
> specified to the IMPORT FOREIGN SCHEMA query, to make the choice of
> setting remotely_inherited for a relation imported an opt-in or
> opt-out choice.
>
> I would not object with a switch of the default behavior across major
> versions, and perhaps my argument is not sound enough, but I've learnt
> my share when it comes to be careful with changes like the one you may
> introduce here across a minor release, particularly knowing that
> remotely_inherited *can* be set on an option basis when creating a
> table *or* when importing a schema.  The designs we have for these
> queries allows this kind of flexibility.

I agree that we should take a more conservative approach especially on
the stable branches, and I think it's a good idea to add the option to
IMPORT FOREIGN SCHEMA for that, so I will update the patch as such in
the next version.

Thanks for the comments!

Best regards,
Etsuro Fujita

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-10T11:30:46Z

    On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Fri, May 15, 2026 at 2:23 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
> > CFbot was unhappy with previous patch set, so here's updated one

> I took a quick look at the patch set.  IIUC I think it's created based
> on what I proposed in the original thread, which is invasive and thus
> not back-patchable, so what you are proposing here isn't
> back-patchable, either, I think.

One thing I noticed about what I proposed in the original thread (but
didn't when working on it) is that it would well handle cases where
the remote table is a (simple) inherited/partitioned table, but
wouldn't cases where it's e.g., a foreign table on the remote server
pointing to such a table on another remote server.  I haven't looked
at your patch in very detail yet, but I tested it as shown below, and
it causes unexpected results, so I suppose it inherits the limitation.

create table pt (a int, b text) partition by list (a);
create table pt_p1 partition of pt for values in (1);
create table pt_p2 partition of pt for values in (2);
create foreign table ft1 (a int, b text) server loopback options
(table_name 'pt');
create foreign table ft2 (a int, b text) server loopback options
(table_name 'ft1');
insert into pt values (1, 'foo'), (2, 'bar');
select ctid, * from ft2;
 ctid  | a |  b
-------+---+-----
 (0,1) | 1 | foo
 (0,1) | 2 | bar
(2 rows)

explain verbose update ft2 set b = b || b where b = 'bar' and random() < 1.0;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Update on public.ft2  (cost=100.00..121.66 rows=0 width=0)
   Remote SQL: UPDATE public.ft1 SET b = $3 WHERE ctid = $1 AND tableoid = $2
   ->  Foreign Scan on public.ft2  (cost=100.00..121.66 rows=1 width=106)
         Output: (b || b), ctid, tableoid, $0, ft2.*
         Filter: (random() < '1'::double precision)
         Remote SQL: SELECT a, b, ctid, tableoid FROM public.ft1 WHERE
((b = 'bar')) FOR UPDATE
(6 rows)

update ft2 set b = b || b where b = 'bar' and random() < 1.0;
UPDATE 1
select ctid, * from ft2;
 ctid  | a |   b
-------+---+--------
 (0,2) | 1 | barbar
 (0,1) | 2 | bar
(2 rows)

The first row belonging to pt_p1 is updated, which is wrong; the
second one belonging to pt_p2 should be updated.

To address this, I think it would be good if we could 1) extend the
concept of inheritance to cover remote inheritances, like pt, and 2)
extend inherited UPDATE/DELETE so that we update/delete leaf tables,
like pt_p2, somehow directly, as done for local inheritances.  I'm not
sure about how to do that, though.

Best regards,
Etsuro Fujita

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

    Michael Paquier <michael@paquier.xyz> — 2026-06-10T22:25:28Z

    On Wed, Jun 10, 2026 at 08:22:31PM +0900, Etsuro Fujita wrote:
> On Wed, Jun 10, 2026 at 2:38 PM Michael Paquier <michael@paquier.xyz> wrote:
>> I would not object with a switch of the default behavior across major
>> versions, and perhaps my argument is not sound enough, but I've learnt
>> my share when it comes to be careful with changes like the one you may
>> introduce here across a minor release, particularly knowing that
>> remotely_inherited *can* be set on an option basis when creating a
>> table *or* when importing a schema.  The designs we have for these
>> queries allows this kind of flexibility.
> 
> I agree that we should take a more conservative approach especially on
> the stable branches, and I think it's a good idea to add the option to
> IMPORT FOREIGN SCHEMA for that, so I will update the patch as such in
> the next version.

Cool, thanks.
--
Michael

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

    Michael Paquier <michael@paquier.xyz> — 2026-06-10T22:30:42Z

    On Wed, Jun 10, 2026 at 08:30:46PM +0900, Etsuro Fujita wrote:
> To address this, I think it would be good if we could 1) extend the
> concept of inheritance to cover remote inheritances, like pt, and 2)
> extend inherited UPDATE/DELETE so that we update/delete leaf tables,
> like pt_p2, somehow directly, as done for local inheritances.  I'm not
> sure about how to do that, though.

FWIW, I think that there is a good argument for keeping it down to
simpler, and just not care about the option chain in such cases,
leaving it up to users to address that with two imports anyway?  Just 
having the option at one level would solve most historical problems I
could see on this thread.  Good is sometimes a better option than
theoretically perfect.  And good here means a simpler implementation
overall, at least it seems to me so..
--
Michael

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

    Nikita Malakhov <hukutoc@gmail.com> — 2026-06-13T19:43:17Z

    Hi!

While testing the proposed solution we've stumbled upon another vanilla bug
related to FDW -
a query with DELETE ... USING selects invalid records from partitioned FDW
tables:

CREATE EXTENSION postgres_fdw;

CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
CREATE USER MAPPING FOR public SERVER loopback3;

CREATE TABLE acc_entry
(
    id bigint,
    doc_date date,
    impact int,
    amount numeric
) PARTITION BY RANGE (doc_date);

CREATE TABLE acc_entry_p1
PARTITION OF acc_entry
FOR VALUES FROM ('2025-01-01') TO ('2025-07-01');

CREATE TABLE acc_entry_p2
PARTITION OF acc_entry
FOR VALUES FROM ('2025-07-01') TO ('2026-01-01');

CREATE FOREIGN TABLE measurement_fdw
(
    id bigint,
    doc_date date,
    impact int,
    amount numeric
)
SERVER loopback
OPTIONS (table_name 'acc_entry');

INSERT INTO acc_entry
SELECT
    CASE
        WHEN g IN (4,15,26,35,46,55,66,75,86,95)
            THEN 2501020100000124
        ELSE g
    END AS id,
    CASE WHEN g % 2 = 0 THEN timestamp '2025-02-02' ELSE timestamp
'2025-08-08' END,
    1,
    g
FROM generate_series(1,100) g;

DELETE FROM measurement_fdw
USING (
    SELECT id
    FROM measurement_fdw
    WHERE id = 2501020100000124
    LIMIT 1
) s
WHERE measurement_fdw.id = s.id;

The latter query selects and deletes records with invalid ID which should
not be selected at all.
Although rewritten query like
with sub as (
           select t1.id sub_id
           from measurement_fdw t1
           where t1.id=2501020100000124
           limit 1
        )
select m.id, m.doc_date, m.impact, m.amount from measurement_fdw m, sub
        where m.id = sub.sub_id;
works correctly.
Currently I try to figure out what's the cause of this strange behavior and
I'm suspicious about
/*
 * WCO_RLS_MERGE_DELETE_CHECK is used to check DELETE USING quals on
 * the existing target row.
*/
add_with_check_options(rel, rt_index,
                    WCO_RLS_MERGE_DELETE_CHECK,
merge_delete_permissive_policies,
merge_delete_restrictive_policies,
withCheckOptions,
hasSubLinks,
hasSubLinks,
true);

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

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-15T14:54:55Z

    On Sun, Jun 14, 2026 at 4:43 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
> While testing the proposed solution we've stumbled upon another vanilla bug related to FDW -
> a query with DELETE ... USING selects invalid records from partitioned FDW tables:

> CREATE TABLE acc_entry
> (
>     id bigint,
>     doc_date date,
>     impact int,
>     amount numeric
> ) PARTITION BY RANGE (doc_date);
>
> CREATE TABLE acc_entry_p1
> PARTITION OF acc_entry
> FOR VALUES FROM ('2025-01-01') TO ('2025-07-01');
>
> CREATE TABLE acc_entry_p2
> PARTITION OF acc_entry
> FOR VALUES FROM ('2025-07-01') TO ('2026-01-01');
>
> CREATE FOREIGN TABLE measurement_fdw
> (
>     id bigint,
>     doc_date date,
>     impact int,
>     amount numeric
> )
> SERVER loopback
> OPTIONS (table_name 'acc_entry');
>
> INSERT INTO acc_entry
> SELECT
>     CASE
>         WHEN g IN (4,15,26,35,46,55,66,75,86,95)
>             THEN 2501020100000124
>         ELSE g
>     END AS id,
>     CASE WHEN g % 2 = 0 THEN timestamp '2025-02-02' ELSE timestamp '2025-08-08' END,
>     1,
>     g
> FROM generate_series(1,100) g;
>
> DELETE FROM measurement_fdw
> USING (
>     SELECT id
>     FROM measurement_fdw
>     WHERE id = 2501020100000124
>     LIMIT 1
> ) s
> WHERE measurement_fdw.id = s.id;
>
> The latter query selects and deletes records with invalid ID which should not be selected at all.

I think that that would be another example that the bug discussed here
causes unexpected results, as I have this after inserting the data
into the partitioned table:

select tableoid::regclass, ctid, * from acc_entry where ctid in
(select ctid from acc_entry where id = 2501020100000124);
   tableoid   |  ctid  |        id        |  doc_date  | impact | amount
--------------+--------+------------------+------------+--------+--------
 acc_entry_p1 | (0,2)  | 2501020100000124 | 2025-02-02 |      1 |      4
 acc_entry_p1 | (0,8)  |               16 | 2025-02-02 |      1 |     16
 acc_entry_p1 | (0,13) | 2501020100000124 | 2025-02-02 |      1 |     26
 acc_entry_p1 | (0,18) |               36 | 2025-02-02 |      1 |     36
 acc_entry_p1 | (0,23) | 2501020100000124 | 2025-02-02 |      1 |     46
 acc_entry_p1 | (0,28) |               56 | 2025-02-02 |      1 |     56
 acc_entry_p1 | (0,33) | 2501020100000124 | 2025-02-02 |      1 |     66
 acc_entry_p1 | (0,38) |               76 | 2025-02-02 |      1 |     76
 acc_entry_p1 | (0,43) | 2501020100000124 | 2025-02-02 |      1 |     86
 acc_entry_p1 | (0,48) |               96 | 2025-02-02 |      1 |     96
 acc_entry_p2 | (0,2)  |                3 | 2025-08-08 |      1 |      3
 acc_entry_p2 | (0,8)  | 2501020100000124 | 2025-08-08 |      1 |     15
 acc_entry_p2 | (0,13) |               25 | 2025-08-08 |      1 |     25
 acc_entry_p2 | (0,18) | 2501020100000124 | 2025-08-08 |      1 |     35
 acc_entry_p2 | (0,23) |               45 | 2025-08-08 |      1 |     45
 acc_entry_p2 | (0,28) | 2501020100000124 | 2025-08-08 |      1 |     55
 acc_entry_p2 | (0,33) |               65 | 2025-08-08 |      1 |     65
 acc_entry_p2 | (0,38) | 2501020100000124 | 2025-08-08 |      1 |     75
 acc_entry_p2 | (0,43) |               85 | 2025-08-08 |      1 |     85
 acc_entry_p2 | (0,48) | 2501020100000124 | 2025-08-08 |      1 |     95
(20 rows)

Note that the rows with normal ids have the same ctid as the rows with
id=2501020100000124 (for example, ctid of the row with id=3 is (0,2),
which is the same as that of the first row, which has
id=2501020100000124), so the bug would delete such normal-id rows as
well when performing the delete query.

Best regards,
Etsuro Fujita