MIME-Version: 1.0
References: <20250718175314.4513c00a@karst>
 <CAPmGK15CQK-oYFMAyq+rR0rQapUHtvAGuGgY5ahERHzZ4tmC8g@mail.gmail.com>
 <20250729174852.14f23557@karst>
 <CAPmGK16v_We-k30qQaP+AARTr3n_dRg6yFuHP39sjV5uE_ne0Q@mail.gmail.com>
 <CAN-LCVMz58ukZ7ubGXiLuTeFE7wWmSwDw4URpF0q1ejzRvqbzg@mail.gmail.com>
 <CAN-LCVM2iOWkfFt22yVEGOrp-76YP3-BVKQg+A20TENkVh8o1w@mail.gmail.com>
 <CAN-LCVPgq0zfOU+BLrHnr2Sex_zndNjzWoAiONWD=R4ULQ2BAA@mail.gmail.com>
 <CAPmGK166P+ngd2ehady=_f-L4MePgBdBNxN5gi5_gSAfmV82QA@mail.gmail.com>
In-Reply-To: 
 <CAPmGK166P+ngd2ehady=_f-L4MePgBdBNxN5gi5_gSAfmV82QA@mail.gmail.com>
From: Etsuro Fujita <etsuro.fujita@gmail.com>
Date: Wed, 10 Jun 2026 20:30:46 +0900
Message-ID: 
 <CAPmGK17FtFGMkeCkRyCH8hQ05fNxoWZmUL-W9EAt1_qy05QOpw@mail.gmail.com>
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned
 foreign table
To: Nikita Malakhov <hukutoc@gmail.com>
Cc: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
 pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAPmGK17FtFGMkeCkRyCH8hQ05fNxoWZmUL-W9EAt1_qy05QOpw%40mail.gmail.com>
Precedence: bulk

On Mon, Jun 1, 2026 at 7:44=E2=80=AFPM Etsuro Fujita <etsuro.fujita@gmail.c=
om> wrote:
> On Fri, May 15, 2026 at 2:23=E2=80=AFAM Nikita Malakhov <hukutoc@gmail.co=
m> 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 =3D b || b where b =3D 'bar' and random() =
< 1.0;
                                           QUERY PLAN
---------------------------------------------------------------------------=
---------------------
 Update on public.ft2  (cost=3D100.00..121.66 rows=3D0 width=3D0)
   Remote SQL: UPDATE public.ft1 SET b =3D $3 WHERE ctid =3D $1 AND tableoi=
d =3D $2
   ->  Foreign Scan on public.ft2  (cost=3D100.00..121.66 rows=3D1 width=3D=
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 =3D 'bar')) FOR UPDATE
(6 rows)

update ft2 set b =3D b || b where b =3D '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