Re: Optimization for updating foreign tables in Postgres FDW

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Optimization for updating foreign tables in Postgres FDW
Date: 2014-08-29 03:58:08
Message-ID: 53FFFA50.6020007@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

(2014/08/13 12:40), Etsuro Fujita wrote:
> (2014/08/12 18:34), Shigeru Hanada wrote:
>> Issues addressed by Eitoku-san were fixed properly, but he found a bug
>> and a possible enhancement in the v2 patch.

>> * push-down check misses delete triggers
>> update_is_pushdown_safe() seems to have a bug that it misses the
>> existence of row-level delete trigger. DELETE statement executed
>> against a foreign table which has row-level delete trigger is pushed
>> down to remote, and consequently no row-level delete trigger is fired.
>
> Ah, I noticed that the current code for that is not correct. Will fix.

Done.

>> * further optimization
>> Is there any chance to consider further optimization by passing the
>> operation type (UPDATE|DELETE) of undergoing statement to
>> update_is_pushdown_safe()? It seems safe to push down UPDATE
>> statement when the target foreign table has no update trigger even it
>> has a delete trigger (of course the opposite combination would be also
>> fine).
>
> Good idea! Will improve that too.

Done.

>> * Documentation
>> The requirement of pushing down UPDATE/DELETE statements would not be
>> easy to understand for non-expert users, so it seems that there is a
>> room to enhance documentation. An idea is to define which expression
>> is safe to send to remote first (it might need to mention the
>> difference of semantics), and refer the definition from the place
>> describing the requirement of pushing-down for SELECT, UPDATE and
>> DELETE.
>
> Yeah, I also think that it would not necessarily easy for the users to
> understand which expression is safe to send. So I agree with that
> enhancement, but ISTM that it would be better to do that as a separate
> patch.

As above, I'd like to leave this as another patch.

Please find attached the updated version of the patch.

Thanks,

Best regards,
Etsuro Fujita

Attachment Content-Type Size
postgres_fdw-update-v3.patch text/x-diff 57.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2014-08-29 03:59:03 Re: Optimization for updating foreign tables in Postgres FDW
Previous Message Peter Geoghegan 2014-08-29 03:05:39 Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}