On Mon, 2005-11-14 at 22:30, mark@mark.mielke.cc 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?
>
Correct... the idea is to cache across queries, not within a query... I
dug around to find some code; in the example below findparenttype is a
very expensive function.
DECLARE intChild ALIAS FOR $1; intTheHost INTEGER; strStatus TEXT;
BEGIN SELECT host_id INTO intTheHost FROM findhost_cache WHERE entity_id =
intChild; IF NOT FOUND THEN SELECT findparenttype(intChild, 'H') INTO intTheHost;
IF intTheHost IS NOT NULL THEN strStatus := 'INSERT INTO findhost_cache VALUES (' ||
intChild || ',' || intTheHost || ')'; RAISE DEBUG 'FindParentCache Update : % ',strStatus; EXECUTE
strStatus; ELSE RAISE NOTICE 'DATABASE WARNING : Unable To Find An
Associated Host For This Entity : %',$1; END IF;
END IF;RETURN intTheHost;
END;
<snip thoughts on ways to abuse the implementation>
> I'm scared of it.
Dude... I'm scared of your sig ;^D
>
> Cheers,
> mark
>
> --
> mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
> . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
> |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
> | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
>
> One ring to rule them all, one ring to find them, one ring to bring them all
> and in the darkness bind them...
>
> http://mark.mielke.cc/
>
>
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL