public inbox for [email protected]  

help / color / mirror / Atom feed

From: Nikita Malakhov <[email protected]>
To: Michael Paquier <[email protected]>
Cc: Etsuro Fujita <[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: Sat, 13 Jun 2026 22:43:17 +0300
Message-ID: <CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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]>

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/

view thread (11+ messages)  latest in thread

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: <CAN-LCVOTB-L7gw89u4RMqOgAFQ34CnzywB_iuXie8iNwgmWREw@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