Neil Conway <neilc@samurai.com> writes:
> PostgreSQL should invalidate a cached query plan when one of the objects
> the plan depends upon is modified.
Agreed.
> Implementation sketch:
I would like to see this folded together with creation of a centralized
plan caching module. We currently have ad-hoc plan caches in
ri_triggers.c, plpgsql, prepare.c, and probably other places. There
is no good reason to keep reinventing that wheel, especially not given
that plan invalidation raises the complexity of the wheel by a considerable
amount.
> - when creating a plan, allow the caller to specify whether dependencies
> should be tracked or not;
I would prefer not to tie this behavior to plan creation per se, but to
plan caching. And in a cached plan there is no "don't track" option.
HOWEVER, see next comment ...
> - to install dependencies for a plan, walk the plan's tree and remember
> the OIDs of any system objects it references.
The difficulty with this after-the-fact approach is that the finished
plan tree may contain no reference to some objects that it in fact
depends on. SQL functions that have been inlined are the classic
example, but consider also the idea that a plan may have been made on
the strength of a table constraint (see nearby thread about partitioning)
and needs to be invalidated if that constraint goes away.
One possible approach is to do the invalidation on a sufficiently coarse
grain that we don't care. For example, I would be inclined to make any
change in a table's schema invalidate all plans that use that table at
all; that would then subsume the constraint problem for instance. This
doesn't solve the inlined function problem however.
For inlined functions, the only answer I see is for the planner to
somehow decorate the plan tree with a list of things it consulted
even though they might not be directly referenced in the finished
plan.
> Both cached plans and their dependencies are backend-local.
Agreed.
> - it is the responsibility of the call site managing the prepared plan
> to check whether a previously prepared plan is invalid or not -- and to
> take the necessary steps to replan it when needed.
Again, I'd rather see that folded into a central plan cache mechanism.
regards, tom lane
