Re: BEFORE UPDATE trigger on postgres_fdw table not work

I forgot to send this by "Reply ALL".

On Tue, Jun 11, 2019 at 10:51 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>
> Amit-san,
>
> On Tue, Jun 11, 2019 at 10:30 AM Amit Langote <amitlangote09@gmail.com> wrote:
> > On Mon, Jun 10, 2019 at 9:04 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > > > 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:
> > > > > > 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.
> > >
> > > Yeah, I think so too, because in UPDATE, we fetch all columns from the
> > > remote (even if the target table doesn't have relevant triggers).
> >
> > Hmm, your parenthetical remark contradicts my observation.  I can see
> > that not all columns are fetched if there are no triggers present.
> >
> > create extension postgres_fdw ;
> > create server loopback foreign data wrapper postgres_fdw ;
> > create user mapping for current_user server loopback;
> > create table loc1 (a int, b int);
> > create foreign table rem1 (a int, b int generated always as (a+1)
> > stored) server loopback options (table_name 'loc1');
> >
> > explain verbose update rem1 set a = 1;
> >                                  QUERY PLAN
> > ─────────────────────────────────────────────────────────────────────────────
> >  Update on public.rem1  (cost=100.00..182.27 rows=2409 width=14)
> >    Remote SQL: UPDATE public.loc1 SET a = $2, b = $3 WHERE ctid = $1
> >    ->  Foreign Scan on public.rem1  (cost=100.00..182.27 rows=2409 width=14)
> >          Output: 1, b, ctid
> >          Remote SQL: SELECT b, ctid FROM public.loc1 FOR UPDATE
> > (5 rows)
>
> Sorry, my explanation was not good; I should have said that in UPDATE,
> we fetch columns not mentioned in the SQL query as well (even if the
> target table doesn't have relevant triggers), so there would be no
> hazard Tom mentioned above, IIUC.
>
> Best regards,
> Etsuro Fujita