Re: functions marked STABLE not allowed to do INSERT - Mailing list pgsql-hackers

From Robert Treat
Subject Re: functions marked STABLE not allowed to do INSERT
Date
Msg-id 1132072599.4545.120.camel@camel
Whole thread Raw
In response to Re: functions marked STABLE not allowed to do INSERT  (mark@mark.mielke.cc)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Rod Taylor
Date:
Subject: Re: Running PostGre on DVD
Next
From: "Dave Page"
Date:
Subject: Re: Running PostGre on DVD