Re: invalidating cached plans

"Harald Fuchs" <use_reply_to(at)protecting(dot)net> writes
> How about using an even coarser grain? Whenever something in the
> database in question changes, blindly throw away all cached plans for
> this DB.
>

If we clearly define what is "something in database in question", we have to
trace all the objects the query will touch. There are two difficulities:

First, even if we can trace all the changes to the objects we will touch, it
is still difficult to differenciate what changes do not invalidate the plan,
what do. For instance, if random() function changes its behavior in two
ways, (1) change its returned precision, then there is no problem of our
plan; (2) change its distribution, then it might be a problem of our plan. A
fast solution to this problem is to discard all the plans once the
referencing object changes (no matter what change).

Second (as Tom says), some changes can hardly be traced. For example, we
only use function A. But function A cites function B, function B cites
function C. when C changes, how do we know that we should worry about our
plan? Maybe we not only need caller-graph, we also need callee-graph. But I
am afraid this will be a big cost. A fast solution is that we forbidden some
kind of query to be cached.

Regards,
Qingqing

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>