PostgreSQL should invalidate a cached query plan when one of the objects
the plan depends upon is modified.
This is the common case of a more general problem: a query plan depends
on various parts of the environment at plan-creation time. That
environment includes the definitions of database objects, but also GUC
variables (most importantly search_path, but also optimizer-tuning
variables for example), the state of database statistics, and so on.
I'll leave resolution of the more general problem to someone else -- I
think if we can manage to invalidate plans automatically when dependent
objects change, that's better than nothing.
Implementation sketch:
- when creating a plan, allow the caller to specify whether dependencies
should be tracked or not; we want to track dependencies for long-lived
plans like cached plans created by PL/PgSQL, named PREPARE plans (both
protocol-level and via SQL), and so forth. We needn't track dependencies
for exec_simple_query(), and so on.
- to install dependencies for a plan, walk the plan's tree and remember
the OIDs of any system objects it references. Both cached plans and
their dependencies are backend-local.
- if we receive a shared invalidation message for a relation referenced
by a plan, mark the plan as invalid (a new boolean flag associated with
a prepared Plan). If the sinval queue overflows, mark all plans as
invalid (well, all the plans we're tracking dependencies for, anyway). I
haven't looked too closely at whether the existing sinval message types
will be sufficient for invalidating cached plans; some modifications
might be needed.
- 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.
Comments welcome.
-Neil