Re: BEFORE UPDATE trigger on postgres_fdw table not work

Amit-san,

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.

Best regards,
Etsuro Fujita