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

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/