

as new know plpgsql has special cache which remember too long (event
non-existing tables (i mean old oid)) so i suggest to create same function
only in plpgsql which would clear this cache, or sth like this ?

for ie, where i drop table i would do plpgsql_clear_cache ();
and when i will create one more time table with this same name plpgsql
will not remeber wrong oid

possible ?


Tue, 30 Dec 2003 23:35:11 +0100 (CET)
ivan <iv@psycho.pl>
cache in plpgsql
ivan wrote:

>as new know plpgsql has special cache which remember too long (event
>non-existing tables (i mean old oid)) so i suggest to create same function
>only in plpgsql which would clear this cache, or sth like this ?
>
>for ie, where i drop table i would do plpgsql_clear_cache ();
>and when i will create one more time table with this same name plpgsql
>will not remeber wrong oid
>
>possible ?
>  
>

You obviously did not bother to search the archives on this.

This will not solve the problem since the "cache" you're talking about 
is per backend local memory. So if one backend modifies the schema, how 
does it cause all other to forgt? Since the same problem exists in 
general for everything that uses SPI, the solution lies in there.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #




Wed, 31 Dec 2003 07:50:16 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: cache in plpgsql

why all backend can not using one cache, which would be always
in real state ... or i can just clear only my cache, at first
(if i know that this relation could has another oid)
and then normal using relations ?

or ... just turn off cache, because its strange to has possible
using drop, create etc in function, but using only EXECUTE ..

there must be same solution .. no ?


On Wed, 31 Dec 2003, Jan Wieck wrote:

> ivan wrote:
>
> >as new know plpgsql has special cache which remember too long (event
> >non-existing tables (i mean old oid)) so i suggest to create same function
> >only in plpgsql which would clear this cache, or sth like this ?
> >
> >for ie, where i drop table i would do plpgsql_clear_cache ();
> >and when i will create one more time table with this same name plpgsql
> >will not remeber wrong oid
> >
> >possible ?
> >
> >
>
> You obviously did not bother to search the archives on this.
>
> This will not solve the problem since the "cache" you're talking about
> is per backend local memory. So if one backend modifies the schema, how
> does it cause all other to forgt? Since the same problem exists in
> general for everything that uses SPI, the solution lies in there.
>
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

Wed, 31 Dec 2003 14:22:07 +0100 (CET)
ivan <iv@psycho.pl>
Re: cache in plpgsql
ivan wrote:
> why all backend can not using one cache, which would be always

Variable sized shared memory with garbage collection for SPI plans?

> in real state ... or i can just clear only my cache, at first
> (if i know that this relation could has another oid)
> and then normal using relations ?

As said, that is not sufficient. The user who does the DDL statement can 
as well reconnect to the database to recompile all saved plans. It is 
the 200 persistent PHP DB connections that suffer from not finding the 
index any more.

> 
> or ... just turn off cache, because its strange to has possible
> using drop, create etc in function, but using only EXECUTE ..

Do you have any numbers about how that would affect performance?


Jan

> 
> there must be same solution .. no ?
> 
> 
> On Wed, 31 Dec 2003, Jan Wieck wrote:
> 
>> ivan wrote:
>>
>> >as new know plpgsql has special cache which remember too long (event
>> >non-existing tables (i mean old oid)) so i suggest to create same function
>> >only in plpgsql which would clear this cache, or sth like this ?
>> >
>> >for ie, where i drop table i would do plpgsql_clear_cache ();
>> >and when i will create one more time table with this same name plpgsql
>> >will not remeber wrong oid
>> >
>> >possible ?
>> >
>> >
>>
>> You obviously did not bother to search the archives on this.
>>
>> This will not solve the problem since the "cache" you're talking about
>> is per backend local memory. So if one backend modifies the schema, how
>> does it cause all other to forgt? Since the same problem exists in
>> general for everything that uses SPI, the solution lies in there.
>>
>>
>> Jan
>>
>> --
>>
>> #======================================================================#
>> # It's easier to get forgiveness for being wrong than for being right. #
>> # Let's break this rule - forgive me.                                  #
>> #================================================== JanWieck@Yahoo.com #
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>       subscribe-nomail command to majordomo@postgresql.org so that your
>>       message can get through to the mailing list cleanly
>>


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Wed, 31 Dec 2003 08:32:16 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: cache in plpgsql


