[HACKERS] Why not cache stable functions?

[falcon]

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 would be 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 from group 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 hashkey equality 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:[EMAIL PROTECTED]



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]