Re: Function result cacheing - any comments?

At 22:29 18/08/2002 -0700, Joe Conway wrote:
>create function get_manager_names() returns setof manager_names as
> 'select d.id, p.name from departments d, people p
> where p.id = d.manager_id' language sql;
>
>select p.name, m.name as boss from people p, get_manager_names() m where
>p.department_id = m.dept_id;
>...
>Is this anything close what you had in mind?

Nice thought, and it probably works for the example I gave, but in the case
where the secondary table is large potentially large, I think it falls down.

To give an example, in my case I have a function 'has_access_to_object'
which does access checking up a tree of ownership & inheritance. While the
first level access check is always unique, subsequent ones will be executed
more than once in a typical tree.

As a result, what I would like to implement is a new attribute for
functions (eg. 'invariant') which tells the function manager that in the
context of one command, if the function is called with the same args, the
result will be the same. The idea is for the function manager(?) to
maintain a cache of, say, up to 100K of cached function results for
functions marked 'invariant'. A hash will be used to check if a function
result is in the cache, and the least recently used results will be purged
when necessary.

While my example is quite specific, the benefits would apply to any simple
lookup function, as well as any external function that is expensive to execute.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/