Thread: cache in plpgsql
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 ?
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 #
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 >
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 #
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 >
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 #
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 # >
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 #
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
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
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
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 #
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 #
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 thatsave plan to query cache. I think it's pretty badidea use for samethings more separate solutions. For example see RI stuff (triggeres) --it's perfect adept for PREPARE/EXECUTE query cache instead the currentRI solution that save plans in own hash table. I think we can addsupportfor 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/