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

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