Re: cache in plpgsql

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> But can you tell me how to actually check if a saved SPI plan
> references that particular catalog object or not?

This is certainly doable in principle. recordDependencyOnExpr()
contains much of the logic that would be needed -- it would need to be
modified so that the dependency info can just be put in memory and not
stored into pg_depend, but that's surely not hard. (Note that to use
it directly, we'd want to examine the parsed querytree not the plan
tree, but that isn't a problem AFAICS.)

Whether it's *practical* is another question. Cache inval events
happen often enough that speed of response to 'em is an issue.
Maybe we could use a hashtable of dependencies to avoid expensive
searches for cached plans that must be invalidated.

Another little problem is that plpgsql doesn't really have any mechanism
for invalidating cached stuff at all; it will leak memory like there's
no tomorrow if we start dropping cached subplans. plpgsql needs to be
rewritten so that everything it allocates lives in per-function memory
contexts that can be dropped when an invalidation happens.

As far as I can see a dependency-based solution is possible. It's
just a Small Matter Of Programming.
http://www.catb.org/~esr/jargon/html/S/SMOP.html

regards, tom lane