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(dot)fujita(at)gmail(dot)com> wrote:
>
> Amit-san,
>
> On Tue, Jun 11, 2019 at 10:30 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > On Mon, Jun 10, 2019 at 9:04 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> > > > On Tue, May 28, 2019 at 12:54 PM Amit Langote
> > > > <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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