Re: Again, sorry, caching, (Tom What do you think: function manager) - Mailing list pgsql-hackers

Jeff Davis wrote:
> 
> > Yes...I was thinking that a generic library interface with a nice design
> > pattern might meet this need rather well.  Done properly, I think we can
> > make it where all that, more or less, would be needed is application
> > hooks which accept the result set to be cached and a mechanism to signal
> > invalidation of the current cache....obviously that's not an exhaustive
> > list... :)
> 
> A library implies that the application is running long enough to actually
> hear the notofication. Web apps start up, read from the database, and before
> any cache is needed they're done and the next one starts up, reading again
> from the database. Only currently open connections receive the notification.
> 
> I think that you do need an entire layer... but that's not a bad thing
> necessarily. Have a daemon that stays connected for a long time and when a
> notification arrives, rewrite the cache (or mark it dirty). Other
> applications can read data from static files or shared memory, or even
> another communication socket with your daemon.
> 
> There may be some way around running a daemon, so if you have a better
> solution please let me know.
> 
> I think I am in favor of client caching in general, but "mlw" (sorry, I can't
> find your real name in the emails at hand) makes some good points. The most
> important one is that we don't want to change application architectures on
> everyone. It's easy if you just have to add "iscachable" to a query, it's
> hard if you have to start writing against a different set of routines (to
> grab from your client cache rather than a database).
> 
> However, I am perfectly happy writing a client-side cache or using temp
> tables to store a result set. I also don't care that much if someone chooses
> PostgreSQL for their website (unless I'm responsible for it's success in some
> way :) ). That's me personally, if you want to attract more users from mysql,
> "iscachable" is very likely an attractive feature.

I was thinking about this. There seems to be a consensus that caching means no
ACID compliance. And everyone seems to think it needs to be limited. We can
implement a non-ACID cache as a contrib function with some work to the function
manager.

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') ;

This does two things that I would like to see: The ability to cache subselects
independent of the full query. The ability to control which queries get cached.

If we can get this row functionality in the function manager for 7.3, we could
then implement MANY MANY enterprise level functionalities. Remote queries,
query caching, external tables, etc. as contrib projects rather than full blown
modifications to PostgreSQL.