--- Shane Ambler <pgsql@xxxxxxxxxxxxxxxx> wrote: > Matthew Peter wrote: > > --- Willy-Bas Loos <willybas@xxxxxxxxx> wrote: > > > >> maybe you would find "materialized views" interesting. > >> http://www.google.com/search?q=materialized+view+postgresql > >> > >> > >> On 12/1/06, Matthew Peter <survivedsushi@xxxxxxxxx> wrote: > >>> Is it possible to put an query result into memory? Like SELECT * from > >>> table WHERE > >>> [...] CACHE cache_name TIMEOUT '1 hour'::interval; So if "cache_name" > >>> exists with > >>> the same SQL statement, the result would be fetched from the cache, > >>> refreshing and > >>> updating the cache with fresh results when it expires? Reducing disk > >>> reads, query > >>> times, etc. > >>> > >>> > > > > > > That is basically the idea but talk about a headache. Too many functions and > > triggers to handle a single view none the less. > > > > Rather, why not write an function to use SELECT INTO and put the new tables in a > > schema named "cache." Drop and recreate the schema cached tables of the views > and > > wallah. Making this process cleanly abstracted into the background with 4 > additional > > words would be a beautiful thing. eg, > > > > SELECT * from table WHERE [...] CACHE cache_name TIMEOUT interval; > > > > Since it's a cache, it doesn't need to be updated until the TIMEOUT expires and > > permissions can be inherited by the VIEW that creates it, etc. > > > > Or if that is that an SQL-spec no-no? Maybe... > > > > CREATE CACHED VIEW on view_name as view_name_cache TIMEOUT interval; > > > > Oh ya. Like CREATE UNIQUE INDEX. Now querying from view_name_cache will not > effect > > querying from the original view view_name for fresh data! > > > > Internally implemented the cached views could be put in a schema like pg_cache, > in > > RAM, etc. Doesn't really matter. Would just be nice to have something seamless, > > clean, upgrade agnostic, and easy! Thoughts? > > > > There was a discussion on pgsql-hackers about a month and a half ago > that went along these lines. The talk started with the idea of > integrating pgmemcached into Postgres. > > The main result was that the current postgres cache and system cache > would give the same results as using forced caching configuration. Yeah. I read throught some of that but didn't know if that was the official conclusion. > The overhead of the client connection and sql parsing/planning would > negate the benefits of specifying what is cached. > > One option that was brought up was to create a ram disk and then create > a tablespace on that disk with tables to hold what you want to cache. Of > course maintaining that between restarts becomes a hassle as well. > And if you have enough ram to do that then you have enough for > PostgreSQL to cache the data that is used in ram anyway. Wouldn't it work just like plpgsql functions? Where the first call caches the plan or whatever? > Using pgmemcached outside of the pg client connection allows you to > bypass the sql parsing and planning and get the speed improvements you > are looking for but that is handled by the client not the server. Interesting. I really don't have any serious problems with performance actually. Postgresql runs fantastically. I was just curious about RAM caching, I have the typical 80/20 issue where I would like to free up disk IO for other stuff. I haven't read up to much on pgmemcached. Although I did read about memcache from dinga(?) a while back for livejournal. I'd still like it if pg could put a views results into RAM out of the box. ____________________________________________________________________________________ Yahoo! Music Unlimited Access over 1 million songs. http://music.yahoo.com/unlimited
