Re: named cache

--- Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:

> maybe you would find "materialized views" interesting.
> http://www.google.com/search?q=materialized+view+postgresql
>
>
> On 12/1/06, Matthew Peter <survivedsushi(at)yahoo(dot)com> 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