Why not cache stable functions? - Mailing list pgsql-hackers

Hello, pgsql-hackers.

I think It would be useful to cache return values for stable
and immutable functions. Now thay are really called everytime.

Demonstration (Postgresql 8.0.1 SlackWare10.0):

create table t
( i int PRIMARY KEY
) without oids;

insert into t values (1);
insert into t values (2);
insert into t values (3);
insert into t values (4);
insert into t values (5);
insert into t values (6);
insert into t values (7);
insert into t values (8);
insert into t values (9);
insert into t values (10);

create table tt
(  x int,  y int,  PRIMARY KEY (x,y)
) without oids;

insert into tt (x,y) select t1.i,t2.i from t t1,t t2;

create function more_with_count(int,int) returns boolean as $BODY$
$_SHARED{count}++;
return (@_[0]>@_[1])?'t':'f';
$BODY$
language plperl stable;

create function get_count() returns int as $BODY$
my $c=$_SHARED{count};
$_SHARED{count}=0;
return $c;
$BODY$
language plperl;

select * from tt ,t where more_with_count(tt.x,t.i);

select get_count();
/*
returns:
get_count()
-----------     1000
*/

Don't you think that get_count=100 is enough?

Motivation (simplified):

I have a hierarchical table for groups:

create table group
( id int PRIMARY KEY; pid int, descr varchar(100), CONSTRAINT CHECK (pid IS NULL or pid<id)
);
--If pid is NULL then group is a root for some tree in a forest

--A table for group price:

create table direction
( direction varchar(20), group_id  int, price     numeric(6,4) NOT NULL, PRIMARY KEY (direction,group_id)
);

create index ix_direction on direction ( (direction || chr(255)), direction, group_id )
--and (part of) table with idetified phonecalls:

create table calls
( id_call    int PRIMARY KEY, called_nom varchar(20) NOT NULL, group_id   int
);

/*
I wish to write function like this (it may contains errors,couse after some test on stable functions I realized it
wouldbe slow and not test)
 
*/
create function is_parent_or_self(int,int) returns boolean as $BODY$
declare  this int;  descendant int;
begin this:=$1; descendant:=$2; while not descendant is NULL and this<>descendant loop    select into descendant id
fromgroup where pid=descendant; end loop; if this=descendant then   return true; end if; return false;
 
end;
$BODY$
language plpgsql stable;

-- And set price for a call with a query
select distinct on (id_call) id_call,price
from
calls c inner join
direction d on
c.called_nom||chr(255)>d.direction
and c.called_nom<=d.direction
inner join
group g
on d.group_id=g.id and is_parent_or_self(c.group_id,g.id)
order by id_call,d.direction desc,g.id desc;

/* there are not more than 15 active groups from 30 total  and I think that 400 hash keys without list, so that  each
hashkeyequality for a different argument list would  replace previous cached value, would enough for  speed up query.
 
*/

Syntactically it may be formed as:

set enable_stable_function_cache=on; -- And somewhat in postgresql.conf
set max_stable_function_cache=1000;


create function ...
stable cached(400);

And I think it useful to cache values for immutable functioins
accros whole connection.
-- falcon                          mailto:falcon@intercable.ru