Problems creating indexes with IMMUTABLE functions
От | Rich Cullingford |
---|---|
Тема | Problems creating indexes with IMMUTABLE functions |
Дата | |
Msg-id | 40D06BEC.3060102@sysd.com обсуждение исходный текст |
Ответы |
Re: Problems creating indexes with IMMUTABLE functions
|
Список | 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@sysd.com
В списке pgsql-interfaces по дате отправления: