Message-ID: <40D06BEC.3060102@sysd.com>
Date: Wed, 16 Jun 2004 11:49:00 -0400
From: Rich Cullingford <rculling@sysd.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030703
MIME-Version: 1.0
To: pgsql-interfaces@postgresql.org
Subject: Problems creating indexes with IMMUTABLE functions 
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

All,
It appears that the use of certain kinds of functions on columns to 
create indexes is disabled in PG 7.4.1 (on RH 8.0 3.2-7). An attempt to 
create an index on the concatenation of a date and an int gives:

sysd=> create index bnoz_idx on evidence (bnoz(alert_date, period));
ERROR:  functions in index expression must be marked IMMUTABLE

although:

sysd=> select provolatile, prosrc from pg_proc where proname='bnoz';
  provolatile |             prosrc
-------------+------------------------------------------------------------------------------------------------------------------------------------------------
  i           |
declare
  hr alias for $2;
  pad text := '0';
begin
   if hr<10 then
    return $1 || ' ' || pad || hr;
   end if;
   return $1 || ' ' || hr;
end;

That is, the function is IMMUTABLE according to the catalog. The problem 
appears to be with the date field:

sysd=> create index bnoz_idx on evidence ((alert_date || ' ' || period));
ERROR:  functions in index expression must be marked IMMUTABLE

though:

sysd=> create index bnoz_idx on evidence ((service || ' ' || period));
CREATE INDEX

works, where 'service' is a text column.

I've searched the lists for indciations of this problem, and have failed 
to find anything. Does this ring a bell with anyone?

                          Sincerely,
                          Rich Cullingford
                          rculling@sysd.com