Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-interfaces by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group