On 19 Mar 2002, Greg Copeland wrote: > On Tue, 2002-03-19 at 07:46, mlw wrote: > [snip] > > > Right now, the function manager can only return one value, or one set of values > > for a column. It should be possible, but require a lot of research, to enable > > the function manager to return a set of rows. If we could get that working, it > > could be fairly trivial to implement a cache as a contrib project. It would > > work something like this: > > > > select querycache("select * from mytable where foo='bar') ; > > Interesting concept...but how would you know when the cache has become > dirty? That would give you a set of rows...but I don't understand what > would let you know your result set is invalid? > > Perhaps: select querycache( foobar_event, "select * from my table where > foo='bar'" ) ; would automatically create a listen for you??
Personally, I think this method of providing query caching is very messy. Why not just implement this along side the system relation cache? This maybe be slightly more time consuming but it will perform better and will be able to take advantage of Postgres's current MVCC. There would be three times when the cache would be interacted with 1) add a new result set ExecRetrieve() would need to be modified to handle a prepare-for-cache-update kind of feature. This would involve adding the tuple table slot data into a linked list. At the end of processing/transaction and if the query was successfuly, the prepare-for-cache-update list could be processed by AtCommit_Cache() (called from CommitTransaction()) and the shared cache updated. 2) attempt to get result set from cache Before planning in postgres.c, test if the query will produce an already cached result set. If so, send the data off from cache. 3) modification of underlying heap Like (1), produce a list inside the executor (ExecAppend(), ExecDelete(), ExecReplace() -> RelationInvalidateHeapTuple() -> PrepareForTupleInvalidation()) which gets processed by AtEOXactInvalidationMessages(). This results in the affected entries being purged. --- I'm not sure that cached results is a direction postgres need move in. But if it does, I think this a better way to do it (given that I may have overlooked something) than modifying the function manager (argh!). Gavin ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org