Thread: ERROR: DefineIndex: index function must be marked iscachable

With version 7.2, when I tried to create an index using the built-in
funtion date(), I got an error:

ai=> create index stl_date on stl(date(stl_trxdate) );
ERROR:  DefineIndex: index function must be marked iscachable

What does this mean?  I could do so under version 7.1.2

On Mon, Mar 11, 2002 at 08:10:12PM +0800, Lec wrote:
> With version 7.2, when I tried to create an index using the built-in
> funtion date(), I got an error:
>
> ai=> create index stl_date on stl(date(stl_trxdate) );
> ERROR:  DefineIndex: index function must be marked iscachable
>
> What does this mean?  I could do so under version 7.1.2

When a function is marked "iscachable" it means that for the same input it
will always produce the same output. This is obviously a desirable property
for something you want to index on.

So, add the "iscachable" function to the "create function" statement.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> If the company that invents a cure for AIDS is expected to make their
> money back in 17 years, why can't we ask the same of the company that
> markets big-titted lip-syncing chicks and goddamn cartoon mice?

On Mon, 11 Mar 2002, Lec wrote:

> With version 7.2, when I tried to create an index using the built-in
> funtion date(), I got an error:
>
> ai=> create index stl_date on stl(date(stl_trxdate) );
> ERROR:  DefineIndex: index function must be marked iscachable
>
> What does this mean?  I could do so under version 7.1.2

See recent discussions on the mailing list.  I'm guessing that
stl_trxdate is a timestamp?  If so, date(timestamp) is not
cachable right now because the results depend on timezone and
so said index could get corrupted if you're doing things
from multiple timezones.  You could make the column
timestamp without timezone which I believe has a cachable
date().