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

Hi hackers!

I've fixed the issue mentioned in two previous messages, so for anyone
already have taken
my patch set above I'd compiled a fixed patch set. It does not object an
approach
proposed by Etsuro-san.

The first patch file
v4-0001-copy-and-remote-tableoid-param.patch
is a core patch introducing extended copy slot and tuple facilities
and core machinery for remote table OIDs;

The second file
v4-0002-teach-fdw-use-remote-tableoid.patch
is a fdw facilities changes (fixed) to use remote table OID.

On Mon, Jun 15, 2026 at 5:55 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
wrote:

> On Sun, Jun 14, 2026 at 4:43 AM Nikita Malakhov <hukutoc(at)gmail(dot)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
>

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