public inbox for [email protected]
help / color / mirror / Atom feedFrom: Etsuro Fujita <[email protected]>
To: Nikita Malakhov <[email protected]>
Cc: Jehan-Guillaume de Rorthais <[email protected]>
Cc: [email protected]
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
Date: Wed, 10 Jun 2026 20:30:46 +0900
Message-ID: <CAPmGK17FtFGMkeCkRyCH8hQ05fNxoWZmUL-W9EAt1_qy05QOpw@mail.gmail.com> (raw)
In-Reply-To: <CAPmGK166P+ngd2ehady=_f-L4MePgBdBNxN5gi5_gSAfmV82QA@mail.gmail.com>
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>
On Mon, Jun 1, 2026 at 7:44 PM Etsuro Fujita <[email protected]> wrote:
> On Fri, May 15, 2026 at 2:23 AM Nikita Malakhov <[email protected]> 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
view thread (11+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
In-Reply-To: <CAPmGK17FtFGMkeCkRyCH8hQ05fNxoWZmUL-W9EAt1_qy05QOpw@mail.gmail.com>
* 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