Thread

Bug with 'iscachable' attribute (Was: Index selection bug)

Andriy I Pilipenko <bamby@marka.net.ua> — 2000-07-27T07:38:57Z

On Wed, 26 Jul 2000, Tom Lane wrote:

> Andriy I Pilipenko <bamby@marka.net.ua> writes:
> > 	create function func() returns int as 'select 1' language 'sql';
> 
> > 	set enable_seqscan to 'off';
> 
> > 	explain select * from t where f = 1;
> 
> > 	  Index Scan using i on t  (cost=0.00..2.01 rows=1 width=4)
> 
> > 	explain select * from t where f = func();
> 
> > 	  Seq Scan on t  (cost=100000000.00..100000001.34 rows=1 width=4)
> 
> Not a bug, because you didn't declare the function 'iscachable'.
> For all the system knows, func() is like random() and will return a
> different result at every row.  An indexscan can't be used unless it's
> safe to fold the function call down to a constant.  See
> http://www.postgresql.org/users-lounge/docs/7.0/postgres/sql-createfunction.htm

Thank you for help. I used iscachable attribute and all are mostly ok
except this:

create table a (a int)

create table b (b int)

create function f() returns int as '
	select a 
          from a 
         where a = (select max(b) from b)
' language 'sql' 
with (iscachable)

select f()

  ERROR:  replace_vars_with_subplan_refs: variable not in subplan target list


  Kind regards,
  Andriy I Pilipenko
  PAI1-RIPE