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

Hi hackers,

This attached patch relies on fdw_scan_tlist to carry the new
remote-tableoid row-identity column.

fdw_scan_tlist is a targetlist describing the contents of the scan tuple
returned by the FDW; it can be NIL if the scan tuple matches the declared
rowtype of the foreign table, which is the normal case for a simple foreign
table scan. (If the plan node represents a foreign join, fdw_scan_tlist
is required since there is no rowtype available from the system catalogs.)

A base-relation foreign scan normally fetches columns positionally: the
scan tuple is assumed to match the foreign table's own rowtype 1:1, so
nothing above the scan needs to know what the remote query actually
returned.

The remote tableoid doesn't fit the positional world at all - it isn't a
column the local foreign table has, so there's no local attribute number
for it to occupy. Reusing the ordinary tableoid system column doesn't
work either, since that resolves locally to the foreign table's own OID,
not the remote row's. So it has to travel as an invented pseudo-column,
under an out-of-range attribute number - RemoteTableOidAttributeNumber,
defined as MaxHeapAttributeNumber + 1, i.e. one past anything a real
catalog column could ever have, so it can never collide with a genuine
attribute - and that forces the scan onto the same explicit-tlist path
joins already use.

Please review the attached patch and let me know your comments.

Thanks & Best Regards,
Ajit

On Thu, 9 Jul 2026 at 19:34, Nikita Malakhov <hukutoc(at)gmail(dot)com> wrote:

> Hi hackers!
>
> I've fixed the issue mentioned in two previous messages, so for anyone
> already have taken
> my patch set above I'd compiled a fixed patch set. It does not object an
> approach
> proposed by Etsuro-san.
>
> The first patch file
> v4-0001-copy-and-remote-tableoid-param.patch
> is a core patch introducing extended copy slot and tuple facilities
> and core machinery for remote table OIDs;
>
> The second file
> v4-0002-teach-fdw-use-remote-tableoid.patch
> is a fdw facilities changes (fixed) to use remote table OID.
>
> On Mon, Jun 15, 2026 at 5:55 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
> wrote:
>
>> On Sun, Jun 14, 2026 at 4:43 AM Nikita Malakhov <hukutoc(at)gmail(dot)com>
>> wrote:
>> > 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 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.
>>
>> I think that that would be another example that the bug discussed here
>> causes unexpected results, as I have this after inserting the data
>> into the partitioned table:
>>
>> select tableoid::regclass, ctid, * from acc_entry where ctid in
>> (select ctid from acc_entry where id = 2501020100000124);
>> tableoid | ctid | id | doc_date | impact | amount
>> --------------+--------+------------------+------------+--------+--------
>> acc_entry_p1 | (0,2) | 2501020100000124 | 2025-02-02 | 1 | 4
>> acc_entry_p1 | (0,8) | 16 | 2025-02-02 | 1 | 16
>> acc_entry_p1 | (0,13) | 2501020100000124 | 2025-02-02 | 1 | 26
>> acc_entry_p1 | (0,18) | 36 | 2025-02-02 | 1 | 36
>> acc_entry_p1 | (0,23) | 2501020100000124 | 2025-02-02 | 1 | 46
>> acc_entry_p1 | (0,28) | 56 | 2025-02-02 | 1 | 56
>> acc_entry_p1 | (0,33) | 2501020100000124 | 2025-02-02 | 1 | 66
>> acc_entry_p1 | (0,38) | 76 | 2025-02-02 | 1 | 76
>> acc_entry_p1 | (0,43) | 2501020100000124 | 2025-02-02 | 1 | 86
>> acc_entry_p1 | (0,48) | 96 | 2025-02-02 | 1 | 96
>> acc_entry_p2 | (0,2) | 3 | 2025-08-08 | 1 | 3
>> acc_entry_p2 | (0,8) | 2501020100000124 | 2025-08-08 | 1 | 15
>> acc_entry_p2 | (0,13) | 25 | 2025-08-08 | 1 | 25
>> acc_entry_p2 | (0,18) | 2501020100000124 | 2025-08-08 | 1 | 35
>> acc_entry_p2 | (0,23) | 45 | 2025-08-08 | 1 | 45
>> acc_entry_p2 | (0,28) | 2501020100000124 | 2025-08-08 | 1 | 55
>> acc_entry_p2 | (0,33) | 65 | 2025-08-08 | 1 | 65
>> acc_entry_p2 | (0,38) | 2501020100000124 | 2025-08-08 | 1 | 75
>> acc_entry_p2 | (0,43) | 85 | 2025-08-08 | 1 | 85
>> acc_entry_p2 | (0,48) | 2501020100000124 | 2025-08-08 | 1 | 95
>> (20 rows)
>>
>> Note that the rows with normal ids have the same ctid as the rows with
>> id=2501020100000124 (for example, ctid of the row with id=3 is (0,2),
>> which is the same as that of the first row, which has
>> id=2501020100000124), so the bug would delete such normal-id rows as
>> well when performing the delete query.
>>
>> Best regards,
>> Etsuro Fujita
>>
>
>
> --
> Regards,
> Nikita Malakhov
> Postgres Professional
> The Russian Postgres Company
> https://postgrespro.ru/
>