Re: Optimization for updating foreign tables in Postgres FDW

Tom, all,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> 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.

Agreed.

> 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.

My initial reaction is that a ForeignUpdate node makes the most sense.
That said, I've not invested any time trying to flesh that out, so take
my gut feeling for what it's worth.

> 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.

Yeah, agreed. I feel like we'll definitely want to support pushing down
LIMIT and it'll depend on the FDW as to if it can handle that or not
(and how it handles it, if it does). My thinking was actually that we'd
ask the FDW if it supports pushing down the UPDATE or DELETE statement,
as I suspect not all FDWs will want to answer that question the same way
for every case.

Thanks,

Stephen