Message-ID: <40D0926C.7040607@sysd.com>
Date: Wed, 16 Jun 2004 14:33:16 -0400
From: Rich Cullingford <rculling@sysd.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030703
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Problems creating indexes with IMMUTABLE functions
References: <40D06BEC.3060102@sysd.com> <23854.1087409757@sss.pgh.pa.us>
In-Reply-To: <23854.1087409757@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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