Re: Clarification of FDW API Documentation

--On 13. Juni 2014 13:46:38 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> Imagine if `BeginForeignScan` set up a remote cursor and
>> `IterateForeignScan` just fetched _one tuple at a time_ (unlike the
>> current behavior where they are fetched in batches). The tuple would be
>> passed to `ExecForeignDelete` (as is required), but the remote cursor
>> would remain pointing at that tuple. Couldn't `ExecForeignDelete` just
>> call `DELETE FROM table WHERE CURRENT OF cursor` to then delete that
>> tuple?
>
> No.  This is not guaranteed (or even likely) to work in join cases: the
> tuple to be updated/deleted might no longer be the current one of the
> scan. You *must* arrange for the scan to return enough information to
> uniquely identify the tuple later, and that generally means adding some
> resjunk columns.

Yeah, this is exactly the trap i ran into while implementing the 
informix_fdw driver. It used an updatable cursor to implement the modify 
actions as you proposed first. Consider a query like

UPDATE remote SET f1 = t.id FROM local t WHERE t.id = f1

The planner might choose a hash join where the hash table is built by 
forwarding the cursor via the foreign scan. You'll end up with the cursor 
positioned at the end and you have no way to get it back "in sync" when the 
modify action is actually called.

-- 
Thanks
Bernd