Thread: Function result cacheing

This has been discussed before in the context of misunderstanding the 
meaning of 'iscachable', but I now have a use for cached function results, 
and have seen at least one other posting with a similar need.

The reason I need it is that I have a few functions that do recursive 
inheritance lookups going up a converging inheritance tree. Typically this 
function will be called on several hundred objects in a single select 
statement. Because of inheritance, it ends up with several thousand 
function calls, each of which is non-trivial.

A solution that would be useful for me would be:

If a function is marked 'invariant' (or something similar), then

- cache the most recently used 20 calls (config item) iff the args were 
less than 1K in total storage (ie. don't cache large text blocks),

- calculate a very simple checksum on the args

- when a function is to be evaluated, calc the checksum and if a match is 
found, compare the args, and if they all match, return the result.

I would anticipate deleting the cache when the current command exits, 
and/or certainly when a TX ends.

Obviously this is not a 7.3 item, but would people support such 
functionality going into a future version?





----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
> Obviously this is not a 7.3 item, but would people support such 
> functionality going into a future version?

Actually, I wouldn't.  I can think of very few situations where
such caching is useful, and I don't believe that the mechanism required
would pay for itself.  In the cases where a cache does make sense,
it's sufficiently application-specific that a generic "cache on a key
consisting of the function arguments" isn't the right thing anyway;
you'll find you want some internal logic to decide what to cache and
what key to use to retrieve it.  Furthermore, a generic cache will have
no clue whatever about cache-invalidating events, thus further
restricting its usefulness.  (Your suggestion of "flush at transaction
end" is too short-term for most applications, too long-term for some,
and just right for hardly any.)

Build the cache internally to your function if you need it.
        regards, tom lane

At 00:18 17/08/2002 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
> > Obviously this is not a 7.3 item, but would people support such
> > functionality going into a future version?
>
>Actually, I wouldn't.

This forces application-based caches, which in turn need indexed local 
temporary tables, and ideally the ability to either check if they exist, or 
a CREATE...IF NOT EXISTS. And I'd guess the indexes would not be used, 
whereas the 'checksum on args' model comes close to hash-index performance.


>I can think of very few situations where
>such caching is useful,

Aside, of course, from any external functions that for whatever reason are 
expensive to execute, and which will be passwed the same args more than 
once in a single SELECT. As well as any functions that do complex lookups 
on reference data in the database; in short anything that only reads data 
and which does more than a simple lookup, and which gets the same args more 
than once.


>  and I don't believe that the mechanism required
>would pay for itself.

In what sense? The mechanism is close to cost-free if the flag is not set 
on the function, and would presumably only be set by the definer if there 
was likely to be a benefit. Coming from a database that supports such 
functions, I *know* they can help a great deal.


>In the cases where a cache does make sense,
>it's sufficiently application-specific that a generic "cache on a key
>consisting of the function arguments" isn't the right thing anyway;

Not for the the uses I have.


>you'll find you want some internal logic to decide what to cache and
>what key to use to retrieve it.

No, I don't. I am very happy with function parameters being used.


>   Furthermore, a generic cache will have
>no clue whatever about cache-invalidating events, thus further
>restricting its usefulness.

This is true, but mainly an argument for cacheing at the statement level; 
TX level cacheing seems like a bad idea. It's a matter for application 
design to ensure that when a developer marks a function as invariant, then 
they mean it. If it really becomes a problem, then *maybe* we need an 
application-level cache invalidation, but it seems very unlikely to be 
a  problem.

>   (Your suggestion of "flush at transaction
>end" is too short-term for most applications, too long-term for some,
>and just right for hardly any.)

I actually suggested two options, and would personally prefer 
flush-at-statement-end.


>Build the cache internally to your function if you need it.

Not too keen on building cacheing code into 3 different functions just on 
the one database;  and doing the same on another which also would benefit.





----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/