but , all in all, do you think that now it is ok ?

On Wed, 31 Dec 2003, Jan Wieck wrote:

> ivan wrote:
> > why all backend can not using one cache, which would be always
>
> Variable sized shared memory with garbage collection for SPI plans?
>
> > in real state ... or i can just clear only my cache, at first
> > (if i know that this relation could has another oid)
> > and then normal using relations ?
>
> As said, that is not sufficient. The user who does the DDL statement can
> as well reconnect to the database to recompile all saved plans. It is
> the 200 persistent PHP DB connections that suffer from not finding the
> index any more.
>
> >
> > or ... just turn off cache, because its strange to has possible
> > using drop, create etc in function, but using only EXECUTE ..
>
> Do you have any numbers about how that would affect performance?
>
>
> Jan
>
> >
> > there must be same solution .. no ?
> >
> >
> > On Wed, 31 Dec 2003, Jan Wieck wrote:
> >
> >> ivan wrote:
> >>
> >> >as new know plpgsql has special cache which remember too long (event
> >> >non-existing tables (i mean old oid)) so i suggest to create same function
> >> >only in plpgsql which would clear this cache, or sth like this ?
> >> >
> >> >for ie, where i drop table i would do plpgsql_clear_cache ();
> >> >and when i will create one more time table with this same name plpgsql
> >> >will not remeber wrong oid
> >> >
> >> >possible ?
> >> >
> >> >
> >>
> >> You obviously did not bother to search the archives on this.
> >>
> >> This will not solve the problem since the "cache" you're talking about
> >> is per backend local memory. So if one backend modifies the schema, how
> >> does it cause all other to forgt? Since the same problem exists in
> >> general for everything that uses SPI, the solution lies in there.
> >>
> >>
> >> Jan
> >>
> >> --
> >>
> >> #======================================================================#
> >> # It's easier to get forgiveness for being wrong than for being right. #
> >> # Let's break this rule - forgive me.                                  #
> >> #================================================== JanWieck@Yahoo.com #
> >>
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 3: if posting/reading through Usenet, please send an appropriate
> >>       subscribe-nomail command to majordomo@postgresql.org so that your
> >>       message can get through to the mailing list cleanly
> >>
>
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Wed, 31 Dec 2003 17:22:57 +0100 (CET)
ivan <iv@psycho.pl>
Re: cache in plpgsql
ivan wrote:
> 
> but , all in all, do you think that now it is ok ?

No, I don't. I just prefer complete solutions over patchwork.


Jan

> 
> On Wed, 31 Dec 2003, Jan Wieck wrote:
> 
>> ivan wrote:
>> > why all backend can not using one cache, which would be always
>>
>> Variable sized shared memory with garbage collection for SPI plans?
>>
>> > in real state ... or i can just clear only my cache, at first
>> > (if i know that this relation could has another oid)
>> > and then normal using relations ?
>>
>> As said, that is not sufficient. The user who does the DDL statement can
>> as well reconnect to the database to recompile all saved plans. It is
>> the 200 persistent PHP DB connections that suffer from not finding the
>> index any more.
>>
>> >
>> > or ... just turn off cache, because its strange to has possible
>> > using drop, create etc in function, but using only EXECUTE ..
>>
>> Do you have any numbers about how that would affect performance?
>>
>>
>> Jan
>>
>> >
>> > there must be same solution .. no ?
>> >
>> >
>> > On Wed, 31 Dec 2003, Jan Wieck wrote:
>> >
>> >> ivan wrote:
>> >>
>> >> >as new know plpgsql has special cache which remember too long (event
>> >> >non-existing tables (i mean old oid)) so i suggest to create same function
>> >> >only in plpgsql which would clear this cache, or sth like this ?
>> >> >
>> >> >for ie, where i drop table i would do plpgsql_clear_cache ();
>> >> >and when i will create one more time table with this same name plpgsql
>> >> >will not remeber wrong oid
>> >> >
>> >> >possible ?
>> >> >
>> >> >
>> >>
>> >> You obviously did not bother to search the archives on this.
>> >>
>> >> This will not solve the problem since the "cache" you're talking about
>> >> is per backend local memory. So if one backend modifies the schema, how
>> >> does it cause all other to forgt? Since the same problem exists in
>> >> general for everything that uses SPI, the solution lies in there.
>> >>
>> >>
>> >> Jan
>> >>
>> >> --
>> >>
>> >> #======================================================================#
>> >> # It's easier to get forgiveness for being wrong than for being right. #
>> >> # Let's break this rule - forgive me.                                  #
>> >> #================================================== JanWieck@Yahoo.com #
>> >>
>> >>
>> >>
>> >>
>> >> ---------------------------(end of broadcast)---------------------------
>> >> TIP 3: if posting/reading through Usenet, please send an appropriate
>> >>       subscribe-nomail command to majordomo@postgresql.org so that your
>> >>       message can get through to the mailing list cleanly
>> >>
>>
>>
>> --
>> #======================================================================#
>> # It's easier to get forgiveness for being wrong than for being right. #
>> # Let's break this rule - forgive me.                                  #
>> #================================================== JanWieck@Yahoo.com #
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Wed, 31 Dec 2003 13:36:22 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: cache in plpgsql

