Re: Again, sorry, caching, (Tom What do you think: function

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