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>
In-Reply-To: <ainlkoxb4m84ZJRD@paquier.xyz>
From: Nikita Malakhov <hukutoc@gmail.com>
Date: Sat, 13 Jun 2026 22:43:17 +0300
Message-ID: 
 <CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw@mail.gmail.com>
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned
 foreign table
To: Michael Paquier <michael@paquier.xyz>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>,
 Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
	pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000c45ad6065427ce4c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw%40mail.gmail.com>
Precedence: bulk

--000000000000c45ad6065427ce4c
Content-Type: text/plain; charset="UTF-8"

Hi!

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 EXTENSION postgres_fdw;

CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
CREATE USER MAPPING FOR public SERVER loopback3;

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.
Although rewritten query like
with sub as (
           select t1.id sub_id
           from measurement_fdw t1
           where t1.id=2501020100000124
           limit 1
        )
select m.id, m.doc_date, m.impact, m.amount from measurement_fdw m, sub
        where m.id = sub.sub_id;
works correctly.
Currently I try to figure out what's the cause of this strange behavior and
I'm suspicious about
/*
 * WCO_RLS_MERGE_DELETE_CHECK is used to check DELETE USING quals on
 * the existing target row.
*/
add_with_check_options(rel, rt_index,
                    WCO_RLS_MERGE_DELETE_CHECK,
merge_delete_permissive_policies,
merge_delete_restrictive_policies,
withCheckOptions,
hasSubLinks,
hasSubLinks,
true);

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

--000000000000c45ad6065427ce4c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi!<div><br></div><div>While testing the =
proposed solution we&#39;ve stumbled upon another vanilla bug related to FD=
W -</div><div>a query with DELETE ... USING selects invalid records from pa=
rtitioned FDW tables:</div><div><br></div><div>CREATE EXTENSION postgres_fd=
w;<br><br>CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;<br>D=
O $d$<br>=C2=A0 =C2=A0 BEGIN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 EXECUTE $$CREAT=
E SERVER loopback FOREIGN DATA WRAPPER postgres_fdw<br>=C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 OPTIONS (dbname &#39;$$||current_database()||$$&#39;,=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0port &#39;$$||current_setting(&#39;port&#39;)||$$&#39;<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )$$;<br>=C2=A0 =C2=A0 END;<br>$d$;<br><br>C=
REATE USER MAPPING FOR public SERVER testserver1<br>	OPTIONS (user &#39;val=
ue&#39;, password &#39;value&#39;);<br>CREATE USER MAPPING FOR CURRENT_USER=
 SERVER loopback;<br>CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;=
<br>CREATE USER MAPPING FOR public SERVER loopback3;<br><br>CREATE TABLE ac=
c_entry<br>(<br>=C2=A0 =C2=A0 id bigint,<br>=C2=A0 =C2=A0 doc_date date,<br=
>=C2=A0 =C2=A0 impact int,<br>=C2=A0 =C2=A0 amount numeric<br>) PARTITION B=
Y RANGE (doc_date);<br><br>CREATE TABLE acc_entry_p1<br>PARTITION OF acc_en=
try<br>FOR VALUES FROM (&#39;2025-01-01&#39;) TO (&#39;2025-07-01&#39;);<br=
><br>CREATE TABLE acc_entry_p2<br>PARTITION OF acc_entry<br>FOR VALUES FROM=
 (&#39;2025-07-01&#39;) TO (&#39;2026-01-01&#39;);<br><br>CREATE FOREIGN TA=
BLE measurement_fdw<br>(<br>=C2=A0 =C2=A0 id bigint,<br>=C2=A0 =C2=A0 doc_d=
ate date,<br>=C2=A0 =C2=A0 impact int,<br>=C2=A0 =C2=A0 amount numeric<br>)=
<br>SERVER loopback<br>OPTIONS (table_name &#39;acc_entry&#39;);<br><br>INS=
ERT INTO acc_entry<br>SELECT<br>=C2=A0 =C2=A0 CASE<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 WHEN g IN (4,15,26,35,46,55,66,75,86,95)<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 THEN 2501020100000124<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE=
 g<br>=C2=A0 =C2=A0 END AS id,<br>=C2=A0 =C2=A0 CASE WHEN g % 2 =3D 0 THEN =
timestamp &#39;2025-02-02&#39; ELSE timestamp &#39;2025-08-08&#39; END,<br>=
=C2=A0 =C2=A0 1,<br>=C2=A0 =C2=A0 g<br>FROM generate_series(1,100) g;<br><b=
r>DELETE FROM measurement_fdw<br>USING (<br>=C2=A0 =C2=A0 SELECT id<br>=C2=
=A0 =C2=A0 FROM measurement_fdw<br>=C2=A0 =C2=A0 WHERE id =3D 2501020100000=
124<br>=C2=A0 =C2=A0 LIMIT 1<br>) s<br>WHERE <a href=3D"http://measurement_=
fdw.id">measurement_fdw.id</a> =3D <a href=3D"http://s.id">s.id</a>;<br></d=
iv><div><br></div><div>The latter query selects and deletes records with in=
valid ID which should not be selected at all.</div><div>Although rewritten =
query like</div><div>with sub as (<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0select <a href=3D"http://t1.id">t1.id</a> sub_id<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0from measurement_fdw t1<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0where <a href=3D"http://t1.id">t1.id</a>=3D250102010000012=
4<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0limit 1<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 )<br>select <a href=3D"http://m.id">m.id</a>, m.doc_date, m.impa=
ct, m.amount from measurement_fdw m, sub<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 whe=
re <a href=3D"http://m.id">m.id</a> =3D sub.sub_id;</div><div>works correct=
ly.</div><div>Currently I try to figure out what&#39;s the cause of this st=
range behavior and I&#39;m suspicious about</div><div>/*</div><div>=C2=A0* =
WCO_RLS_MERGE_DELETE_CHECK is used to check DELETE USING quals on<br>=C2=A0=
* the existing target row.<br>*/<br>add_with_check_options(rel, rt_index,<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WCO=
_RLS_MERGE_DELETE_CHECK,<br>merge_delete_permissive_policies,<br>merge_dele=
te_restrictive_policies,<br>withCheckOptions,<br>hasSubLinks,<br>hasSubLink=
s,<br>true);<br></div><div><br></div></div><div>--</div><div dir=3D"ltr" cl=
ass=3D"gmail_signature"><div dir=3D"ltr"><div style=3D"color:rgb(34,34,34)"=
>Regards,</div><div style=3D"color:rgb(34,34,34)">Nikita Malakhov</div><div=
 style=3D"color:rgb(34,34,34)">Postgres Professional</div><div style=3D"col=
or:rgb(34,34,34)">The Russian Postgres Company</div><div style=3D"color:rgb=
(34,34,34)"><a href=3D"https://postgrespro.ru/" target=3D"_blank">https://p=
ostgrespro.ru/</a></div></div></div></div>

--000000000000c45ad6065427ce4c--