may be postgres can use same way like triggers working,
and when relation is droping ( what is equal to delete from pg_class)
there could be something like trigger after .. which can
waiting for CREATE or DROP command, and then clean-up cache,
(for each backend).
This could be for example same message, not just trigger
(i said trigger only to show scheme of acction)

ehheh this idea is also wrong ?

On Wed, 31 Dec 2003, Jan Wieck wrote:

> ivan wrote:
> > why all backend can not using one cache, which would be always
>
> Variable sized shared memory with garbage collection for SPI plans?
>
> > in real state ... or i can just clear only my cache, at first
> > (if i know that this relation could has another oid)
> > and then normal using relations ?
>
> As said, that is not sufficient. The user who does the DDL statement can
> as well reconnect to the database to recompile all saved plans. It is
> the 200 persistent PHP DB connections that suffer from not finding the
> index any more.
>
> >
> > or ... just turn off cache, because its strange to has possible
> > using drop, create etc in function, but using only EXECUTE ..
>
> Do you have any numbers about how that would affect performance?
>
>
> Jan
>
> >
> > there must be same solution .. no ?
> >
> >
> > On Wed, 31 Dec 2003, Jan Wieck wrote:
> >
> >> ivan wrote:
> >>
> >> >as new know plpgsql has special cache which remember too long (event
> >> >non-existing tables (i mean old oid)) so i suggest to create same function
> >> >only in plpgsql which would clear this cache, or sth like this ?
> >> >
> >> >for ie, where i drop table i would do plpgsql_clear_cache ();
> >> >and when i will create one more time table with this same name plpgsql
> >> >will not remeber wrong oid
> >> >
> >> >possible ?
> >> >
> >> >
> >>
> >> You obviously did not bother to search the archives on this.
> >>
> >> This will not solve the problem since the "cache" you're talking about
> >> is per backend local memory. So if one backend modifies the schema, how
> >> does it cause all other to forgt? Since the same problem exists in
> >> general for everything that uses SPI, the solution lies in there.
> >>
> >>
> >> Jan
> >>
> >> --
> >>
> >> #======================================================================#
> >> # It's easier to get forgiveness for being wrong than for being right. #
> >> # Let's break this rule - forgive me.                                  #
> >> #================================================== JanWieck@Yahoo.com #
> >>
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 3: if posting/reading through Usenet, please send an appropriate
> >>       subscribe-nomail command to majordomo@postgresql.org so that your
> >>       message can get through to the mailing list cleanly
> >>
>
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
>

Thu, 1 Jan 2004 17:04:16 +0100 (CET)
ivan <iv@psycho.pl>
Re: cache in plpgsql
ivan wrote:

>may be postgres can use same way like triggers working,
>and when relation is droping ( what is equal to delete from pg_class)
>there could be something like trigger after .. which can
>waiting for CREATE or DROP command, and then clean-up cache,
>(for each backend).
>This could be for example same message, not just trigger
>(i said trigger only to show scheme of acction)
>
>ehheh this idea is also wrong ?
>

Ivan,

I really appreciate that you are thinking about this problem. But you 
keep talking about it as if this would be some sort of shared buffer 
cache. Do you know what a saved SPI execution plan is?

