Re: Optimization for updating foreign tables in Postgres FDW

Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> Tom Lane wrote:
>> I'm not sure offhand what the new plan tree ought to look like. We could
>> just generate a ForeignScan node, but that seems like rather a misnomer.
>> Is it worth inventing a new ForeignUpdate node type? Or maybe it'd still
>> be ForeignScan but with a flag field saying "hey this is really an update
>> (or a delete)". The main benefit I can think of right now is that the
>> EXPLAIN output would be less strange-looking --- but EXPLAIN is hardly
>> the only thing that ever looks at plan trees, so having an outright
>> misleading plan structure is likely to burn us down the line.

> I can understand these qualms.
> I wonder if "ForeignUpdate" is such a good name though, since it would
> surprise the uninitiate that in the regular (no push-down) case the
> actual modification is *not* performed by ForeignUpdate.
> So it should rather be a "ForeignModifyingScan", but I personally would
> prefer a "has_side_effects" flag on ForeignScan.

I was envisioning that the EXPLAIN output would look like

Foreign Scan on tab1
Remote SQL: SELECT ...

for the normal case, versus

Foreign Update on tab1
Remote SQL: UPDATE ...

for the pushed-down-update case (and similarly for DELETE). For a
non-optimized update it'd still be a ForeignScan underneath a ModifyTable.

As for the internal representation, I was thinking of adding a CmdType
field to struct ForeignScan, with currently only CMD_SELECT, CMD_UPDATE,
CMD_DELETE as allowed values, though possibly in future we'd think of a
reason to allow CMD_INSERT there. This is more or less isomorphic to your
"has_side_effects" flag, but it allows distinguishing UPDATE from DELETE
which might be useful.

The only thing that's bothering me about this concept is that I'm not
seeing how to scale it up to handling a pushed-down update on a join,
ie, "UPDATE foo ... FROM bar ..." where both foo and bar are remote.
Maybe it's silly to worry about that until join push-down is done;
but in that case I'd vote for postponing this whole patch until we
have join push-down.

regards, tom lane