Re: functions marked STABLE not allowed to do INSERT

On Mon, 2005-11-14 at 22:30, mark(at)mark(dot)mielke(dot)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(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)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