Greetings!<br /><br />I've done a brief search of the postgresql mail archives, and I've noticed a few projects for
addingquery caches to postgresql, (for example, Masanori Yamazaki's query cache proposal for GSOC 2011), as well as
thequery cache announced at <a
href="http://www.postgresql.org/about/news/1296/">http://www.postgresql.org/about/news/1296/</a> (pgc). Both of these
seemto be external solutions that act more like a proxy between clients and servers, instead of being part of the
serverprocesses.<br /><br />I'm wondering if anyone would be interested in a query cache as a backend to postgresql?
I'vebeen playing around with the postgresql code, and if I'm understanding the code, I believe this is possible. I've
beenwriting some code, but don't have anything working yet, (I'm receiving a hash table corruption error), but I'm
workingthrough it.<br /><br />here's my basic idea:<br /><br />1. intercept select queries in execMain.c at
ExecuteQueryand 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
wouldcreate a different query hash key.<br /> 2. if the query is in the cache, return the cached results of this
query.<br/>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
addedbefore going to production).<br /><br />To invalidate cache entries, look at the transactions being committed (and
writtento WAL log, if my memory serves me) and send a message to the qcache process to invalidate any query which
dependson the modfied relation (ie, table, etc)<br /><br /><br />For the experts out there, does this seem reasonable,
oram I misunderstanding the source code? Anyone aware of a project trying to accomplish this?<br /><br />Thanks!<br
/><br/>Billy Earney<br /><br />