Re: Query caching absent "query caching"

Hi Kevin,

On 11/25/2012 8:10 AM, Kevin Grittner wrote:
> Bexley Hall wrote:
>
>> Specifically, I have several computationally expensive
>> functions that derive their results from specific values of
>> these base types. *Solely*. (For example, area() when
>> applied to a given "circle" always yields the same result...
>> though this is a trivial/inexpensive function, by comparison).
>>
>> I can define the base types to set aside space to store
>> these results and cache them *in* the base type. Then, serve
>> up these cached results when they are needed, again. With
>> plan caching, this should (?) reduce the cost of repeated
>> queries significantly without the need/benefit for caching the
>> actual query results. (Is that true?)
>>
>> To guard against future enhancements to the server (e.g., if
>> query caching is ever implemented, etc.), I assume that all
>> such functions should declare themselves as IMMUTABLE? Or,
>> does my update of the internal representation of the data
>> values (i.e., to include the cached results of each of these
>> functions) conflict with this declaration?
>
> As long as a call to a given function with a specific set of
> arguments always returns the same result, and there are no *user
> visible* side effects of the internal caching, I don't see a
> problem with declaring the functions immutable.

OK.

> Out of curiosity, are you planning on using a process-local cache
> (which would start empty for each new connection) or are you
> planning to allocate shared memory somehow and coordinate access to
> that?

I was planning on writing back the results of each successful
function evaluation into the data type's internal representation.
Ideally, back into PostgreSQL's "master copy" of the data
(though I would settle for hiding it in an anonymous table
behind a view, etc.)

The point is NEVER to have to RE-evaluate any of these functions
for the data on which they are evaluated once they have been
evaluated (assuming the data themselves do not change).  And,
in doing so, make the results of each evaluation available to
other clients regardless of the query which caused them to
be evaluated.

Thx,
--don