Re: Function immutable is not during a reindex ?

From: "Mendola Gaetano" <mendola(at)bigfoot(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Function immutable is not during a reindex ?
Date: 2003-07-13 12:12:15
Message-ID: 002f01c34937$ff7c48f0$10d4a8c0@mm.eutelsat.org
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-admin pgsql-bugs

On: Sunday, July 13, 2003 4:19 AM "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Mendola Gaetano" <mendola(at)bigfoot(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Andrew Biagioni 2003-07-13 14:16:33 Re: Installazione Postgres
Previous Message Rajesh Kumar Mallah 2003-07-13 11:19:42 Re: How to backup and Restore single table in Postgresql DB

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2003-07-13 15:21:06 Re: Function immutable is not during a reindex ?
Previous Message Mendola Gaetano 2003-07-13 02:58:42 Function immutable is not during a reindex ?