Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-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

pgsql-admin by date

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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group