Search Postgresql Archives

Re: named cache

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



--- 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?


 
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux