Re: Problems creating indexes with IMMUTABLE functions

From: Rich Cullingford <rculling(at)sysd(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: Problems creating indexes with IMMUTABLE functions
Date: 2004-06-16 18:33:16
Message-ID: 40D0926C.7040607@sysd.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-interfaces

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

In response to

Browse pgsql-interfaces by date

  From Date Subject
Next Message Owens, Steve 2004-06-18 21:11:01 Interesting ECPG error
Previous Message Tom Lane 2004-06-16 18:15:57 Re: Problems creating indexes with IMMUTABLE functions