Re: BEFORE UPDATE trigger on postgres_fdw table not work

Fujita-san,

Thanks for the comments.

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)

whereas, all columns are fetched if a trigger is defined:

create or replace function trigfunc() returns trigger as $$ begin
raise notice '%', new; return new; end; $$ language plpgsql;
create trigger rem1_trig before insert or update on rem1 for each row
execute function trigfunc();

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)

Am I missing something?

Thanks,
Amit