Re: Writable FDW: returning clauses.

Hello.

While implementing the new writable API for FDWs, I wondered wether they
are any obvious problems with the following behavior for handling returning
clauses (for the delete case).

The goal is to fetch all required attributes during the initial scan, and
avoid fetching data on the delete operation itself.

- in the AddForeignUpdateTargets hook, add resjunk entries for the columns in
the returning clause
- in the ExecForeignDelete hook, fill the returned slot with values taken from
the planSlot.

--
Ronan Dunklau

Ronan Dunklau <rdunklau(at)gmail(dot)com> writes:
> While implementing the new writable API for FDWs, I wondered wether they
> are any obvious problems with the following behavior for handling returning
> clauses (for the delete case).

> The goal is to fetch all required attributes during the initial scan, and
> avoid fetching data on the delete operation itself.

> - in the AddForeignUpdateTargets hook, add resjunk entries for the columns in
> the returning clause
> - in the ExecForeignDelete hook, fill the returned slot with values taken from
> the planSlot.

You can try it if you want. There were some reasons not to try it in
postgres_fdw:

* this would foreclose doing something closer to the semantics for local
tables, in which the initial scan doesn't lock the rows.

* at least update operations have to pull back the actual row anyhow in
order to tell the truth when a BEFORE UPDATE trigger on the remote
changes the stored data.

Both of those boil down to the fact that the initial scan may not see
the right data to return, if there are other actors involved. I grant
that some remote data sources don't have any such issues to worry about,
but you need to be careful.

There are some comments in postgres_fdw about eventually optimizing the
case where all update/delete quals are remote into a scan node that does
UPDATE/DELETE RETURNING to start with, and then the Modify node would
have to do what you suggest in order to have data to return. It didn't
seem like something to tackle in the first iteration though.

regards, tom lane