Re: [ADMIN] Function immutable is not during a reindex ?

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