Problems creating indexes with IMMUTABLE functions

From: Rich Cullingford <rculling(at)sysd(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Problems creating indexes with IMMUTABLE functions
Date: 2004-06-16 15:49:00
Message-ID: 40D06BEC.3060102@sysd.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

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(at)sysd(dot)com

Responses

Browse pgsql-interfaces by date

  From Date Subject
Next Message Tom Lane 2004-06-16 18:15:57 Re: Problems creating indexes with IMMUTABLE functions
Previous Message Bruce Momjian 2004-06-16 14:33:30 Re: ecpg Informix compatability changes for Version 7.4.3