Обсуждение: [BUG] Query with postgres fwd deletes more tuples than it should

Hi,

If we create foreign table (via postgres_fdw) on a partitioned table, queries
that don't use "direct modify" will delete too many tuples because of
invalid "WHERE" clause.

Please, see this script :
--
CREATE DATABASE remote;
CREATE DATABASE test;

\c remote

-- create partitioned table with two partitions and fill it with some data

CREATE TABLE measurement (
    city_id         INT NOT NULL,
    logdate         DATE NOT NULL,
    peaktemp        INT,
    unitsales       INT
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

INSERT INTO measurement VALUES (1,'2006-02-01',1,1);
INSERT INTO measurement VALUES (2,'2006-03-01',1,1);

\c test

-- create foreign table on partitioned table

CREATE EXTENSION postgres_fdw;

CREATE SERVER fdw_oltp_n1 FOREIGN DATA WRAPPER
postgres_fdw OPTIONS (dbname 'remote');

CREATE USER MAPPING FOR PUBLIC SERVER fdw_oltp_n1
OPTIONS (password 'postgres');

CREATE FOREIGN TABLE measurement_fdw
    (
        city_id INT OPTIONS (column_name 'city_id') NOT NULL,
        logdate DATA OPTIONS (column_name 'logdate') NOT NULL,
        peaktemp TEXT OPTIONS (column_name 'peaktemp'),
        unitsales INT OPTIONS (column_name 'unitsales')
        )
    SERVER fdw_oltp_n1
    OPTIONS (schema_name 'public', table_name 'measurement');

-- try to delete single row from foreign table

DELETE FROM measurement_fdw
USING (
   SELECT t1.city_id sub_city_id
   FROM measurement_fdw t1
   WHERE t1.city_id=1
   LIMIT 1000
) sub
WHERE measurement_fdw.city_id = sub.sub_city_id;

-- check result

SELECT * FROM measurement_fdw;

\c remote;
SELECT * FROM measurement;

--

We are expecting only one tuple to be deleted, but the last two select
queries will show zero rows. Why? :
If query doesn't contain LIMIT, it will use direct modify, i.e. send query
like this :
--
DELETE FROM public.measurement r1 USING public.measurement r3
WHERE ((r3.city_id = 1)) AND ((r1.city_id = 1))
--
In other words, it is the desired column that is being compared.

But if there is a LIMIT in the query, then first of all we make a
selection from the foreign table (save the row in the planSlot), and
then the postgresPlanForeignModify function hardcodes the query :

DELETE FROM ... WHERE ctid = $1

The "$1" parameter will then be replaced by the ctid from the tuple
in the planSlot.

measurement_fdw has this content :
 city_id | ctid
---------+-------
       1 | (0,1)
       2 | (0,1)

Thus, both rows are appropriate for "ctid = (0,1)", and they all
will be deleted. Obviously, if we don't specify LIMIT, then only one
tuple will be deleted.

What do you think about it? How can we fix such behavior?

--
Best regards,
Daniil Davydov

Hi,

I can reproduce the same issue using the script you provided.

I think a good way to fix would be to use `(tableoid, ctid)` together as the row
identifier in row-by-row updates/deletes. This combination is unique
across partitions and avoids the incorrect behavior, while still keeping
LIMIT support.


Best regards,
--
JiaoShuntian
HighGo Software Co., Ltd.

On Wed, Sep 17, 2025 at 2:46 PM <jiaoshuntian@highgo.com> wrote:
> I can reproduce the same issue using the script you provided.

This is a known bug.  See discussions in [1], where I proposed a
simple fix for it that disables foreign modifications in problematic
cases as shown by Daniil.

> I think a good way to fix would be to use `(tableoid, ctid)` together as the row
> identifier in row-by-row updates/deletes. This combination is unique
> across partitions and avoids the incorrect behavior, while still keeping
> LIMIT support.

Agreed; actually I created such a fix as well a long time ago, but
unfortunately it has many issues...

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/flat/20250718175314.4513c00a%40karst

Hi,

On Wed, Sep 17, 2025 at 6:46 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>
> On Wed, Sep 17, 2025 at 2:46 PM <jiaoshuntian@highgo.com> wrote:
> > I can reproduce the same issue using the script you provided.
>
> This is a known bug.  See discussions in [1], where I proposed a
> simple fix for it that disables foreign modifications in problematic
> cases as shown by Daniil.
>

Thanks! Somehow I overlooked it.

> > I think a good way to fix would be to use `(tableoid, ctid)` together as the row
> > identifier in row-by-row updates/deletes. This combination is unique
> > across partitions and avoids the incorrect behavior, while still keeping
> > LIMIT support.
>
> Agreed; actually I created such a fix as well a long time ago, but
> unfortunately it has many issues...

I'll try to help with implementation of the proposed idea.

BTW, maybe we should move commitfest entry [1] to the next CF?

[1] https://commitfest.postgresql.org/patch/1819/
--
Best regards,
Daniil Davydov

On Wed, Sep 17, 2025 at 10:11 PM Daniil Davydov <3danissimo@gmail.com> wrote:
> On Wed, Sep 17, 2025 at 6:46 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > On Wed, Sep 17, 2025 at 2:46 PM <jiaoshuntian@highgo.com> wrote:
> > > I think a good way to fix would be to use `(tableoid, ctid)` together as the row
> > > identifier in row-by-row updates/deletes. This combination is unique
> > > across partitions and avoids the incorrect behavior, while still keeping
> > > LIMIT support.
> >
> > Agreed; actually I created such a fix as well a long time ago, but
> > unfortunately it has many issues...
>
> I'll try to help with implementation of the proposed idea.

Great!

> BTW, maybe we should move commitfest entry [1] to the next CF?
>
> [1] https://commitfest.postgresql.org/patch/1819/

My plan is to re-add this to CF when I address Tom's comments [2], but
I don't yet have a clear solution for that.  BUT: if you want to work
on it, feel free.

Best regards,
Etsuro Fujita

[2] https://www.postgresql.org/message-id/1590.1542393315%40sss.pgh.pa.us