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>
 <CAPmGK17FtFGMkeCkRyCH8hQ05fNxoWZmUL-W9EAt1_qy05QOpw@mail.gmail.com>
 <ainlkoxb4m84ZJRD@paquier.xyz>
 <CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw@mail.gmail.com>
In-Reply-To: 
 <CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw@mail.gmail.com>
From: Etsuro Fujita <etsuro.fujita@gmail.com>
Date: Mon, 15 Jun 2026 23:54:55 +0900
Message-ID: 
 <CAPmGK14YzEjixwy+Frz_fNiFYBMkri8h=YyEoJKWXvEbbsoXwQ@mail.gmail.com>
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned
 foreign table
To: Nikita Malakhov <hukutoc@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>,
 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/CAPmGK14YzEjixwy%2BFrz_fNiFYBMkri8h%3DYyEoJKWXvEbbsoXwQ%40mail.gmail.com>
Precedence: bulk

On Sun, Jun 14, 2026 at 4:43=E2=80=AFAM Nikita Malakhov <hukutoc@gmail.com>=
 wrote:
> While testing the proposed solution we've stumbled upon another vanilla b=
ug related to FDW -
> a query with DELETE ... USING selects invalid records from partitioned FD=
W 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 =3D 0 THEN timestamp '2025-02-02' ELSE timestamp '202=
5-08-08' END,
>     1,
>     g
> FROM generate_series(1,100) g;
>
> DELETE FROM measurement_fdw
> USING (
>     SELECT id
>     FROM measurement_fdw
>     WHERE id =3D 2501020100000124
>     LIMIT 1
> ) s
> WHERE measurement_fdw.id =3D 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 =3D 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=3D2501020100000124 (for example, ctid of the row with id=3D3 is (0,2),
which is the same as that of the first row, which has
id=3D2501020100000124), so the bug would delete such normal-id rows as
well when performing the delete query.

Best regards,
Etsuro Fujita