Re: postgres_fdw : altering foreign table not invalidating prepare statement execution plan.

On Tue, Apr 5, 2016 at 10:54 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2016/04/05 0:23, Tom Lane wrote:
>> Amit Langote <amitlangote09(at)gmail(dot)com> writes:
>>> On Mon, Apr 4, 2016 at 11:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> A related issue, now that I've seen this example, is that altering
>>>> FDW-level or server-level options won't cause a replan either. I'm
>>>> not sure there's any very good fix for that. Surely we don't want
>>>> to try to identify all tables belonging to the FDW or server and
>>>> issue relcache invals on all of them.
>>
>>> Hm, some kind of PlanInvalItem-based solution could work maybe?
>>
>> Hm, so we'd expect that whenever an FDW consulted the options while
>> making a plan, it'd have to record a plan dependency on them? That
>> would be a clean fix maybe, but I'm worried that third-party FDWs
>> would fail to get the word about needing to do this.
>
> I would imagine that that level of granularity may be a little too much; I
> mean tracking dependencies at the level of individual FDW/foreign
> table/foreign server options. I think it should really be possible to do
> the entire thing in core instead of requiring this to be made a concern of
> FDW authors. How about the attached that teaches
> extract_query_dependencies() to add a foreign table and associated foreign
> data wrapper and foreign server to invalItems. Also, it adds plan cache
> callbacks for respective caches.

Although this is a better solution than the previous one, it
invalidates the query tree along with the generic plan. Invalidating
the query tree and the generic plan required parsing the query again
and generating the plan. Instead I think, we should invalidate only
the generic plan and not the query tree. The attached patch adds the
dependencies from create_foreignscan_plan() which is called for any
foreign access. I have also added testcases to test the functionality.
Let me know your comments on the patch.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company