Re: BEFORE UPDATE trigger on postgres_fdw table not work

> On Tue, Jun 11, 2019 at 10:51 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> > 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.

[ ... ]

> > 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.

If there is a trigger present though, the trigger may want to
reference 'a' in the OLD rows, so it's fetched along with any other
columns that are present in the table, because they may be referenced
too.

-- after creating a trigger
explain verbose update rem1 set a = 1;
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────
Update on public.rem1 (cost=100.00..147.23 rows=1241 width=46)
Remote SQL: UPDATE public.loc1 SET a = $2, b = $3 WHERE ctid = $1
-> Foreign Scan on public.rem1 (cost=100.00..147.23 rows=1241 width=46)
Output: 1, b, ctid, rem1.*
Remote SQL: SELECT a, b, ctid FROM public.loc1 FOR UPDATE
(5 rows)

Thanks,
Amit