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/