From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rich Cullingford <rculling@sysd.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Problems creating indexes with IMMUTABLE functions
Date: Wed, 16 Jun 2004 14:15:57 -0400
Message-ID: <23854.1087409757@sss.pgh.pa.us> (raw)
In-Reply-To: <40D06BEC.3060102@sysd.com>
References: <40D06BEC.3060102@sysd.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, rculling@sysd.com
Subject: Re: Problems creating indexes with IMMUTABLE functions
In-Reply-To: <23854.1087409757@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox