Re: BEFORE UPDATE trigger on postgres_fdw table not work

Mochizuki-san,

On 2019/05/28 13:10, Shohei Mochizuki wrote:
> On 2019/05/28 12:54, Amit Langote 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.
>>
>> The fetching side is fine, because rewriteTargetListUD() adds a
>> whole-row-var to the target list when the UPDATE / DELETE target is a
>> foreign table *and* there is a row trigger on the table.  postgres_fdw
>> sees that and constructs the query to fetch all columns.
>>
>> So, the only problem here is the optimizing away of storing all columns,
>> which the Mochizuki-san's patch seems enough to fix.
>
> Amit-san, Tom,
> Thanks for the comments.
>
> I checked other scenario. If a foreign table has AFTER trigger, remote update
> query must return all columns and these cases are added at
> deparseReturningList
> and covered by following existing test cases.
>
> EXPLAIN (verbose, costs off)
> UPDATE rem1 set f2 = '';          -- can't be pushed down
>                                   QUERY PLAN
> -------------------------------------------------------------------------------
>
>  Update on public.rem1
>    Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1 RETURNING
> f1, f2
>    ->  Foreign Scan on public.rem1
>          Output: f1, ''::text, ctid, rem1.*
>          Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
> (5 rows)

Ah, I had missed the AFTER triggers case, which seems to be working fine
as you've shown here.

Thanks,
Amit