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
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 4This 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