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
