Thread: Does CREATE FUNCTION... WITH (ISCACHABLE) work?

I have a function that always returns the same answer given the
same input (no database lookups, etc.). The pg Users' Manual
documents the attribute 'iscachable' as allowing the database to
parse the results of the function and not keep looking it up.

Does this actually work yet? A simple test case:

CREATE FUNCTION f() RETURNS INT AS '
BEGIN
  raise notice ''foo'';
  return 1;
end;
' LANGUAGE 'plpgsql' WITH (ISCACHABLE);

SELECT o();
NOTICE: foo
o
___
1
(1 row)

SELECT o();
NOTICE: foo
o
____
(1 row)

It might be that the parser is smart enough to copy any output
(such as the RAISE NOTICE), my fear is that it is actually running
the function a second time.

Does anyone know if this caching actually happens yet, or is this a
future feature?

Thanks.
--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)

"Joel Burton" <jburton@scw.org> writes:
> I have a function that always returns the same answer given the
> same input (no database lookups, etc.). The pg Users' Manual
> documents the attribute 'iscachable' as allowing the database to
> parse the results of the function and not keep looking it up.

iscachable does not mean that the system will cache the results of the
function across queries, it just means that the function needn't be
re-evaluated multiple times for the same arguments within a single query.
For example, given

    SELECT * from table1 where col = foo(42);

If foo() is marked cachable then it's evaluated once during query
planning; if not it's evaluated again for each row scanned in table1.

I don't think anyone has plans to implement a function result cache
across queries.  Most of the time it'd be a waste of space and cycles...

            regards, tom lane

On 19 Jul 2000, at 14:30, Tom Lane wrote:

> "Joel Burton" <jburton@scw.org> writes:
> > I have a function that always returns the same answer given the same
> > input (no database lookups, etc.). The pg Users' Manual documents
> > the attribute 'iscachable' as allowing the database to parse the
> > results of the function and not keep looking it up.
>
> iscachable does not mean that the system will cache the results of the
> function across queries, it just means that the function needn't be
> re-evaluated multiple times for the same arguments within a single
> query. For example, given
>
>  SELECT * from table1 where col = foo(42);
>
> If foo() is marked cachable then it's evaluated once during query
> planning; if not it's evaluated again for each row scanned in table1.

Sounds reasonable. But does it work as advertised?

CREATE FUNCTION foo(int) RETURNS int AS '
BEGIN
  RAISE NOTICE ''hi'';
  RETURN 1;
END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION foocache(int) RETURNS int AS '
BEGIN
  RAISE NOTICE ''hi'';
  RETURN 1;
END;'
LANGUAGE 'plpgsql'
WITH (iscachable);

SELECT foo(1),foo(1),foo(1)
gives us 3 NOTICEs, as does
SELECT foocache(1), foocache(1), foocache(1)

So is it running the cached version a second time?

Thanks,

--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)

"Joel Burton" <jburton@scw.org> writes:
>> If foo() is marked cachable then it's evaluated once during query
>> planning; if not it's evaluated again for each row scanned in table1.

> Sounds reasonable. But does it work as advertised?

Yes, it does.  You're still not trying it on multiple tuples.

            regards, tom lane