Hi,
there's a topic that comes up from time to time on the lists, the problem
that pgsql functions get planned only once and thereafter the same query
plan is used until server shutdown or explicit recreation of the function.
I'd like to implement a way of automatic function replanning. I can think of
two possible approaches.
1. in a more general way: extend pg_proc by an interval column "ttl" or "replanAfter" and add a function declaration
attributeto the parser "... STRICT STABLE REPLAN AFTER '3 days'::interval"
+ general approach, every language that can pre-compute plans can use this feature, the check can be done in one
placefor all languages
- in fact only plpsql can do that at the moment (right?) and there is no other candidate for something similar at
themoment
- catalog change that also requires interval to be specially treated while bootstrapping
- catalog would grow, every function would have the attribute though it is only applicable for a very low number of
functions,let alone the number of functions that would actually use it in a typical installation
2. use the #option feature of plpgsql. Add the possibility to specify #option ttl '3 days' or #option replan-after
'1day 2 hours'
+ Minor changes, changes only local to plpgsql
- plpgsql specific solution
- is #option documented at all? Should it stay "unofficial"? If so, why?
3. (not automatic) add a statement that makes pgsql forget the plan and compute a new one when the function gets
calledagain. "The user should rather use cron for doing maintenance tasks."
What do you think? Any other ideas?
Joachim