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 |/