Re: functions marked STABLE not allowed to do INSERT

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?

> > [ 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.

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/