[BUG] Query with postgres fwd deletes more tuples than it should - Mailing list pgsql-hackers

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