Re: Optimization for updating foreign tables in Postgres FDW

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> I have to admit that, while I applaud the effort made to have this
> change only be to postgres_fdw, I'm not sure that having the
> update/delete happening during the Scan phase and then essentially
> no-op'ing the ExecForeignUpdate/ExecForeignDelete is entirely in-line
> with the defined API.

Yeah, I've started looking at this patch and that seemed like not
necessarily such a wise choice. I think it'd be better if the generated
plan tree had a different structure in this case. The existing approach
is an impressive hack but it's hard to call it anything but a hack.

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.

One advantage of getting the core code involved in the decision about
whether an update/delete can be pushed down is that FDW-independent checks
like whether there are relevant triggers could be implemented in the core
code, rather than having to duplicate them (and later maintain them) in
every FDW that wants to do this. OTOH, maybe the trigger issue is really
the only thing that could be shared, not sure. Stuff like "is there a
LIMIT" probably has to be in the FDW since some FDWs could support pushing
down LIMIT and others not.

regards, tom lane