CREATE INDEX idx_d_trh_code_id_partial
ON xmms.d_trh_table
USING btree
(code_id) where code_id not in ('P000','000') and code_id is not null;
ERROR: functions in index predicate must be marked IMMUTABLE

Just trying something new.

I want to create partial indexes on code_id which are not null and not
P000/000

the ones I want are like HMD11 or UE935 or OIOR11 etc.

not sure where the IMMUTABLE part is coming from..

Clue?





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:
> On Mon, 2008-08-25 at 12:23 +0800, Ow Mun Heng wrote:
>> CREATE INDEX idx_d_trh_code_id_partial
>> ON xmms.d_trh_table
>> USING btree
>> (code_id) where code_id not in ('P000','000') and code_id is not null;
>> ERROR: functions in index predicate must be marked IMMUTABLE

> BTW, this is on 8.2.9 Seems to work OK on 8.3.3.

I suppose code_id is varchar or some such?

Try "where code_id::text not in ...". There's an array type coercion
underlying the right-hand side of the NOT IN, and 8.2 had some problems
with correctly identifying the volatility of such coercions.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:
> On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:
>> I suppose code_id is varchar or some such?

> Yep
> After a few more investigation on the usefulness of the partial indexes,
> I found that, it really isn't all that useful, perhaps some experts can
> shed some light.

I poked at that example a bit more earlier today, and found that 8.3
has a problem that's interfering with optimizing x IN ('y','z') type
clauses when x is varchar. If you don't mind building a local copy,
see if this patch helps you any:
http://archives.postgresql.org/pgsql...8/msg00254.php

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general