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 attribute to 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 place for all languages - in fact only plpsql can do that at the moment (right?) and there is no other candidate for something similar at the moment - 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 '1 day 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 called again. "The user should rather use cron for doing maintenance tasks." What do you think? Any other ideas? Joachim ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster