On Mon, Nov 14, 2005 at 10:30:48PM -0500, [EMAIL PROTECTED] wrote: > On Mon, Nov 14, 2005 at 10:02:32PM -0500, Robert Treat wrote: > > > Isn't this the sort of case that Tom just explained as not functioning > > > in 8.1, as the STABLE functions, and all functions called by the > > > STABLE functions will use the snapshot that is used at the time it > > > was called? As in, you do the INSERT, but within the same SELECT > > > statement invoking this 'STABLE' function, it never sees the inserted > > > cached value? > > That's the whole point, it doesn't need to see the cached value as it has > > already done the look-up the expensive way. But all subsequent queries > > will > > get the value from the cache table, thereby avoiding the expensive query. > > Ok. I think I get it. But -- isn't the STABLE definition itself enough to > benefit the same query, without INSERT, assuming appropriate optimization > of STABLE? > > The INSERT is only for caching across multiple statements, then, > correct? Or is it to get around a deficiency in the implementation of > STABLE?
FWIW, another use-case: I've got some code that logs page hits. Being that it's dirt simple, it just uses the incomming URL as a means for logging. I want that info to be normalized, so part of logging involves looking up that url to see if it already exists in the url table, and returning it's id. If it doesn't already exist, the function creates it and then returns the ID. > > > [ application side caching? ] > > These are all business logic decsions and as such would be implementation > > dependent. Generally the idea is that once the expensive query is done, > > it's > > value is unlikely to change. If this were something that would change a lot > > then it wouldn't exactly be non-volatle would it? > > I think that's the point. Whether the data changes or not in the table, isn't > restricted by the definition of the functions that access the data. > > I believe I see your argument, and given a suitable definition of STABLE > (such as only table snapshots being used for the STABLE function, and all > functions invoked by the STABLE function), I can see INSERT being safe > (although perhaps difficult to understand). > > I predict wierd scenarios, including a VOLATILE function that normally > expects to be able to update a table, and view the updates > immediately, failing in unexpected ways when called from a STABLE > function. Yuck. It really sounds like something is wrong. Or missing. > > I'm scared of it. ISTM that there might be need for another level of function stability marking (which could possible be determined automatically). It's certainly possible to construct a function that can modify data but will always return the same results in a tablescan (current definition of STABLE). If there's performance benefits to be had on functions that are both STABLE (as per the old definition) and don't modify any data (or contain any volatile functions?) then that should be a new level of stability. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
