Re: BEFORE UPDATE trigger on postgres_fdw table not work

Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> On 2019/05/27 10:52, Shohei Mochizuki wrote:
>> I noticed returning a modified record in a row-level BEFORE UPDATE trigger
>> on postgres_fdw foreign tables do not work. Attached patch fixes this issue.
>> This is because current fdw code adds only columns to RemoteSQL that were
>> explicitly targets of the UPDATE as follows.

> Yeah.  So, the trigger execution correctly modifies the existing tuple
> fetched from the remote server, but those changes are then essentially
> discarded by postgres_fdw, that is, postgresExecForeignModify().

> ... Also, in the worst case, we'll end
> up generating new query for every row being changed, because the trigger
> may change different columns for different rows based on some condition.

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.

            regards, tom lane