Your idea is neither bad nor new, there actually is a so called system 
cache invalidation event propagated to every backend in the case of 
catalog changes, so that it can purge it's syscache. But can you tell me 
how to actually check if a saved SPI plan references that particular 
catalog object or not?


Jan

>
>On Wed, 31 Dec 2003, Jan Wieck wrote:
>
>  
>
>>ivan wrote:
>>    
>>
>>>why all backend can not using one cache, which would be always
>>>      
>>>
>>Variable sized shared memory with garbage collection for SPI plans?
>>
>>    
>>
>>>in real state ... or i can just clear only my cache, at first
>>>(if i know that this relation could has another oid)
>>>and then normal using relations ?
>>>      
>>>
>>As said, that is not sufficient. The user who does the DDL statement can
>>as well reconnect to the database to recompile all saved plans. It is
>>the 200 persistent PHP DB connections that suffer from not finding the
>>index any more.
>>
>>    
>>
>>>or ... just turn off cache, because its strange to has possible
>>>using drop, create etc in function, but using only EXECUTE ..
>>>      
>>>
>>Do you have any numbers about how that would affect performance?
>>
>>
>>Jan
>>
>>    
>>
>>>there must be same solution .. no ?
>>>
>>>
>>>On Wed, 31 Dec 2003, Jan Wieck wrote:
>>>
>>>      
>>>
>>>>ivan wrote:
>>>>
>>>>        
>>>>
>>>>>as new know plpgsql has special cache which remember too long (event
>>>>>non-existing tables (i mean old oid)) so i suggest to create same function
>>>>>only in plpgsql which would clear this cache, or sth like this ?
>>>>>
>>>>>for ie, where i drop table i would do plpgsql_clear_cache ();
>>>>>and when i will create one more time table with this same name plpgsql
>>>>>will not remeber wrong oid
>>>>>
>>>>>possible ?
>>>>>
>>>>>
>>>>>          
>>>>>
>>>>You obviously did not bother to search the archives on this.
>>>>
>>>>This will not solve the problem since the "cache" you're talking about
>>>>is per backend local memory. So if one backend modifies the schema, how
>>>>does it cause all other to forgt? Since the same problem exists in
>>>>general for everything that uses SPI, the solution lies in there.
>>>>
>>>>
>>>>Jan
>>>>
>>>>--
>>>>
>>>>#======================================================================#
>>>># It's easier to get forgiveness for being wrong than for being right. #
>>>># Let's break this rule - forgive me.                                  #
>>>>#================================================== JanWieck@Yahoo.com #
>>>>
>>>>
>>>>
>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>>>      subscribe-nomail command to majordomo@postgresql.org so that your
>>>>      message can get through to the mailing list cleanly
>>>>
>>>>        
>>>>
>>--
>>#======================================================================#
>># It's easier to get forgiveness for being wrong than for being right. #
>># Let's break this rule - forgive me.                                  #
>>#================================================== JanWieck@Yahoo.com #
>>
>>    
>>


-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #




Thu, 01 Jan 2004 21:50:46 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: cache in plpgsql
Jan Wieck <JanWieck@Yahoo.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

Thu, 01 Jan 2004 22:25:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cache in plpgsql 
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.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.

Everyone seems to look at it as a PL/pgSQL specific problem. It is not!

The very same problem with cached plans exists for everything that uses 
SPI. Referential integrity is a good example, where we lately had a 
report from someone that dropping an index that was in fact not required 
for the constraint broke a cached plan. PL/Tcl has the ability to save 
prepared plans too.

Thus, this invalidation and recompilation of plans has to happen inside 
of SPI_execp() I'd say. Imagine SPI_prepare() would save the original 
query along with the parameter types, and the execution plan itself is 
stored in a sub memory context (and this separation is kept during 
SPI_saveplan()). Also we have a hash table holding all the dependencies 
with pointers to these plans, so that cache invalidation can quickly set 
a "recompile" flag in all the plans that depend on a dropped object. Now 
if SPI_execp() is called for a plan that is marked "recompile", all it 
has to do is remove all references for this plan from the hashtable, 
throw away the sub memory context, recompile the plan and store new 
dependencies in the hashtable. Note, that this whole process would only 
occur at the moment where the current system errors out with a rather 
cryptic error message.

This "hashtable" of dependencies would contain linked lists. The lookup 
key at the time of syscache invalidation is the object type and id. And 
each of this can be referenced by any number of plans.

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

