From: Rich Cullingford <rculling@sysd.com>
To: pgsql-interfaces@postgresql.org
Subject: Problems creating indexes with IMMUTABLE functions
Date: Wed, 16 Jun 2004 11:49:00 -0400
Message-ID: <40D06BEC.3060102@sysd.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: rculling@sysd.com
Subject: Re: Problems creating indexes with IMMUTABLE functions
In-Reply-To: <40D06BEC.3060102@sysd.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox