Re: Problems creating indexes with IMMUTABLE functions

[Rich Cullingford <rculling(at)sysd(dot)com>]

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