Re: functions marked STABLE not allowed to do INSERT

On Mon, Nov 14, 2005 at 10:30:48PM -0500, 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?

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      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461