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

On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> On Fri, May 15, 2026 at 2:23 AM Nikita Malakhov <hukutoc(at)gmail(dot)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