Re: BEFORE UPDATE trigger on postgres_fdw table not work

Fujita-san,

On Tue, Jun 11, 2019 at 6:09 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> On Tue, Jun 11, 2019 at 1:31 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > > On Tue, Jun 11, 2019 at 10:51 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> > > > 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.
> >
> > Sorry but I still don't understand. Sure, *some* columns of the table
> > not present in the UPDATE statement are fetched, but the column(s)
> > being assigned to are not fetched.
> >
> > -- before creating a trigger
> > 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
> >
> > In this case, column 'a' is not present in the rows that are fetched
> > to be updated, because it's only assigned to and not referenced
> > anywhere (such as in WHERE clauses). Which is understandable, because
> > fetching it would be pointless.
>
> Right, but what I'm saying here is what you call "some columns". For
> UPDATE, the planner adds any unassigned columns to the targetlist (see
> expand_targetlist()), so the reltarget for the target relation would
> include such columns, leading to fetching them from the remote in
> postgres_fdw even if the target table doesn't have relevant triggers.

Thanks for clarifying again. I now understand that you didn't mean
*all* columns.

It's just that I was interpreting your words in the context of Tom's
concern, so I thought you are implying that *all* columns are always
fetched, irrespective of whether triggers are present (Tom's concern)
or not. Reading Tom's email again, he didn't say *all* columns, but
maybe meant so, because that's what's needed for triggers to work.

Thanks,
Amit