Greetings! I've done a brief search of the postgresql mail archives, and I've noticed a few projects for adding query caches to postgresql, (for example, Masanori Yamazaki's query cache proposal for GSOC 2011), as well as the query cache announced at http://www.postgresql.org/about/news/1296/ (pgc). Both of these seem to be external solutions that act more like a proxy between clients and servers, instead of being part of the server processes.
I'm wondering if anyone would be interested in a query cache as a backend to postgresql? I've been playing around with the postgresql code, and if I'm understanding the code, I believe this is possible. I've been writing some code, but don't have anything working yet, (I'm receiving a hash table corruption error), but I'm working through it. here's my basic idea: 1. intercept select queries in execMain.c at ExecuteQuery and see if the sourcetext of this query is in the "query hash". (later we could make this more sophisticated by using the query plan or some type of AST) instead of the query text since adding or removing a space would create a different query hash key. 2. if the query is in the cache, return the cached results of this query. 3. if the query is not cached, run the query like normal, grabbing the tuples as they are sent to the "dest" and store them in the cache. (For now, I'm ignoring storage constraints, etc, but these details will need to be added before going to production). To invalidate cache entries, look at the transactions being committed (and written to WAL log, if my memory serves me) and send a message to the qcache process to invalidate any query which depends on the modfied relation (ie, table, etc) For the experts out there, does this seem reasonable, or am I misunderstanding the source code? Anyone aware of a project trying to accomplish this? Thanks! Billy Earney