Re: Clarification of FDW API Documentation

Jason Petersen <jason(at)citusdata(dot)com> writes:
> 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.

> Even if there is no guarantee that `IterateForeignScan` is called exactly once
> before each `ExecForeignDelete` call (which would remove the ability to have
> them cooperate using this single cursor), one could easily devise other storage
> backends that don't need "junk" columns to perform `DELETE` operations.

Such as? I could imagine having an optimization that works like you
suggest for simple scan cases, but it's not there now, and it could not
be the only mode.

> Each of the `ExecForeign`- functions needs to return a tuple representing the
> row inserted, deleted, or modified. But each function's documentation contains
> an aside similar to this:

>> The return value is either a slot containing the data that was actually
>> inserted (this might differ from the data supplied, for example as a result
>> of trigger actions), or NULL if no row was actually inserted (again,
>> typically as a result of triggers).

> Is this even accurate in PostgreSQL 9.3? Can triggers fire against foreign
> tables?

Any local trigger execution would be handled by the core executor.
What this is on about is that the remote database might have modified or
suppressed the operation as a result of triggers on the remote table;
and we'd like the FDW to return data that reflects what actually got
inserted/updated/deleted remotely. (I guess a particular FDW might have a
policy of not reporting such things accurately, but the point of the text
is that if you want to tell the truth you can do so.)

Perhaps it would help if these paragraphs said "remote trigger" not
just "trigger".

regards, tom lane