Re: Function result cacheing - Mailing list pgsql-hackers
From | Philip Warner |
---|---|
Subject | Re: Function result cacheing |
Date | |
Msg-id | 5.1.0.14.0.20020817164914.028f4ae0@mail.rhyme.com.au Whole thread Raw |
In response to | Re: Function result cacheing (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-hackers |
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 |/
pgsql-hackers by date: