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

On 2016/11/22 4:49, Tom Lane wrote:
> Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> writes:
>> On 2016/11/10 5:17, Tom Lane wrote:
>>> I think there's a very good argument that we should just treat any inval
>>> in pg_foreign_data_wrapper as a reason to blow away the whole plan cache.
>>> People aren't going to alter FDW-level options often enough to make it
>>> worth tracking things more finely. Personally I'd put pg_foreign_server
>>> changes in the same boat, though maybe those are changed slightly more
>>> often. If it's worth doing anything more than that, it would be to note
>>> which plans mention foreign tables at all, and not invalidate those that
>>> don't. We could do that with, say, a hasForeignTables bool in
>>> PlannedStmt.

>> I agree on that point.

> OK, please update the patch to handle those catalogs that way.

Will do.

>>> That leaves the question of whether it's worth detecting table-level
>>> option changes this way, or whether we should just handle those by forcing
>>> a relcache inval in ATExecGenericOptions, as in Amit's original patch in
>>> <5702298D(dot)4090903(at)lab(dot)ntt(dot)co(dot)jp>. I kind of like that approach; that
>>> patch was short and sweet, and it put the cost on the unusual path (ALTER
>>> TABLE) not the common path (every time we create a query plan).

>> I think it'd be better to detect table-level option changes because that
>> could allow us to do more; we could avoid invalidating cached plans that
>> need not to be invalidated, by tracking the plan dependencies more
>> exactly, ie, avoid collecting the dependencies of foreign tables in a
>> plan that are in dead subqueries or excluded by constraint exclusion.
>> The proposed patch doesn't do that, though. I think updates on
>> pg_foreign_table would be more frequent, so it might be worth
>> complicating the code. But the relcache-inval approach couldn't do
>> that, IIUC.

> Why not? But the bigger picture here is that relcache inval is the
> established practice for forcing replanning due to table-level changes,
> and I have very little sympathy for inventing a new mechanism for that
> just for foreign tables. If it were worth bypassing replanning for
> changes in tables in dead subqueries, for instance, it would surely be
> worth making that happen for all table types not just foreign tables.

My point here is that FDW-option changes wouldn't affect replanning by
core; even if forcing a replan, we would have the same foreign tables
excluded by constraint exclusion, for example. So, considering updates
on pg_foreign_table to be rather frequent, it might be better to avoid
replanning for the pg_foreign_table changes to foreign tables excluded
by constraint exclusion, for example. My concern about the
relcache-inval approach is: that approach wouldn't allow us to do that,
IIUC.

Best regards,
Etsuro Fujita