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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
