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
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?

			regards, tom lane

Mon, 29 Apr 2002 15:43:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Tue, 30 Apr 2002 10:29:47 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: Syscache/relcache invalidation event callbacks
Karel Zak <zakkr@zf.jcu.cz> writes:
>  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 );

Yes, some kind of routine to extract all the referenced relation OIDs
in a plan tree would be a good idea.  I can provide that.  The inval
callback just tells you the OID of the relation that got flushed; it's
up to you to get from there to the plans you need to rebuild.  Perhaps
a hash table would work well.

BTW, the inval callback had better just mark the plans invalid, not
try to rebuild them right away.  I don't think it's safe to try to
do more database accesses while we're in the relcache invalidation
path.  "Rebuild plan on next attempted use" seems like a better idea.

			regards, tom lane

Tue, 30 Apr 2002 09:43:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Syscache/relcache invalidation event callbacks 
On Tue, Apr 30, 2002 at 09:43:29AM -0400, Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.cz> writes:
> >  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 );
> 
> Yes, some kind of routine to extract all the referenced relation OIDs

 The relations or others things like operators, functions, etc. Right?

> in a plan tree would be a good idea.  I can provide that.  The inval
> callback just tells you the OID of the relation that got flushed; it's
> up to you to get from there to the plans you need to rebuild.  Perhaps
> a hash table would work well.

 There must be possible define some callback specific data too, the
 callback maybe will search query in some own specific cache and it
 require some key.
 
> BTW, the inval callback had better just mark the plans invalid, not
> try to rebuild them right away.  I don't think it's safe to try to

 Hmm, it can depend on action, I can imagine:

 DROP TABLE tab;

 ERROR: mycallback(): can't rebuild the query used in PL/SQL function
        'xyz'. Please drop this function first.
  
 ...table drop failed.
  
 This is maybe not possible implement now, but it's ideal conception
 :-)
 
> do more database accesses while we're in the relcache invalidation
> path.  "Rebuild plan on next attempted use" seems like a better idea.

 Agree. It means store to cache query string too (I not sure if it's
 used in current qcache, but it's simple).

 There can be one query cache and one cached planns checking only, for RI, 
 SPI, PL/SQL, PREPARE/EXECUTE. Or not? I think implement 4x same things is 
 terrible.

        Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Tue, 30 Apr 2002 16:26:55 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: Syscache/relcache invalidation event callbacks
Karel Zak <zakkr@zf.jcu.cz> writes:
>  There must be possible define some callback specific data too, the
>  callback maybe will search query in some own specific cache and it
>  require some key.

Yeah, I have it set up similarly to on_proc_exit callbacks: when you
register the callback function you can also provide a Datum (typically
a pointer).  But I'm not sure that there's any real value in this.
AFAICS, such callbacks are always going to be interested in looking
through a cache data structure of some kind, and so there will always
be global variables pointing to what they need to get to.  (I'm
envisioning *one* callback function handling a whole cache, not a
callback for each entry!)
 
>> BTW, the inval callback had better just mark the plans invalid, not
>> try to rebuild them right away.  I don't think it's safe to try to

>  Hmm, it can depend on action, I can imagine:

>  DROP TABLE tab;

>  ERROR: mycallback(): can't rebuild the query used in PL/SQL function
>         'xyz'. Please drop this function first.

>  ...table drop failed.

Nope, that is NOT okay.

(1) The drop might have been done by some other backend.

(2) Even if it was your own backend, the drop is already committed
    by the time you hear about it.

You invalidate the plan, and then when and if that function is called
again, you'll flag an error as a natural result of trying to recompute
the plan.  No shortcuts.

(If we do want to prevent a drop in cases like this, it has to be done
via the kind of dependency mechanism that Rod is working on.)

>  There can be one query cache and one cached planns checking only, for RI, 
>  SPI, PL/SQL, PREPARE/EXECUTE. Or not?

Hmm.  Seems like it might be a good idea, but I'm not certain that all
of these have exactly the same requirements.  If they can all share one
cache that'd definitely be a win.

			regards, tom lane

Tue, 30 Apr 2002 10:39:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Syscache/relcache invalidation event callbacks 
On Tue, Apr 30, 2002 at 10:39:38AM -0400, Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.cz> writes:
> >  There must be possible define some callback specific data too, the
> >  callback maybe will search query in some own specific cache and it
> >  require some key.
> 
> Yeah, I have it set up similarly to on_proc_exit callbacks: when you
> register the callback function you can also provide a Datum (typically
> a pointer).  But I'm not sure that there's any real value in this.
> AFAICS, such callbacks are always going to be interested in looking
> through a cache data structure of some kind, and so there will always
> be global variables pointing to what they need to get to.  (I'm
> envisioning *one* callback function handling a whole cache, not a
> callback for each entry!)

 I understand.
  
> >> BTW, the inval callback had better just mark the plans invalid, not
> >> try to rebuild them right away.  I don't think it's safe to try to
> 
> >  Hmm, it can depend on action, I can imagine:
> 
> >  DROP TABLE tab;
> 
> >  ERROR: mycallback(): can't rebuild the query used in PL/SQL function
> >         'xyz'. Please drop this function first.
> 
> >  ...table drop failed.
> 
> Nope, that is NOT okay.

 It was dream.

> (If we do want to prevent a drop in cases like this, it has to be done
> via the kind of dependency mechanism that Rod is working on.)

 It's good that someone works on dreams:-)

> >  There can be one query cache and one cached planns checking only, for RI, 
> >  SPI, PL/SQL, PREPARE/EXECUTE. Or not?
> 
> Hmm.  Seems like it might be a good idea, but I'm not certain that all
> of these have exactly the same requirements.  If they can all share one
> cache that'd definitely be a win.

 All they needs save query planns -- IMHO it's enough. It's always
 couple "plan-identificator" + "plan-tree".

 There is not problem create branchs of the cache, separate for SPI,
 PL/SQL ..etc. But the _routines_ and API will _same_.

 branch = qCache_CreateBranch("PL/SQL");
 qCache_AddPlan(branch, plan, hashkey);

        Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Tue, 30 Apr 2002 17:22:10 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: Syscache/relcache invalidation event callbacks