Re: Again, sorry, caching. - Mailing list pgsql-hackers

Greg Copeland wrote:
> 
> On Sat, 2002-03-16 at 08:36, mlw wrote:
> > Triggers and asynchronous notification are not substitutes for real hard ACID
> > complient caching. The way you suggest implies only one access model. Take
the
> > notion of a library, they have both web and application access. These should
> > both be able to use the cache.
> >
> 
> Well, obviously, you'd need to re-implement the client side cache in
> each implementation of the client.  That is a down side and I certainly
> won't argue that.  As for the "no substitute" comment, I'm guess I'll
> plead ignorance because I'm not sure what I'm missing here.  What am I
> missing that would not be properly covered by that model?

It would not be guarenteed to be up to date with the state of the database. By
implementing the cache within the database, PostgreSQL could maintain the
consistency.

> 
> > Also, your suggestion does not address the sub-select case, which I think is
> > much bigger, performance wise, and more efficient than MySQL's cache.
> 
> I'm really not sure what you mean by that.  Doesn't address it but is
> more efficient?  Maybe it's because I've not had my morning coffee
> yet... ;)

If an internal caching system can be implemented within PostgreSQL, and trust
me, I undersand what a hairball it would be with multiversion concurrency,
omplex queries such as:

select * from (select * from mytable where foo = 'bar' cacheable) as subset
where subset.col = 'value'

The 'cacheable' keyword applied to the query would mean that PostgreSQL could
keep that result set handy for later use. If mytable and that subselect always
does a table scan, no one can argue that this subquery caching could be a huge
win.

As a side note, I REALLY like the idea of a keyword for caching as apposed to
automated caching. t would allow the DBA or developer more control over
PostgreSQL's behavior, and poentially make the fature easier to implement.

> 
> >
> > This whole discussion could be moot, and this could be developed as an
> > extension, if there were a function API that could return sets of whole rows.
> >
> 
Currently a function can only return one value or a setof a single type,
implemented as one function call for each entry in a set. If there could be a
function interface which could return a row, and multiple rows similar to the
'setof' return, that would be very cool. That way caching can be implemented
as:

select * from pgcache('select * from mytable where foo='bar') as subset where
subset.col = 'value';