public inbox for [email protected]
help / color / mirror / Atom feedFrom: Etsuro Fujita <[email protected]>
To: Nikita Malakhov <[email protected]>
Cc: Michael Paquier <[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: Mon, 15 Jun 2026 23:54:55 +0900
Message-ID: <CAPmGK14YzEjixwy+Frz_fNiFYBMkri8h=YyEoJKWXvEbbsoXwQ@mail.gmail.com> (raw)
In-Reply-To: <CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw@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>
<CAPmGK17FtFGMkeCkRyCH8hQ05fNxoWZmUL-W9EAt1_qy05QOpw@mail.gmail.com>
<[email protected]>
<CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw@mail.gmail.com>
On Sun, Jun 14, 2026 at 4:43 AM Nikita Malakhov <[email protected]> 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
view thread (11+ messages)
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], [email protected]
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
In-Reply-To: <CAPmGK14YzEjixwy+Frz_fNiFYBMkri8h=YyEoJKWXvEbbsoXwQ@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