Re: Syscache/relcache invalidation event callbacks

On Mon, Apr 29, 2002 at 03:43:30PM -0400, Tom Lane wrote:
> I'm planning to add a mechanism to backend/utils/cache/inval.c that will
> allow additional modules to register to get notification of syscache and
> relcache invalidation events. Right now, only the syscache and relcache
> get told about it --- but there's no reason we couldn't call additional
> routines here.
>
> The immediate thing I want this for is so that the namespace.c routines
> can safely use a cached list of OIDs for accessible namespaces. Without
> this cache, we'll have to repeatedly look up pg_namespace entries and
> check their USAGE privilege bits. That strikes me as a fairly expensive
> operation to have to do for each table, function, and operator name in
> every query, when in practice the results aren't going to be changing
> very often. I'd like to only do the lookups when search_path changes
> or we receive a notification of an update in pg_namespace.
>
> This mechanism would also allow us to solve the plpgsql-cached-plans
> problem that keeps coming up. If plpgsql registers a callback routine
> for relcache events, then it'll get a notification every time something
> happens to a relation schema. It could search its cached plans to see
> if there is any reference to that relation OID. If so, blow away the

IMHO is clean call a callback if a change is relavant for cached
plan -- it means if Oid is used in plan.

> cached plan. (Or at least prevent it from being used again. There'd
> be some possibility of this happening for a plan that's currently in
> use, I believe, so you'd probably need to avoid discarding the plan
> until the active call is done.)
>
> We'll have the same problem with the PREPAREd-plan feature that Neil is
> working on, so it seems like time to do this. Comments?

Wanted! It's very good idea.

I have a question, how I will know how changes are relevant for my
query plan? IMHO is needful some hight-level API, like

list = ExtractQueryPlanOids( plan );
reg = RegisterOidsCallback( list, mycallback, mycallbackdata );

and now I can do:

mycallback(reg, mycallbackdata)
{
remove_plan_from_my_cache( (MyKey *) mycallbackdata );
UnregisterOidsCallback(reg);
}

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz