To: Rich Cullingford <rculling@sysd.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Problems creating indexes with IMMUTABLE functions 
In-reply-to: <40D06BEC.3060102@sysd.com> 
References: <40D06BEC.3060102@sysd.com>
Comments: In-reply-to Rich Cullingford <rculling@sysd.com>
	message dated "Wed, 16 Jun 2004 11:49:00 -0400"
Date: Wed, 16 Jun 2004 14:15:57 -0400
Message-ID: <23854.1087409757@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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