Re: Function result cacheing

At 00:18 17/08/2002 -0400, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)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 |/