Thread: Problems creating indexes with IMMUTABLE functions

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

Rich Cullingford <rculling@sysd.com> writes:
> sysd=> create index bnoz_idx on evidence (bnoz(alert_date, period));
> ERROR:  functions in index expression must be marked IMMUTABLE

How is bnoz declared, exactly?  You did not show us the function
signature.

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

Assuming alert_date is of type DATE, this would fail because the
date-to-text coercion function is not immutable (for the simple reason
that its results depend on the DateStyle variable as well as the input
value).

I am guessing that bnoz is declared to take type text as its first
argument, which means that the above index declaration includes an
implicit date-to-text coercion as part of the index expression,
which quite rightly causes the CREATE INDEX to fail.  You'd not
want your index to break if you changed DateStyle.

When I tried to duplicate this, I declared bnoz as taking type DATE,
and the CREATE INDEX worked just fine.  However, I then had a time
bomb on my hands, because the index entries in fact depended on
the setting of DateStyle --- the internal conversion occurring inside
bnoz isn't immutable, and so I was lying to claim that bnoz was.

The safe way to approach this would be to declare bnoz to take date,
and be careful to do the text conversion inside it in a
DateStyle-independent manner, perhaps using to_char().  Better watch out
for TimeZone dependencies, too.  I think you'd need to write something
liketo_char($1::timestamp without time zone, 'YYYY/MM/DD')
to be sure about that.  (Of course you can pick any date format you
like here, you just can't change your mind without rebuilding the
index.)
        regards, tom lane

Tom,
Thanks for the reply. Here's the function:

create or replace function bnoz(date, int)
returns text as ' 

begin  return $1 || '' '' || $2;
end; '
language 'plpgsql' immutable;

But now I'm embarrassed. When I load this function into a new invocation 
of psql, the CREATE INDEX call works fine. I must have missed an error 
message like:

sysd=> create index bnoz_idx on scenario_evidence (bnoz(alert_date, 
period));
ERROR:  relation "bnoz_idx" already exists

from an old attempt to create the index.

As you say though, for safety's sake a function like this should take 
account of the DateStyle in use, and stick with it.

I guess I was just disconcerted that an example so close to the one in 
the doc failed.
                  Sorry, and thanks for the advice,                  Rich C.

Tom Lane wrote:
> Rich Cullingford <rculling@sysd.com> writes:
> 
>>sysd=> create index bnoz_idx on evidence (bnoz(alert_date, period));
>>ERROR:  functions in index expression must be marked IMMUTABLE
> 
> 
> How is bnoz declared, exactly?  You did not show us the function
> signature.
> 
> 
>>sysd=> create index bnoz_idx on evidence ((alert_date || ' ' || period));
>>ERROR:  functions in index expression must be marked IMMUTABLE
> 
> 
> Assuming alert_date is of type DATE, this would fail because the
> date-to-text coercion function is not immutable (for the simple reason
> that its results depend on the DateStyle variable as well as the input
> value).
> 
> I am guessing that bnoz is declared to take type text as its first
> argument, which means that the above index declaration includes an
> implicit date-to-text coercion as part of the index expression,
> which quite rightly causes the CREATE INDEX to fail.  You'd not
> want your index to break if you changed DateStyle.
> 
> When I tried to duplicate this, I declared bnoz as taking type DATE,
> and the CREATE INDEX worked just fine.  However, I then had a time
> bomb on my hands, because the index entries in fact depended on
> the setting of DateStyle --- the internal conversion occurring inside
> bnoz isn't immutable, and so I was lying to claim that bnoz was.
> 
> The safe way to approach this would be to declare bnoz to take date,
> and be careful to do the text conversion inside it in a
> DateStyle-independent manner, perhaps using to_char().  Better watch out
> for TimeZone dependencies, too.  I think you'd need to write something
> like
>     to_char($1::timestamp without time zone, 'YYYY/MM/DD')
> to be sure about that.  (Of course you can pick any date format you
> like here, you just can't change your mind without rebuilding the
> index.)
> 
>             regards, tom lane