Re: BEFORE UPDATE trigger on postgres_fdw table not work

Hi,

On Tue, May 28, 2019 at 12:54 PM Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2019/05/27 22:02, Tom Lane wrote:
> > Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> >> On 2019/05/27 10:52, Shohei Mochizuki wrote:
> >>> I noticed returning a modified record in a row-level BEFORE UPDATE trigger
> >>> on postgres_fdw foreign tables do not work. Attached patch fixes this issue.
> >>> This is because current fdw code adds only columns to RemoteSQL that were
> >>> explicitly targets of the UPDATE as follows.
> >
> >> Yeah.  So, the trigger execution correctly modifies the existing tuple
> >> fetched from the remote server, but those changes are then essentially
> >> discarded by postgres_fdw, that is, postgresExecForeignModify().

> > Perhaps, if the table has relevant BEFORE triggers, we should just abandon
> > our attempts to optimize away fetching/storing all columns?  It seems like
> > another potential hazard here is a trigger needing to read a column that
> > is not mentioned in the SQL query.

> So, the only problem here is the optimizing away of storing all columns,
> which the Mochizuki-san's patch seems enough to fix.

Will look into the patch after returning from PGCon, unless somebody wants to.

Best regards,
Etsuro Fujita