Re: Optimization for updating foreign tables in Postgres FDW

On 2014/09/13 0:13, Tom Lane wrote:
> 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.

I'll re-add this to the final CF. And I'll update the patch.

Best regards,
Etsuro Fujita