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

[ apologies for not responding sooner ]

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.

>> 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.

regards, tom lane