The context of the discussion was a hack to speed queries against static tables, so MVCC is not relevent. As soon as any work unit against a referenced table commits, the cache is invalid, and in fact the table shouldn't be a candidate for this caching for a while. In fact, this cache would reduce some the MVCC 'select count(*) from us_states' type of horrors.
(The attraction of a server side cache is obviously that it could *with no server or app changes* dramatically improve performance. A materialized view is a specialized denormalization-ish mechanism to optimize a category of queries and requires the DBA to sweat the details. It is very hard to cache things stochastically without writing a server. Trigger managed extracts won't help you execute 1,000 programs issuing the query "select sec_level from sec where division=23" each second or a big table loaded monthly.) ----- Original Message ----- From: "Jeff" <[EMAIL PROTECTED]> To: "Mitch Pirtle" <[EMAIL PROTECTED]> Cc: "Aaron Werman" <[EMAIL PROTECTED]>; "Scott Kirkwood" <[EMAIL PROTECTED]>; "Neil Conway" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Tom Lane" <[EMAIL PROTECTED]> Sent: Monday, September 27, 2004 2:25 PM Subject: Re: [PERFORM] Caching of Queries > [ discussion of server side result caching ] > > and lets not forget PG's major fork it will throw into things: MVCC > The results of query A may hold true for txn 1, but not txn 2 and so on > . > That would have to be taken into account as well and would greatly > complicate things. > > It is always possible to do a "poor man"'s query cache with triggers.. > which would just leave you with basically a materialized view. > > -- > Jeff Trout <[EMAIL PROTECTED]> > http://www.jefftrout.com/ > http://www.stuarthamm.net/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
