Thread: Function immutable is not during a reindex ?

Hi all,
I'm running the followin example on Postgres 7.3.3
I notice that if I declare an immutable function like this:


CREATE OR REPLACE FUNCTION test (integer) RETURNS integer
 AS '
declare
begin
 raise notice ''test called'';
 return $1+1;
end;'
LANGUAGE plpgsql IMMUTABLE;

and I use this function for a partial index:

create table t_a
(
  a integer,
  b integer
);

insert into t_a values ( 1, 0 );
insert into t_a values ( 1, 1 );
insert into t_a values ( 1, 2 );

Now creating an index on that table:

create index idxv on t_a ( b ) where test(3) = b;
NOTICE:  test called
NOTICE:  test called
NOTICE:  test called
CREATE INDEX


the function is immutable but is executed 3 times
( one for each row).

The same if I reindex the table:

reindex table t_a;
NOTICE:  test called
NOTICE:  test called
NOTICE:  test called
REINDEX


Regards
Gaetano Mendola

"Mendola Gaetano" <mendola@bigfoot.com> writes:
> the function is immutable but is executed 3 times
> ( one for each row).

So?  Sounds to me like it's working as intended.

            regards, tom lane

On: Sunday, July 13, 2003 4:19 AM "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Mendola Gaetano" <mendola@bigfoot.com> writes:
> > the function is immutable but is executed 3 times
> > ( one for each row).
>
> So?  Sounds to me like it's working as intended.

Well the documentation says:
IMMUTABLE [...] If this option is given,
any call of the function with all-constant
arguments can be immediately replaced
with the function value.

The "index" behaviuor is different if the same function is used
for a default value, or as field in a select:

if I look at the table of the example:

#\d t_a
Table "public.t_a"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | integer |
Indexes: idxv btree (b) WHERE (test(3) = b)

I was expecting:
Indexes: idxv btree (b) WHERE (4 = b)


look now the differrent behaviour:

Used as field in a select:

#select *, test(2) from t_a;
NOTICE:  test called
 a | b | test
---+---+------
 1 | 0 |    3
 1 | 1 |    3
 1 | 2 |    3
(3 rows)



Used as default value:
# alter table t_a alter b set default test(3);
NOTICE:  test called
ALTER TABLE

#\d t_a
      Table "public.t_a"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | integer | default 4
Indexes: idxv btree (b) WHERE (test(3) = b)


look that in the case of default value there is 4 and in
case of index there is still the call.



I don't like neather the result of the following experiment:
# select *, test(a) from t_a;
NOTICE:  test called
NOTICE:  test called
NOTICE:  test called
 a | b | test
---+---+------
 1 | 0 |    2
 1 | 1 |    2
 1 | 2 |    2
(3 rows)


here is called 3 times with the same argumen '1', I'm not sure
but with the previous version of postgres 7.2.X or 7.1.X
( when there only way was write:   WITH ( iscachable ) )

that select
was like this:
# select *, test(a) from t_a;
NOTICE:  test called
 a | b | test
---+---+------
 1 | 0 |    2
 1 | 1 |    2
 1 | 2 |    2
(3 rows)

and test(1) was correctly cached, I'm not sure about this but you see
the difference when is used inside a default value and inside an index ?



Regards
Gaetano Mendola

"Mendola Gaetano" <mendola@bigfoot.com> writes:
> On: Sunday, July 13, 2003 4:19 AM "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> So?  Sounds to me like it's working as intended.

> Well the documentation says:
> IMMUTABLE [...] If this option is given,
> any call of the function with all-constant
> arguments can be immediately replaced
> with the function value.

Note it says "can be", not "always will be".  IMMUTABLE is a promise
you make to the system about the function's behavior ... not vice
versa.

> Used as default value:
> # alter table t_a alter b set default test(3);
> NOTICE:  test called
> ALTER TABLE

> #\d t_a
>       Table "public.t_a"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  a      | integer |
>  b      | integer | default 4

This is a bug, or at least a bad idea in hindsight, and 7.4 doesn't
do it anymore.

            regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Mendola Gaetano" <mendola@bigfoot.com> writes:
> > #\d t_a
> >       Table "public.t_a"
> >  Column |  Type   | Modifiers
> > --------+---------+-----------
> >  a      | integer |
> >  b      | integer | default 4
>
> This is a bug, or at least a bad idea in hindsight, and 7.4 doesn't
> do it anymore.

Indead I was thinking that was a nice feature have a function
cached...
:-(

regards
Gaetano