As said, the idea is neither bad, nor new. And please let's not forget 
to add temp object detection into the dependency collector so that SPI 
automagically will handle temp tables used in PL/pgSQL by NOT storing 
prepared plans at all. I'm not sure, do we have a TODO item for this?


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Fri, 02 Jan 2004 11:10:42 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: cache in plpgsql
Jan Wieck <JanWieck@Yahoo.com> writes:
> Tom Lane wrote:
>> 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.

> Everyone seems to look at it as a PL/pgSQL specific problem. It is not!

No, of course not, but plpgsql has issues of its own that (IMHO) should
be solved along with the SPI-level problem.

> As said, the idea is neither bad, nor new. And please let's not forget 
> to add temp object detection into the dependency collector so that SPI 
> automagically will handle temp tables used in PL/pgSQL by NOT storing 
> prepared plans at all.

Why shouldn't we cache plans for temp tables?  They are good as long as
the temp table exists.  AFAICS the same dependency mechanism will work
fine for temp and regular tables.

			regards, tom lane

Fri, 02 Jan 2004 12:21:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cache in plpgsql 
Tom Lane wrote:

> Jan Wieck <JanWieck@Yahoo.com> writes:
>> Tom Lane wrote:
>>> 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.
> 
>> Everyone seems to look at it as a PL/pgSQL specific problem. It is not!
> 
> No, of course not, but plpgsql has issues of its own that (IMHO) should
> be solved along with the SPI-level problem.

Not sure what you mean by that. Is it the execution plan shortcut stuff 
for simple expressions (you know, the faked econtext to evaluate just a 
function call) that you want to move into SPI as well?

> 
>> As said, the idea is neither bad, nor new. And please let's not forget 
>> to add temp object detection into the dependency collector so that SPI 
>> automagically will handle temp tables used in PL/pgSQL by NOT storing 
>> prepared plans at all.
> 
> Why shouldn't we cache plans for temp tables?  They are good as long as
> the temp table exists.  AFAICS the same dependency mechanism will work
> fine for temp and regular tables.

Good point. So you mean to call the SPI functionality to mark plans for 
recompile at temp object destruction as well.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Fri, 02 Jan 2004 14:31:58 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: cache in plpgsql
Jan Wieck <JanWieck@Yahoo.com> writes:
> Tom Lane wrote:
>> No, of course not, but plpgsql has issues of its own that (IMHO) should
>> be solved along with the SPI-level problem.

> Not sure what you mean by that.

I'm referring to the fact that plpgsql's internal data structures are
all built with malloc and cannot be effectively reclaimed when the
function definition is invalidated.  I'd also like to get rid of its
ad-hoc method of detecting function definition changes (viz, looking
at the pg_proc row's xmin) in favor of hooking it into the same cache
invalidation mechanism as SPI would be using.

These are perhaps not essential changes, but they should happen
eventually.

>> Why shouldn't we cache plans for temp tables?  They are good as long as
>> the temp table exists.  AFAICS the same dependency mechanism will work
>> fine for temp and regular tables.

> Good point. So you mean to call the SPI functionality to mark plans for 
> recompile at temp object destruction as well.

I think it would be difficult to avoid.  Temp objects are not very magic
in themselves, they just live in particular schemas that happen to be a
little bit magic.

			regards, tom lane

Fri, 02 Jan 2004 15:45:21 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cache in plpgsql 
On Fri, Jan 02, 2004 at 12:21:22PM -0500, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> > Tom Lane wrote:
> >> 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.
> 
> > Everyone seems to look at it as a PL/pgSQL specific problem. It is not!
> 
> No, of course not, but plpgsql has issues of its own that (IMHO) should
> be solved along with the SPI-level problem.

 My original PREPARE/EXECUTE patch contained SPI_saveplan() version that
 save plan  to query cache. I  think it's pretty  bad idea use  for same
 things more separate solutions. For example see RI stuff (triggeres) --
 it's perfect adept for PREPARE/EXECUTE  query cache instead the current
 RI  solution that  save plans  in own  hash table. I  think we  can add
 support for work with query cache to SPI and use it in more places (RI,
 PL, etc.), something like SPI_saveplan_bykey().

    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

Mon, 5 Jan 2004 10:13:12 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: cache in plpgsql