Thread: "iscachable" functions

For the "iscachable" parameter of CREATE FUNCTION, the 7.2.x
documentation states:
    "Iscachable indicates that the function always returns the same result
when given the same argument values (i.e., it does not do database
lookups or otherwise use information not directly present in its
parameter list). The optimizer uses iscachable to know whether it is
safe to pre-evaluate a call of the function."

But where is this cache?  Is it per backend connection, or is it shared
among all backends?  Also, is there a way to invalidate this cache or
otherwise expire the values?

thanks!

eric

--- "Eric B.Ridge" <ebr@tcdi.com> wrote:
> For the "iscachable" parameter of CREATE FUNCTION,
> the 7.2.x
> documentation states:
>     "Iscachable indicates that the function always
> returns the same result
> when given the same argument values (i.e., it does
> not do database
> lookups or otherwise use information not directly
> present in its
> parameter list). The optimizer uses iscachable to
> know whether it is
> safe to pre-evaluate a call of the function."
>
> But where is this cache?  Is it per backend
> connection, or is it shared
> among all backends?  Also, is there a way to
> invalidate this cache or
> otherwise expire the values?
>
There isn't any cache: the choice of name is
misleading.  "iscachable" means nothing more than the
docs quote says.  The usefulness of it lies in being
able to index the return value of the function.

__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

On Friday, February 14, 2003, at 04:38  PM, Jeff Eckermann wrote:
> --- "Eric B.Ridge" <ebr@tcdi.com> wrote:

<snip>

>> But where is this cache?  Is it per backend
>> connection, or is it shared
>> among all backends?  Also, is there a way to
>> invalidate this cache or
>> otherwise expire the values?
>>
> There isn't any cache: the choice of name is
> misleading.  "iscachable" means nothing more than the
> docs quote says.  The usefulness of it lies in being
> able to index the return value of the function.

thanks!  I was trying to read too much into the docs.

eric

"Eric B.Ridge" <ebr@tcdi.com> writes:
> For the "iscachable" parameter of CREATE FUNCTION, the 7.2.x
> documentation states:
>     "Iscachable indicates that the function always returns the same result
> when given the same argument values (i.e., it does not do database
> lookups or otherwise use information not directly present in its
> parameter list). The optimizer uses iscachable to know whether it is
> safe to pre-evaluate a call of the function."

> But where is this cache?

There is no cache, only constant-folding of function calls that are
constants at plan time.

The function attribute name(s) have been changed as of 7.3, to "stable"
and "immutable", partly because people kept thinking that the attribute
name implied that we had a cache.

            regards, tom lane

On Fri, 2003-02-14 at 15:14, Eric B.Ridge wrote:
> For the "iscachable" parameter of CREATE FUNCTION, the 7.2.x
> documentation states:
>     "Iscachable indicates that the function always returns the same result
> when given the same argument values (i.e., it does not do database
> lookups or otherwise use information not directly present in its
> parameter list). The optimizer uses iscachable to know whether it is
> safe to pre-evaluate a call of the function."

In CS terms this means the function is referentially transparent.

>
> But where is this cache?  Is it per backend connection, or is it shared
> among all backends?  Also, is there a way to invalidate this cache or
> otherwise expire the values?

The 'cache' is within whatever you used the function for. Let's say you.

   CREATE INDEX foo ON bar( lower(qux) );

When we create this index Pg calls lower(qux) for each record in bar. It
computes each value and 'caches' it as the key.

So when you call:

   SELECT * FROM bar WHERE lower(qux) = lower('aBBa')

The only work to do is lower('aBBa') to 'abba', then do a straight index
lookup. Figuring out lower(qux) for each record in bar has already been
'cached'.

To invalidate the cache you'd have to recreate the index. There's no
automatic dependency tracking I'm aware of.

The same idea applies to other things -- like SPs -- that can be
'compiled'.

>
> thanks!
>
> eric
>
>

Side note:
----------
For a list of iscachable functions (drop the pg_catalog bit for pre
7.3):

    SELECT proname FROM pg_catalog.pg_proc WHERE proisstrict = true;